All,

Out of necessity, I've implemented user quotas in 7.4.3.  What would the 
process be for having this reviewed and combined?  I have a patch for 
7.4.3 ready, but wanted to know if you suggest that I patch the latest 
cvs instead.  Below if some information on the implementation.

=======================================================================
-- FACTS --------------------------------------------------------------
=======================================================================

1. Only a superuser can modify user quotas.
2. The minimum quota size is 1K.
3. The maximum quota size is (currently) the maximum of int4*1024 bytes.
4. Altering a user's quota does not make a change to their current data.
5. If you modify a user's quota to smaller than their current amount, 
they will be unable to add any more data... but their current schema is 
not truncated.

=======================================================================
-- SQL CHANGES --------------------------------------------------------
=======================================================================

     COMMAND: ALTER USER
 

     *** NEW SYNTAX ***
 

         ALTER USER name [ [ WITH ] option [ ... ] ]
 

         where option can be:
 

             [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
             | CREATEDB | NOCREATEDB
             | CREATEUSER | NOCREATEUSER
             | VALID UNTIL 'abstime'
             | QUOTA file_size | NOQUOTA
 

         where file_size is:
 

             integer[K | M | G]
 

             K = Kilobytes
             M = Megabytes
             G = Gigabytes

=======================================================================
-- USAGE EXAMPLES -----------------------------------------------------
=======================================================================
 

     *** VARIATIONS OF QUOTA SIZES ***
     ALTER USER someuser QUOTA 50M;
     ALTER USER someuser QUOTA 1G;
     ALTER USER someuser QUOTA 500K;
 

     *** GIVE USER AN UNLIMITED QUOTA ***
     ALTER USER someuser NOQUOTA;
 

=======================================================================
-- INTERNAL CHANGES ---------------------------------------------------
=======================================================================

pg_shadow has added attribute userquota of type int4
pg_user view has added attribute (pg_shadow.userquota) to selection.

ADDED KEYWORDS

     QUOTA
     NOQUOTA


-- 
Jonah H. Harris, UNIX Administrator  | phone: 505.224.4814
Albuquerque TVI                      | fax:   505.224.3014
525 Buena Vista SE                   | jharris@tvi.edu
Albuquerque, New Mexico 87106        | http://w3.tvi.edu/~jharris/

"All great truths begin as blasphemies."
                 --  George Bernard Shaw


Thu, 08 Jul 2004 15:27:34 -0600
"Jonah H. Harris" <jharris@tvi.edu>
User Quota Implementation
* Jonah H. Harris (jharris@tvi.edu) wrote:
> Out of necessity, I've implemented user quotas in 7.4.3.  What would the 
> process be for having this reviewed and combined?  I have a patch for 
> 7.4.3 ready, but wanted to know if you suggest that I patch the latest 
> cvs instead.  Below if some information on the implementation.

Personally, I would love to see this in PostgreSQL.  It'd be great if it
could get into 7.5.  An issue I see with that is that (similar to
Oracle...) I think people would want to be able to specify
per-tablespace quotas.  Perhaps that wouldn't be too hard to add?

	Stephen
Fri, 9 Jul 2004 08:31:04 -0400
Stephen Frost <sfrost@snowman.net>
Re: User Quota Implementation
> Personally, I would love to see this in PostgreSQL.  It'd be great if it
> could get into 7.5.  An issue I see with that is that (similar to
> Oracle...) I think people would want to be able to specify
> per-tablespace quotas.  Perhaps that wouldn't be too hard to add?

7.5 is already closed for new features I believe...

Chris


Fri, 09 Jul 2004 20:57:44 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: User Quota Implementation
* Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:
> >Personally, I would love to see this in PostgreSQL.  It'd be great if it
> >could get into 7.5.  An issue I see with that is that (similar to
> >Oracle...) I think people would want to be able to specify
> >per-tablespace quotas.  Perhaps that wouldn't be too hard to add?
> 
> 7.5 is already closed for new features I believe...

Quotas is part of a sane tablespace implementation, right? O;)

	Stephen
Fri, 9 Jul 2004 09:29:14 -0400
Stephen Frost <sfrost@snowman.net>
Re: User Quota Implementation
On Fri, 9 Jul 2004, Christopher Kings-Lynne wrote:

>> Personally, I would love to see this in PostgreSQL.  It'd be great if it
>> could get into 7.5.  An issue I see with that is that (similar to
>> Oracle...) I think people would want to be able to specify
>> per-tablespace quotas.  Perhaps that wouldn't be too hard to add?
>
> 7.5 is already closed for new features I believe...

Very much so ...


----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Fri, 9 Jul 2004 11:04:00 -0300 (ADT)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: User Quota Implementation
On Fri, 9 Jul 2004, Stephen Frost wrote:

> * Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:
>>> Personally, I would love to see this in PostgreSQL.  It'd be great if it
>>> could get into 7.5.  An issue I see with that is that (similar to
>>> Oracle...) I think people would want to be able to specify
>>> per-tablespace quotas.  Perhaps that wouldn't be too hard to add?
>>
>> 7.5 is already closed for new features I believe...
>
> Quotas is part of a sane tablespace implementation, right? O;)

So its still considered a 'missing feature', not a bug ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Fri, 9 Jul 2004 11:04:19 -0300 (ADT)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: User Quota Implementation
On Fri, 2004-07-09 at 09:29, Stephen Frost wrote:
> * Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:
> > >Personally, I would love to see this in PostgreSQL.  It'd be great if it
> > >could get into 7.5.  An issue I see with that is that (similar to
> > >Oracle...) I think people would want to be able to specify
> > >per-tablespace quotas.  Perhaps that wouldn't be too hard to add?
> > 
> > 7.5 is already closed for new features I believe...
> 
> Quotas is part of a sane tablespace implementation, right? O;)

I would think having would allow us to take advantage of all of the
various kernel level filesystem features without needing to implement
them directly within PostgreSQL (crypto, quotas, data mirror, etc.).

Simply setup a tablespace for a given user with permissions to allow
only that user to create new objects within it and make it the default
location) -- tie their schema to their tablespace? -- then set a kernel
level quota on their tablespace.


Or do we expect a PostgreSQL implementation to do more than that, to
only count active data by ignoring data pending a vacuum?



Fri, 09 Jul 2004 10:14:11 -0400
Rod Taylor <pg@rbt.ca>
Re: User Quota Implementation
On Thu, 8 Jul 2004, Jonah H. Harris wrote:

Hi,

> 3. The maximum quota size is (currently) the maximum of int4*1024 bytes.

why is this? This is very limiting ...
Using a 64bit value would be a lot more straight foreward.

Greetings, Klaus

-- 
Full Name   : Klaus Naumann     | (http://www.mgnet.de/) (Germany)
Phone / FAX : ++49/177/7862964  | E-Mail: (kn@mgnet.de)

Fri, 9 Jul 2004 16:16:42 +0200 (CEST)
Klaus Naumann <kn@mgnet.de>
Re: User Quota Implementation
On Fri, 2004-07-09 at 10:14, Rod Taylor wrote:
> On Fri, 2004-07-09 at 09:29, Stephen Frost wrote:
> > * Christopher Kings-Lynne (chriskl@familyhealth.com.au) wrote:
> > > >Personally, I would love to see this in PostgreSQL.  It'd be great if it
> > > >could get into 7.5.  An issue I see with that is that (similar to
> > > >Oracle...) I think people would want to be able to specify
> > > >per-tablespace quotas.  Perhaps that wouldn't be too hard to add?
> > > 
> > > 7.5 is already closed for new features I believe...
> > 
> > Quotas is part of a sane tablespace implementation, right? O;)
> 
> I would think having would allow us to take advantage of all of the
                     ^^^

having tablespaces would


Fri, 09 Jul 2004 10:19:16 -0400
Rod Taylor <pg@rbt.ca>
Re: User Quota Implementation
* Rod Taylor (pg@rbt.ca) wrote:
> I would think having would allow us to take advantage of all of the
> various kernel level filesystem features without needing to implement
> them directly within PostgreSQL (crypto, quotas, data mirror, etc.).
> 
> Simply setup a tablespace for a given user with permissions to allow
> only that user to create new objects within it and make it the default
> location) -- tie their schema to their tablespace? -- then set a kernel
> level quota on their tablespace.

Since the user accessing/writing to the tablespaces would be the
postgres user I don't really think this 'solution' works in reality.

> Or do we expect a PostgreSQL implementation to do more than that, to
> only count active data by ignoring data pending a vacuum?

Certainly, it should.

	Stephen
Fri, 9 Jul 2004 11:06:50 -0400
Stephen Frost <sfrost@snowman.net>
Re: User Quota Implementation
> > Simply setup a tablespace for a given user with permissions to allow
> > only that user to create new objects within it and make it the default
> > location) -- tie their schema to their tablespace? -- then set a kernel
> > level quota on their tablespace.
> 
> Since the user accessing/writing to the tablespaces would be the
> postgres user I don't really think this 'solution' works in reality.

I had assumed it would be a directory based quota rather than a user
based one.

> > Or do we expect a PostgreSQL implementation to do more than that, to
> > only count active data by ignoring data pending a vacuum?
> 
> Certainly, it should.

Okay. But just so we all know that this means the user with a 5MB quota
could still (potentially) fill 1TB of physical diskspace.



Fri, 09 Jul 2004 11:12:21 -0400
Rod Taylor <pg@rbt.ca>
Re: User Quota Implementation
* Rod Taylor (pg@rbt.ca) wrote:
> > > Simply setup a tablespace for a given user with permissions to allow
> > > only that user to create new objects within it and make it the default
> > > location) -- tie their schema to their tablespace? -- then set a kernel
> > > level quota on their tablespace.
> > 
> > Since the user accessing/writing to the tablespaces would be the
> > postgres user I don't really think this 'solution' works in reality.
> 
> I had assumed it would be a directory based quota rather than a user
> based one.

It's been a while since I played with quotas but I don't recall this
option being available.

> > > Or do we expect a PostgreSQL implementation to do more than that, to
> > > only count active data by ignoring data pending a vacuum?
> > 
> > Certainly, it should.
> 
> Okay. But just so we all know that this means the user with a 5MB quota
> could still (potentially) fill 1TB of physical diskspace.

Hmm, interesting point.  What are the options?  Make sure the user
understands they have to vacuum their tables in order to regain the
space?  Have two seperate values (similar to soft vs. hard limits) that
the admin sets?  Either (or both) of those seem reasonable to me.

	Stephen
Fri, 9 Jul 2004 11:15:35 -0400
Stephen Frost <sfrost@snowman.net>
Re: User Quota Implementation
> > > Since the user accessing/writing to the tablespaces would be the
> > > postgres user I don't really think this 'solution' works in reality.
> > 
> > I had assumed it would be a directory based quota rather than a user
> > based one.
> 
> It's been a while since I played with quotas but I don't recall this
> option being available.

Group quotas should be sufficient. Create directory readable/writable to
only the pgsql user, but have the group ownership be representative of
the user in question.


Fri, 09 Jul 2004 11:25:15 -0400
Rod Taylor <pg@rbt.ca>
Re: User Quota Implementation
* Rod Taylor (pg@rbt.ca) wrote:
> > > > Since the user accessing/writing to the tablespaces would be the
> > > > postgres user I don't really think this 'solution' works in reality.
> > > 
> > > I had assumed it would be a directory based quota rather than a user
> > > based one.
> > 
> > It's been a while since I played with quotas but I don't recall this
> > option being available.
> 
> Group quotas should be sufficient. Create directory readable/writable to
> only the pgsql user, but have the group ownership be representative of
> the user in question.

Rather ugly, and you'll run out of groups if you have alot of users (the
postgres user can only be in so many groups).  It's a cute idea but I
really don't see it as being viable.

	Stephen
Fri, 9 Jul 2004 11:31:31 -0400
Stephen Frost <sfrost@snowman.net>
Re: User Quota Implementation
On Thu, Jul 08, 2004 at 03:27:34PM -0600, Jonah H. Harris wrote:

> Out of necessity, I've implemented user quotas in 7.4.3.  What would the 
> process be for having this reviewed and combined?  I have a patch for 
> 7.4.3 ready, but wanted to know if you suggest that I patch the latest 
> cvs instead.  Below if some information on the implementation.

At exactly what time is the quota enforced?  Does the enforcement
somehow serialize that operation?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Thou shalt study thy libraries and strive not to reinvent them without
cause, that thy code may be short and readable and thy days pleasant
and productive. (7th Commandment for C Programmers)


Fri, 9 Jul 2004 11:35:52 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: User Quota Implementation
* Klaus Naumann (kn@mgnet.de) wrote:
> On Thu, 8 Jul 2004, Jonah H. Harris wrote:
> > 3. The maximum quota size is (currently) the maximum of int4*1024 bytes.
> 
> why is this? This is very limiting ...

It's 2TB...

> Using a 64bit value would be a lot more straight foreward.

It sounded to me like it might be a limitation forced by some other part
of postgres, but I don't really know...  Good question though.

	Stephen
Fri, 9 Jul 2004 11:47:26 -0400
Stephen Frost <sfrost@snowman.net>
Re: User Quota Implementation
There are a couple of modifications that I'd still like to make to user 
quotas.  Because 7.5 is locked, this may be a good time to discuss the 
implementation (possibly for 7.6?)

I have seen some discussion about using OS-level quotas on a user or 
group level, however, like our Oracle system, not all database users 
have a system account.  This is why I needed to implement user-specific 
quota functionality within the database itself.

Also note, my quota implementation currently expects a well-vacuumed 
database.  I always use pg_autovacuum but don't know about most other 
people.

I'd like to make the following changes...  Let me know your thoughts.

- Change userquota from int4 to int8.

- userquota is stored in units of kilobytes... is this adequate?  Would 
anyone ever use a quota < 1K other than for allowing a user no space? 
If the user has no space, why not just disable the account and/or make 
them read-only?

- Would anyone want to use a group quota in PGSQL (rather than user-only)?

- I assume that, based on discussion, not everyone sets up 
auto-vacuuming and therefore I would need to change the way I perform 
calculations.

- Quota acts on any object owned by the user.  Is this adequate for 
everyone?

- Hard limits vs. soft limits... does anyone think it's a good idea to 
truncate someone's data?  I personally don't think it's a good idea.

- What do you think about userquota being the attribute in pg_shadow... 
would you rather see something else.

Is there any additional functionality you would like to see in a quota 
implementation?

Would you rather see ALTER USER SET for quota rather than ALTER USER? 
Likewise, I had originally used ALTER USER username QUOTA UNLIMITED (in 
Oracle style) but found that it didn't meet PostgreSQL's common 
syntax... which is why I changed it to ALTER USER username NOQUOTA... 
does everyone agree with NOQUOTA over QUOTA UNLIMITED?

-Jonah

Stephen Frost wrote:
> * Rod Taylor (pg@rbt.ca) wrote:
> 
>>>>>Since the user accessing/writing to the tablespaces would be the
>>>>>postgres user I don't really think this 'solution' works in reality.
>>>>
>>>>I had assumed it would be a directory based quota rather than a user
>>>>based one.
>>>
>>>It's been a while since I played with quotas but I don't recall this
>>>option being available.
>>
>>Group quotas should be sufficient. Create directory readable/writable to
>>only the pgsql user, but have the group ownership be representative of
>>the user in question.
> 
> 
> Rather ugly, and you'll run out of groups if you have alot of users (the
> postgres user can only be in so many groups).  It's a cute idea but I
> really don't see it as being viable.
> 
> 	Stephen

-- 
Jonah H. Harris, UNIX Administrator  | phone: 505.224.4814
Albuquerque TVI                      | fax:   505.224.3014
525 Buena Vista SE                   | jharris@tvi.edu
Albuquerque, New Mexico 87106        | http://w3.tvi.edu/~jharris/

"All great truths begin as blasphemies."
                 --  George Bernard Shaw


Fri, 09 Jul 2004 10:04:01 -0600
"Jonah H. Harris" <jharris@tvi.edu>
Re: User Quota Implementation
> > Group quotas should be sufficient. Create directory readable/writable to
> > only the pgsql user, but have the group ownership be representative of
> > the user in question.
> 
> Rather ugly, and you'll run out of groups if you have alot of users (the
> postgres user can only be in so many groups).  It's a cute idea but I
> really don't see it as being viable.

The postgres user doesn't need to be in any of the groups, the group
simply exists simply to give the directory representation for a quota.


Fri, 09 Jul 2004 12:09:38 -0400
Rod Taylor <pg@rbt.ca>
Re: User Quota Implementation
Quota is currently enforced on commit.  I've considered checking during 
insert/update/copy and throwing an abort but within a transaction they 
may be deleting data as well.

However, even as a delete may take place before a massive insert/update, 
a vacuum cannot be run within a transaction block and therefore the 
commit would fail at the same point as an insert/update within the 
transaction itself.

So, essentially, there is no difference at which point to check other 
than to reduce the calculation overhead by placing the check at the 
commit point.

What's the consensus on when to check?

Alvaro Herrera wrote:
> On Thu, Jul 08, 2004 at 03:27:34PM -0600, Jonah H. Harris wrote:
> 
> 
>>Out of necessity, I've implemented user quotas in 7.4.3.  What would the 
>>process be for having this reviewed and combined?  I have a patch for 
>>7.4.3 ready, but wanted to know if you suggest that I patch the latest 
>>cvs instead.  Below if some information on the implementation.
> 
> 
> At exactly what time is the quota enforced?  Does the enforcement
> somehow serialize that operation?
> 

-- 
Jonah H. Harris, UNIX Administrator  | phone: 505.224.4814
Albuquerque TVI                      | fax:   505.224.3014
525 Buena Vista SE                   | jharris@tvi.edu
Albuquerque, New Mexico 87106        | http://w3.tvi.edu/~jharris/

"All great truths begin as blasphemies."
                 --  George Bernard Shaw


Fri, 09 Jul 2004 10:22:49 -0600
"Jonah H. Harris" <jharris@tvi.edu>
Re: User Quota Implementation
On Fri, 2004-07-09 at 11:47, Stephen Frost wrote:
> * Klaus Naumann (kn@mgnet.de) wrote:
> > On Thu, 8 Jul 2004, Jonah H. Harris wrote:
> > > 3. The maximum quota size is (currently) the maximum of int4*1024 bytes.
> > 
> > why is this? This is very limiting ...
> 
> It's 2TB...

Okay.. that is good for a few years. What do we do after 2007?



Fri, 09 Jul 2004 12:25:03 -0400
Rod Taylor <pg@rbt.ca>
Re: User Quota Implementation

On Jul 9, 2004, at 12:04 PM, Jonah H. Harris wrote:
>
>
> - Quota acts on any object owned by the user.  Is this adequate for 
> everyone?

Does changing owner also trigger new quota calculations on both the new 
and old owner?

> Is there any additional functionality you would like to see in a quota 
> implementation?

Quotas per user per tablespace, assuming 7.5 gets tablespaces.

User quotas would make postgres on a shared university box much more 
pleasant.
----
James Robinson
Socialserve.com


Fri, 9 Jul 2004 12:30:40 -0400
James Robinson <jlrobins@socialserve.com>
Re: User Quota Implementation
On Fri, 9 Jul 2004, Stephen Frost wrote:

Hi,

> > why is this? This is very limiting ...
>
> It's 2TB...

Yeah, you're right. I didn't take into account, that you multiply it with
1kb - my fault.
2TB is enough - at the moment at least. But implementing it in 64 from now
on could save a lot of work in the future ...

> It sounded to me like it might be a limitation forced by some other part
> of postgres, but I don't really know...  Good question though.

I'm not sure about it either - anyone?

Greetings, Klaus

-- 
Full Name   : Klaus Naumann     | (http://www.mgnet.de/) (Germany)
Phone / FAX : ++49/177/7862964  | E-Mail: (kn@mgnet.de)

Fri, 9 Jul 2004 18:31:30 +0200 (CEST)
Klaus Naumann <kn@mgnet.de>
Re: User Quota Implementation
On Fri, Jul 09, 2004 at 10:22:49AM -0600, Jonah H. Harris wrote:
> Quota is currently enforced on commit.  I've considered checking during 
> insert/update/copy and throwing an abort but within a transaction they 
> may be deleting data as well.

How do you do it?  Do you add relblocks from all tables and indexes?

> What's the consensus on when to check?

We don't have one AFAIK ...

You haven't shown us the patch, have you?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Granting software the freedom to evolve guarantees only different results,
not better ones." (Zygo Blaxell)


Fri, 9 Jul 2004 12:32:52 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: User Quota Implementation
On Fri, Jul 09, 2004 at 10:04:01AM -0600, Jonah H. Harris wrote:

> I'd like to make the following changes...  Let me know your thoughts.
> 
> - userquota is stored in units of kilobytes... is this adequate?  Would 
> anyone ever use a quota < 1K other than for allowing a user no space? 
> If the user has no space, why not just disable the account and/or make 
> them read-only?

We don't have the functionality for read-only, unless you REVOKE all his
privileges except select.

Anyway I think that you can't really enforce with such granularity, so
this is moot.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La felicidad no es maï¿½ana. La felicidad es ahora"


Fri, 9 Jul 2004 12:36:16 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: User Quota Implementation
* Rod Taylor (pg@rbt.ca) wrote:
> > > Group quotas should be sufficient. Create directory readable/writable to
> > > only the pgsql user, but have the group ownership be representative of
> > > the user in question.
> > 
> > Rather ugly, and you'll run out of groups if you have alot of users (the
> > postgres user can only be in so many groups).  It's a cute idea but I
> > really don't see it as being viable.
> 
> The postgres user doesn't need to be in any of the groups, the group
> simply exists simply to give the directory representation for a quota.

I'm not sure if this would really work...  Are you sure the quota would
be enforced against a user not in the group?  Of course, I still see it
as very ugly and a workaround at best...

	Stephen
Fri, 9 Jul 2004 12:44:39 -0400
Stephen Frost <sfrost@snowman.net>
Re: User Quota Implementation

James Robinson wrote:
> 
> On Jul 9, 2004, at 12:04 PM, Jonah H. Harris wrote:
> 
>>
>>
>> - Quota acts on any object owned by the user.  Is this adequate for 
>> everyone?
> 
> 
> Does changing owner also trigger new quota calculations on both the new 
> and old owner?
> 

Quota calculations are performed per-owner at commit time.  As only a 
superuser can perform an ALTER TABLE OWNER, my implementation allows 
quotas to be handled automatically at run-time for the current-owner. 
Therefore, there is no need to perform new calculations for each user. 
I'm still testing large objects.

>> Is there any additional functionality you would like to see in a quota 
>> implementation?
> 
> 
> Quotas per user per tablespace, assuming 7.5 gets tablespaces.

I agree.

> 
> User quotas would make postgres on a shared university box much more 
> pleasant.

Oh yeah!  Our college is using PostgreSQL for student user accounts, 
which is why I originally implemented this :).

> ----
> James Robinson
> Socialserve.com
> 

-- 
Jonah H. Harris, UNIX Administrator  | phone: 505.224.4814
Albuquerque TVI                      | fax:   505.224.3014
525 Buena Vista SE                   | jharris@tvi.edu
Albuquerque, New Mexico 87106        | http://w3.tvi.edu/~jharris/

"All great truths begin as blasphemies."
                 --  George Bernard Shaw


Fri, 09 Jul 2004 10:45:50 -0600
"Jonah H. Harris" <jharris@tvi.edu>
Re: User Quota Implementation
* Rod Taylor (pg@rbt.ca) wrote:
> On Fri, 2004-07-09 at 11:47, Stephen Frost wrote:
> > * Klaus Naumann (kn@mgnet.de) wrote:
> > > On Thu, 8 Jul 2004, Jonah H. Harris wrote:
> > > > 3. The maximum quota size is (currently) the maximum of int4*1024 bytes.
> > > 
> > > why is this? This is very limiting ...
> > 
> > It's 2TB...
> 
> Okay.. that is good for a few years. What do we do after 2007?

I was pointing out that it's not all *that* limiting.  If it's not too
difficult (ie: isn't something that affects disk layout or internal
postgres things..) I certainly don't have a problem w/ moving to a 64bit
int.

	Stephen
Fri, 9 Jul 2004 12:50:21 -0400
Stephen Frost <sfrost@snowman.net>
Re: User Quota Implementation


Stephen Frost wrote:
> * Rod Taylor (pg@rbt.ca) wrote:
> 
>>On Fri, 2004-07-09 at 11:47, Stephen Frost wrote:
>>
>>>* Klaus Naumann (kn@mgnet.de) wrote:
>>>
>>>>On Thu, 8 Jul 2004, Jonah H. Harris wrote:
>>>>
>>>>>3. The maximum quota size is (currently) the maximum of int4*1024 bytes.
>>>>
>>>>why is this? This is very limiting ...
>>>
>>>It's 2TB...
>>
>>Okay.. that is good for a few years. What do we do after 2007?
> 
> 
> I was pointing out that it's not all *that* limiting.  If it's not too
> difficult (ie: isn't something that affects disk layout or internal
> postgres things..) I certainly don't have a problem w/ moving to a 64bit
> int.
> 
> 	Stephen

My thinking was, if you're allowing a user to use that much space, 
they're probably better off with an unlimited quota... unless you don't 
vacuum often and there are heavy updates/deletes performed on that 
user's relations.

Internally there isn't a problem (as I see it) with using a bigger data 
type.

-- 
Jonah H. Harris, UNIX Administrator  | phone: 505.224.4814
Albuquerque TVI                      | fax:   505.224.3014
525 Buena Vista SE                   | jharris@tvi.edu
Albuquerque, New Mexico 87106        | http://w3.tvi.edu/~jharris/

"All great truths begin as blasphemies."
                 --  George Bernard Shaw


Fri, 09 Jul 2004 10:59:50 -0600
"Jonah H. Harris" <jharris@tvi.edu>
Re: User Quota Implementation
On Fri, 9 Jul 2004, Jonah H. Harris wrote:

> - Would anyone want to use a group quota in PGSQL (rather than user-only)?

Yes ... I could see this as being more useful, not less ... where you have 
a dept working on a database, but individual logins for audit logging ...

> - I assume that, based on discussion, not everyone sets up 
> auto-vacuuming and therefore I would need to change the way I perform 
> calculations.

With 7.5 and beyond, what is the chances that auto-vacuuming isn't used? 
It would definitely change the direction of questions from "why is my 
database so slow?" to "why is it telling me I'm out of space when my hard 
drive is empty?" ... the fix for the second would eliminate the first :)

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Fri, 9 Jul 2004 15:23:29 -0300 (ADT)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: User Quota Implementation


Stephen Frost wrote:
> * Rod Taylor (pg@rbt.ca) wrote:
> 
>>>>Simply setup a tablespace for a given user with permissions to allow
>>>>only that user to create new objects within it and make it the default
>>>>location) -- tie their schema to their tablespace? -- then set a kernel
>>>>level quota on their tablespace.
>>>
>>>Since the user accessing/writing to the tablespaces would be the
>>>postgres user I don't really think this 'solution' works in reality.
>>
>>I had assumed it would be a directory based quota rather than a user
>>based one.
> 
> 
> It's been a while since I played with quotas but I don't recall this
> option being available.
> 
> 
>>>>Or do we expect a PostgreSQL implementation to do more than that, to
>>>>only count active data by ignoring data pending a vacuum?
>>>
>>>Certainly, it should.
>>
>>Okay. But just so we all know that this means the user with a 5MB quota
>>could still (potentially) fill 1TB of physical diskspace.
> 
> 
> Hmm, interesting point.  What are the options?  Make sure the user
> understands they have to vacuum their tables in order to regain the
> space?  Have two seperate values (similar to soft vs. hard limits) that
> the admin sets?  Either (or both) of those seem reasonable to me.
> 
> 	Stephen

So I'm clear, the soft limit being actual data and the hard limit being 
unvacuumed space?  How many people don't have auto vacuum set up?

While Oracle doesn't have the vacuum problem, its quota implementation 
simply limits the collective amount of space a user can consume in a 
tablespace... do we want to deviate from this somewhat standard approach?

I don't see the value in letting a user with a 5M quota take up as much 
space as they want.  Otherwise, how are they really saving any space at 
all?  IMO, I think that if a pgsql admin wants to implement quotas they 
should understand the auto vacuum requirement.  Also, it would add a 
great deal of complexity and computation time to calculate the soft 
limit on every commit rather than requiring a vacuum analyze.



-- 
Jonah H. Harris, UNIX Administrator  | phone: 505.224.4814
Albuquerque TVI                      | fax:   505.224.3014
525 Buena Vista SE                   | jharris@tvi.edu
Albuquerque, New Mexico 87106        | http://w3.tvi.edu/~jharris/

"All great truths begin as blasphemies."
                 --  George Bernard Shaw


Fri, 09 Jul 2004 16:28:48 -0600
"Jonah H. Harris" <jharris@tvi.edu>
Re: User Quota Implementation
[ catching up on this discussion a bit late... ]

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> You haven't shown us the patch, have you?

That was pretty much the point that leapt out at me.  For a change of
this magnitude, there is absolutely zero chance that we'll accept an
implementation sight unseen.  Let's see a proof-of-concept patch...

			regards, tom lane

Sat, 10 Jul 2004 01:25:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: User Quota Implementation 
this leads me to the first question I asked... do you want me to pull 
the latest cvs and patch it... or distribute my patch for 7.4.3?

Tom Lane wrote:
> [ catching up on this discussion a bit late... ]
> 
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> 
>>You haven't shown us the patch, have you?
> 
> 
> That was pretty much the point that leapt out at me.  For a change of
> this magnitude, there is absolutely zero chance that we'll accept an
> implementation sight unseen.  Let's see a proof-of-concept patch...
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

-- 
Jonah H. Harris, UNIX Administrator  | phone: 505.224.4814
Albuquerque TVI                      | fax:   505.224.3014
525 Buena Vista SE                   | jharris@tvi.edu
Albuquerque, New Mexico 87106        | http://w3.tvi.edu/~jharris/

"All great truths begin as blasphemies."
                  --  George Bernard Shaw

Sat, 10 Jul 2004 00:05:11 -0600
"Jonah H. Harris" <jharris@tvi.edu>
Re: User Quota Implementation
"Jonah H. Harris" <jharris@tvi.edu> writes:

> this leads me to the first question I asked... do you want me to pull
> the latest cvs and patch it... or distribute my patch for 7.4.3?

Latest CVS, no question.  It would be going into 7.6 (or whatever) T
the earliest...

-Doug

Sat, 10 Jul 2004 07:28:21 -0400
Doug McNaught <doug@mcnaught.org>
Re: User Quota Implementation
"Jonah H. Harris" <jharris@tvi.edu> writes:
>>> You haven't shown us the patch, have you?
>
> this leads me to the first question I asked... do you want me to pull 
> the latest cvs and patch it... or distribute my patch for 7.4.3?

Well, we will not be applying any such patch to 7.4.*, so if you want
to submit something that's likely to get applied then you'll need to
update it to CVS tip.  But as long as we're at the feedback stage I'd
counsel just showing us what you have for 7.4.*.  There's no point in
doing more work till you have a good reading on whether it will be
accepted.

(Also, you probably may as well wait till after 7.6 development starts
before trying to update the patch... there will be at least one pgindent
run before 7.6, and that is likely to break pending patches...)

			regards, tom lane

Sat, 10 Jul 2004 12:13:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: User Quota Implementation 
Hi,

did anything happen to implementing quotas, yet?
though I did not see anything on the TODO List I was wondering what is
going on.

Regards,
Yann

Fri, 10 Jun 2005 10:06:00 +0200
Yann Michel <yann-postgresql@spline.de>
Re: User Quota Implementation
Yann Michel wrote:
> Hi,
> 
> did anything happen to implementing quotas, yet?
> though I did not see anything on the TODO List I was wondering what is
> going on.

No work has been done on it, and I don't even see a TODO item for it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 10 Jun 2005 09:45:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: User Quota Implementation
Hi Bruce,

On Fri, Jun 10, 2005 at 09:45:32AM -0400, Bruce Momjian wrote:
> > did anything happen to implementing quotas, yet?
> > though I did not see anything on the TODO List I was wondering what is
> > going on.
> 
> No work has been done on it, and I don't even see a TODO item for it.

Do you think that it is possible that one can generate a TODO item out
of the request or do you rather think different?

Regards,
Yann

Fri, 10 Jun 2005 16:04:09 +0200
Yann Michel <yann-postgresql@spline.de>
Re: User Quota Implementation
Yann Michel wrote:
> Hi Bruce,
> 
> On Fri, Jun 10, 2005 at 09:45:32AM -0400, Bruce Momjian wrote:
> > > did anything happen to implementing quotas, yet?
> > > though I did not see anything on the TODO List I was wondering what is
> > > going on.
> > 
> > No work has been done on it, and I don't even see a TODO item for it.
> 
> Do you think that it is possible that one can generate a TODO item out
> of the request or do you rather think different?

Yes, sure.  Ah, I found it.  TODO has now:

	* Allow limits on per-db/user connections

That is pretty vague, but it is all we have so far.  In fact, that
refers more to the number of connections rather than say disk space or
CPU.  The issue we have had with these issues in the past is that we
aren't sure how such limits would be implemented or used.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 10 Jun 2005 10:07:59 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: User Quota Implementation
On Fri, Jun 10, 2005 at 10:07:59AM -0400, Bruce Momjian wrote:
> > Do you think that it is possible that one can generate a TODO item out
> > of the request or do you rather think different?
> 
> Yes, sure.  Ah, I found it.  TODO has now:
> 
> 	* Allow limits on per-db/user connections

Fine!

> That is pretty vague, but it is all we have so far.  In fact, that
> refers more to the number of connections rather than say disk space or
> CPU.  The issue we have had with these issues in the past is that we
> aren't sure how such limits would be implemented or used.

Well, I have realy a lot of experiences with oracle usage and with its
limitation capabilities. What I need the most is space-limitation per
tablespace. Since 9i there is also a possibility to restrict cpu-usage
for a certain consumer or group but in fact I din't need to to so since
most of the apps have their own database. Maybe it could be useful to
have these groups later on. As far as I understood the thread above (and
any other mails) the space limitation would not only be nice for me.

BTW: Is there any patch available, yet? This thread dealt with a "patch"
but I didn't see any!?

Regards,
Yann

Fri, 10 Jun 2005 16:51:21 +0200
Yann Michel <yann-postgresql@spline.de>
Re: User Quota Implementation
Yann Michel wrote:
> On Fri, Jun 10, 2005 at 10:07:59AM -0400, Bruce Momjian wrote:
> > > Do you think that it is possible that one can generate a TODO item out
> > > of the request or do you rather think different?
> > 
> > Yes, sure.  Ah, I found it.  TODO has now:
> > 
> > 	* Allow limits on per-db/user connections
> 
> Fine!
> 
> > That is pretty vague, but it is all we have so far.  In fact, that
> > refers more to the number of connections rather than say disk space or
> > CPU.  The issue we have had with these issues in the past is that we
> > aren't sure how such limits would be implemented or used.
> 
> Well, I have realy a lot of experiences with oracle usage and with its
> limitation capabilities. What I need the most is space-limitation per
> tablespace. Since 9i there is also a possibility to restrict cpu-usage
> for a certain consumer or group but in fact I din't need to to so since
> most of the apps have their own database. Maybe it could be useful to
> have these groups later on. As far as I understood the thread above (and
> any other mails) the space limitation would not only be nice for me.

I assume you can't use file system quotas for the tablespace partitions?
> 
> BTW: Is there any patch available, yet? This thread dealt with a "patch"
> but I didn't see any!?

Oh, there is no patch, just a discussion.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 10 Jun 2005 10:59:46 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: User Quota Implementation
Hi,

On Fri, Jun 10, 2005 at 10:59:46AM -0400, Bruce Momjian wrote:
> > Well, I have realy a lot of experiences with oracle usage and with its
> > limitation capabilities. What I need the most is space-limitation per
> > tablespace. Since 9i there is also a possibility to restrict cpu-usage
> > for a certain consumer or group but in fact I din't need to to so since
> > most of the apps have their own database. Maybe it could be useful to
> > have these groups later on. As far as I understood the thread above (and
> > any other mails) the space limitation would not only be nice for me.
> 
> I assume you can't use file system quotas for the tablespace partitions?

No, that's definetely no solution, due to I'm interested in a general
solution which should be applicable for all platforms.

> > BTW: Is there any patch available, yet? This thread dealt with a "patch"
> > but I didn't see any!?
> 
> Oh, there is no patch, just a discussion.

O.K. so I was not wrong about that. Is it usefull to re-discuss some of
the aspects to get a gist of what should probably be
implemented/extended?

Regards,
Yann

Fri, 10 Jun 2005 18:26:29 +0200
Yann Michel <yann-postgresql@spline.de>
Re: User Quota Implementation
Yann Michel wrote:
> Hi,
> 
> On Fri, Jun 10, 2005 at 10:59:46AM -0400, Bruce Momjian wrote:
> > > Well, I have realy a lot of experiences with oracle usage and with its
> > > limitation capabilities. What I need the most is space-limitation per
> > > tablespace. Since 9i there is also a possibility to restrict cpu-usage
> > > for a certain consumer or group but in fact I din't need to to so since
> > > most of the apps have their own database. Maybe it could be useful to
> > > have these groups later on. As far as I understood the thread above (and
> > > any other mails) the space limitation would not only be nice for me.
> > 
> > I assume you can't use file system quotas for the tablespace partitions?
> 
> No, that's definetely no solution, due to I'm interested in a general
> solution which should be applicable for all platforms.
> 
> > > BTW: Is there any patch available, yet? This thread dealt with a "patch"
> > > but I didn't see any!?
> > 
> > Oh, there is no patch, just a discussion.
> 
> O.K. so I was not wrong about that. Is it usefull to re-discuss some of
> the aspects to get a gist of what should probably be
> implemented/extended?

Sure.  Basically there has not been a lot of interest in this, and we
are not sure how to implement it without a huge amount of work. 
Considering the other things we are working on, it hasn't been a
priority, and lots of folks don't like the Oracle approach either.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 10 Jun 2005 12:33:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: User Quota Implementation
Bruce, Yann,

> Sure.  Basically there has not been a lot of interest in this, and we
> are not sure how to implement it without a huge amount of work.
> Considering the other things we are working on, it hasn't been a
> priority, and lots of folks don't like the Oracle approach either.

Yeah.  I'd prefer per-database quotas, rather than per-user quotas, which 
seem kind of useless.   The hard part is making any transaction which 
would exceed the per-database quota roll back cleanly with a 
comprehensible error message rather than just having the database shut 
down.

If we had per-database user quotas, and per-database users, it would pretty 
much wind up all of the issues which ISPs have with Postgres.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Fri, 10 Jun 2005 10:13:52 -0700
Josh Berkus <josh@agliodbs.com>
Re: User Quota Implementation
Josh Berkus <josh@agliodbs.com> writes:
> Yeah.  I'd prefer per-database quotas, rather than per-user quotas, which 
> seem kind of useless.   The hard part is making any transaction which 
> would exceed the per-database quota roll back cleanly with a 
> comprehensible error message rather than just having the database shut 
> down.

That part doesn't seem hard to me: we already recover reasonably well
from smgrextend failures.  The real difficulty is in monitoring the
total database size to know when it's time to complain.  We don't
currently make any effort at all to measure that, let alone keep track
of it in real time.

Given that there might be lots of processes concurrently adding pages
in different places, I don't think you could hope for an exact
stop-on-a-dime limit, but maybe if you're willing to accept some fuzz
it is doable ...

			regards, tom lane

Fri, 10 Jun 2005 13:37:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: User Quota Implementation 
Hi Josh!

On Fri, Jun 10, 2005 at 10:13:52AM -0700, Josh Berkus wrote:
> 
> Yeah.  I'd prefer per-database quotas, rather than per-user quotas, which 
> seem kind of useless.   The hard part is making any transaction which 
> would exceed the per-database quota roll back cleanly with a 
> comprehensible error message rather than just having the database shut 
> down.
> 
> If we had per-database user quotas, and per-database users, it would pretty 
> much wind up all of the issues which ISPs have with Postgres.

O.K. This makes sens to me. Otherwise I'd like to see quotas per
tablespace. As far as I got it, a tablespace may grow in size untile the
volume is full. Here a grace quota might be usefull as well. Let's say a
5% threshold like the ext filesystem as an default for generating a
warning to th elogs files letting the admin extend the volum(s) by time.

Regards,
Yann

Fri, 10 Jun 2005 21:16:15 +0200
Yann Michel <yann-postgresql@spline.de>
Re: User Quota Implementation
Hi Tom,

On Fri, Jun 10, 2005 at 01:37:54PM -0400, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> > Yeah.  I'd prefer per-database quotas, rather than per-user quotas, which 
> > seem kind of useless.   The hard part is making any transaction which 
> > would exceed the per-database quota roll back cleanly with a 
> > comprehensible error message rather than just having the database shut 
> > down.
> 
> That part doesn't seem hard to me: we already recover reasonably well
> from smgrextend failures.  The real difficulty is in monitoring the
> total database size to know when it's time to complain.  We don't
> currently make any effort at all to measure that, let alone keep track
> of it in real time.
> 
> Given that there might be lots of processes concurrently adding pages
> in different places, I don't think you could hope for an exact
> stop-on-a-dime limit, but maybe if you're willing to accept some fuzz
> it is doable ...

Well I think a fuzzy test is better than none. But I think one should be
able to calculate how much later the quota is detected as exceeded than
it is planed to be. Therefor a threshold is usefull as well (for
alerting)

Regards,
Yann

Fri, 10 Jun 2005 21:18:48 +0200
Yann Michel <yann-postgresql@spline.de>
Re: User Quota Implementation
Yann,

> O.K. This makes sens to me. Otherwise I'd like to see quotas per
> tablespace. As far as I got it, a tablespace may grow in size untile the
> volume is full. Here a grace quota might be usefull as well. Let's say a
> 5% threshold like the ext filesystem as an default for generating a
> warning to th elogs files letting the admin extend the volum(s) by time.

Hmmm ... Tablespace quotas would be *even more* useful than database 
quotas.  If it's just as easy for you?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Fri, 10 Jun 2005 14:25:11 -0700
Josh Berkus <josh@agliodbs.com>
Re: User Quota Implementation
I have a patch for user quotas in (I think) 7.4.2.  I was going to 
update it for 8.x but have been too busy.  The discussion (in the past) 
was related to whether quotas would be applied to users or groups and 
whether it would be on tablespaces (which I think it should).

I can spend some time reviving the patch this weekend if it is of 
interest to you.  Just let me know.

-Jonah

Josh Berkus wrote:

>Yann,
>
>  
>
>>O.K. This makes sens to me. Otherwise I'd like to see quotas per
>>tablespace. As far as I got it, a tablespace may grow in size untile the
>>volume is full. Here a grace quota might be usefull as well. Let's say a
>>5% threshold like the ext filesystem as an default for generating a
>>warning to th elogs files letting the admin extend the volum(s) by time.
>>    
>>
>
>Hmmm ... Tablespace quotas would be *even more* useful than database 
>quotas.  If it's just as easy for you?
>
>  
>


Fri, 10 Jun 2005 15:58:21 -0600
"Jonah H. Harris" <jharris@tvi.edu>
Re: User Quota Implementation
Hi Josh,

On Fri, Jun 10, 2005 at 02:25:11PM -0700, Josh Berkus wrote:
> > O.K. This makes sens to me. Otherwise I'd like to see quotas per
> > tablespace. As far as I got it, a tablespace may grow in size untile the
> > volume is full. Here a grace quota might be usefull as well. Let's say a
> > 5% threshold like the ext filesystem as an default for generating a
> > warning to th elogs files letting the admin extend the volum(s) by time.
> 
> Hmmm ... Tablespace quotas would be *even more* useful than database 
> quotas.  If it's just as easy for you?

Well, lets see...

What do we need:

- Extension of the "CREATE TABLESPACE" command:
  CREATE TABLESPACE tablespacename 
        [ OWNER username ] 
        [ SIZE <integer><K | M | G | T> ] 
        LOCATION 'directory'

- Extension of the "ALTER TABLESPACE" command:
  ALTER TABLESPACE name 
        {RENAME TO newname |
        SIZE <integer><K | M | G | T> }

- Storage of this information in the system "tablespace" relation
  
- Determine the actual size of a tables space
  --> Already exists in contrib/dbsize/dbsize.c

- Define the point in time where this calculation should happen.
  That's the point where I think some lazyness may appear, i.e. it is
  enough to evaluate the size from time to time but not after each
  statement. Of cause this will enable that a tablespace may become to
  large but once it is to large, further extensions of it will become 
  prohibited.

- Define how to disable further extension of tablespace objects or
  creation of new ones.

- Optional: Define postgresql.conf parameter:
        "tablesspace_full_warning = 90"
        Whenever the threshold of 90 percent is reached a warning will
        be generated (and written to the log-files)

So far from me about my thoughts...

Regards,
Yann

Sat, 11 Jun 2005 10:49:26 +0200
Yann Michel <yann-postgresql@spline.de>
Re: User Quota Implementation