Thread: Adding "quota user limit" using triggers
>on 06/08/11 11:14 PM, Andrea Peri wrote:
>Hi,>Is possible the limit the size of an user (quota user) as space allocated in a tablespace ?
>
>>I don't find this option in the 9.0 version of PG,>However before start to work on it , I like to have a confirm and if there is some documentation or information on this
>so I think it is not available.
>
>Perhaps it should be possible to simulate it using triggers.
>
>>problem available.
>The total size of tables created by that user regardless of what role inserted data into said tables? There's really no way to >track the data written by a user into tables that multiple users have insert/update privs to. and does it include older tuple >versions that aren't yet vacuumed? calculating pg_total_relation_size is fairly expensive, too, it requires scanning the table >and ancilliary items (indexes, toast storage) to sum up the number of blocks allocated.
>what exactly would this limit?
>
>
>
I guess the quota limit should be applied to the owner of the table is also the owner of the indexes, sequences, and so on..
regardless of which has inserted on it.
And also regardless of vacuumed space.
>calculating pg_total_relation_size is fairly expensive, too, it requires scanning the table >and ancilliary items (indexes, toast
>storage) to sum up the number of blocks allocated.
yes I think this is a fairly expensive task,
But is for me necessary.
--
-----------------
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-----------------
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andrea Peri Sent: Thursday, June 09, 2011 6:05 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Adding "quota user limit" using triggers >on 06/08/11 11:14 PM, Andrea Peri wrote: >Hi, > > >Is possible the limit the size of an user (quota user) as space allocated in a tablespace ? >I don't find this option in the 9.0 version of PG, >so I think it is not available. > >Perhaps it should be possible to simulate it using triggers. > > >However before start to work on it , I like to have a confirm and if there is some documentation or information on this >problem available. >what exactly would this limit? > > >The total size of tables created by that user regardless of what role inserted data into said tables? There's really no way to >track the data written by a user into tables that multiple users have insert/update privs to. and does it include older tuple >versions that aren't yet vacuumed? calculating pg_total_relation_size is fairly expensive, too, it requires scanning the table >and ancilliary items (indexes, toast storage) to sum up the number of blocks allocated. > I guess the quota limit should be applied to the owner of the table is also the owner of the indexes, sequences, and so on.. regardless of which has inserted on it. And also regardless of vacuumed space. >calculating pg_total_relation_size is fairly expensive, too, it requires scanning the table >and ancilliary items (indexes, toast >storage) to sum up the number of blocks allocated. yes I think this is a fairly expensive task, But is for me necessary. >>>>>>>>>>>>>>>>>>>>>>>>>>>> Is a hard-limit required or would a simple periodic size/limit check query - run during low activity timeframes - be sufficient? No triggers required and you only call the expensive size check occasionally instead of during every insert. Or, instead of limiting by size you could limit by number of records which can either be done fairly quickly via trigger or still via a periodic query. You could maybe add a trigger to ensure that any bytea or text fields fall under an expected maximum size so that your average row size calculation stays within reason. You don't even need to disallow the insert but instead simply put a message out that a particular record is over-sized. Arbitrary hard limits are problematic since they force action when the threshold is met - even if only by a few bits. If someone with database privileges is abusing them to the point where a hard-quota is your solution you have bigger problems to address. If it is NOT an abuse issue then you need to revise your expectations - or get more hardware. David J.