CODEWITHSUNDEEP
postgresql
Chen Yu
Chen Yu

Reputation: 4077

How to restrict the whole schema's size?

Every user has its own schema.

I want to restrict every schema under specified size. While reading the manual, I found it doesn't seem have such limitation. Is it possible? 

drop schema jacob_001 cascade;
/*drop schema jacob cascade;*/
create schema jacob_001;
/*create schema jacob;*/

/*使用缺省的schema*/
set search_path to jacob_001;

create table messages(
       id    integer,
       message text,
       culture_location varchar(6),
       primary key (id,culture_location)
       );

create table media_types(
       /*code       char(2) primary key,*/
       id           integer primary key,
       description      text
       /*message_code      int*/
       );

Upvotes: 3

Views: 1993

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78423

Per Craig's comment, you cannot enforce quotas at the DB level.

What you can do, however, is check the schema's size at your app's level. Quoting:

http://wiki.postgresql.org/wiki/Schema_Size

CREATE OR REPLACE FUNCTION pg_schema_size(text)
    returns bigint
AS $$
SELECT sum(pg_relation_size(
           quote_ident(schemaname) || '.' || quote_ident(tablename)
       ))::bigint
FROM pg_tables
WHERE schemaname = $1
$$ LANGUAGE sql;

Usage:

select pg_size_pretty(pg_schema_size('public'));

Upvotes: 4

Related Questions