Re: SOC & user quotas

From: "Sergey E(dot) Koposov" <math(at)sai(dot)msu(dot)ru>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org, Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
Subject: Re: SOC & user quotas
Date: 2007-02-28 17:14:13
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

On Wed, 28 Feb 2007, Tom Lane wrote:

> "Sergey E. Koposov" <math(at)sai(dot)msu(dot)ru> writes:
>> 1) The main idea is to implement the per-user quota (not per tablespace
>> for example). So, during the creation of the new user some quota can be
>> specified, and after that the size of all the relations *owned* by that
>> user should be limited by that number.
> This seems impractical as stated; there is no way to determine what a
> user owns in some other database. Possibly you could do it if the quota
> were both per-user and per-database.

yes, agreed. I didn't think of that.

>> 3) How the quota should be controlled: I think, that generally, for all
>> the users which have quotas, the shared memory should contain the number
>> of blocks left from the quota. And each backend extending or truncating
>> the relations owned by the user should appropriately change that number of
>> blocks left in the shared memory.
> What will you do with ALTER TABLE OWNER? What if such a command is
> rolled back?

I don't know, but I guess the ALTER OWNER should be considered
differently. It probably should proceed only if it sees that there are
enough place to perform the whole operation. If there are, then it should
block any writing to the tables of the user, perform the alter owner and
unblock everything again.

> (Likewise for some other commands such as TRUNCATE, or
> even just DROP TABLE.)

I didn't think of yet, but I will.

> What if there are too many users to fit in your
> (necessarily fixed size) shared memory area?

We really don't need to create the array for all users. We only need to
create that array for users 1) having quotas 2) the users,
whose tables are accessed at the moment
So I don't think that in that case the amount of required space is a
problem here.

> What sort of contention
> will there be for access to this area?

I think, that the only requirement is that the incrementation or
decrementation of number of blocks left for each user
should be atomic operation.


Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
E-mail: math(at)sai(dot)msu(dot)ru

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2007-02-28 17:16:10 Re: COMMIT NOWAIT Performance Option
Previous Message Joshua D. Drake 2007-02-28 16:56:58 Re: SOC & user quotas