Skip site navigation (1) Skip section navigation (2)

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
Message-ID: Pine.LNX.4.64.0702282003110.14175@lnfm1.sai.msu.ru (view raw, whole thread or download thread mbox)
Thread:
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.

regards,
 	Sergey

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

In response to

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group