Re: User Quota Implementation

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(at)tvi(dot)edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/

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

* Jonah H. Harris (jharris(at)tvi(dot)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

> 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

* Christopher Kings-Lynne (chriskl(at)familyhealth(dot)com(dot)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

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(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

On Fri, 9 Jul 2004, Stephen Frost wrote:

> * Christopher Kings-Lynne (chriskl(at)familyhealth(dot)com(dot)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(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

On Fri, 2004-07-09 at 09:29, Stephen Frost wrote:
> * Christopher Kings-Lynne (chriskl(at)familyhealth(dot)com(dot)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?

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(at)mgnet(dot)de)

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(at)familyhealth(dot)com(dot)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

* Rod Taylor (pg(at)rbt(dot)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

> > 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.

* Rod Taylor (pg(at)rbt(dot)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

> > > 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.

* Rod Taylor (pg(at)rbt(dot)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

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)

* Klaus Naumann (kn(at)mgnet(dot)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

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(at)rbt(dot)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(at)tvi(dot)edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/

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

> > 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.

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(at)tvi(dot)edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/

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

On Fri, 2004-07-09 at 11:47, Stephen Frost wrote:
> * Klaus Naumann (kn(at)mgnet(dot)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?

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

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(at)mgnet(dot)de)

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)

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"

* Rod Taylor (pg(at)rbt(dot)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

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(at)tvi(dot)edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/

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

* Rod Taylor (pg(at)rbt(dot)ca) wrote:
> On Fri, 2004-07-09 at 11:47, Stephen Frost wrote:
> > * Klaus Naumann (kn(at)mgnet(dot)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

Stephen Frost wrote:
> * Rod Taylor (pg(at)rbt(dot)ca) wrote:
>
>>On Fri, 2004-07-09 at 11:47, Stephen Frost wrote:
>>
>>>* Klaus Naumann (kn(at)mgnet(dot)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(at)tvi(dot)edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/

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

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(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

Stephen Frost wrote:
> * Rod Taylor (pg(at)rbt(dot)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(at)tvi(dot)edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/

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

[ catching up on this discussion a bit late... ]

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)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

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(at)dcc(dot)uchile(dot)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(at)tvi(dot)edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/

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

"Jonah H. Harris" <jharris(at)tvi(dot)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

"Jonah H. Harris" <jharris(at)tvi(dot)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

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

Josh Berkus <josh(at)agliodbs(dot)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

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

Hi Tom,

On Fri, Jun 10, 2005 at 01:37:54PM -0400, Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)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

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

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?
>
>
>

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