Re: Adding "quota user limit" using triggers

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Andrea Peri
Sent: Thursday, June 09, 2011 6:05 AM
To: pgsql-general(at)postgresql(dot)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.