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

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

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

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

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?

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)

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

* 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

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

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

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

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

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@tvi.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@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?

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

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

* 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

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

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

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

Jonah,

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.

Agreed. Also, implementing quotas on a filesystem level is an administrative
pain.

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

That's a reasonable limitation from my perspective, especially since we're
merging autovacuum into the back-end.

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

Yes, it's fine. I can't imagine needing a granularity < 1K. In fact, I
personally can't imagine using a granularity < 1mb, but that may be just me.

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

I can imagine this, yes.

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

More that auto-vacuuming does not fix the issue for a high-activity database,
or at least that you have to set quotas at 50% of the available space.
Also, keep in mind that these quotas would not at all restrict the use of
xlog or swap space, so your users could still run you over.

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

For me, yes.

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

No, it's not a good idea. If the user goes over quota, it should exception
and their transaction should abort.

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

A pg_catalog view that allows seeing the user quotas and what % full they are.

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?

No opinion.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

[ 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

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

"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

"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

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

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

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

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

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

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

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:

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

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Yann Michel
Sent: 11 June 2005 09:49
To: Josh Berkus
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] User Quota Implementation

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

Wouldn't MAXSIZE be more appropriate?

- Storage of this information in the system "tablespace" relation

- Determine the actual size of a tables space
--> Already exists in contrib/dbsize/dbsize.c

A patch was recently submitted by Andreas Pflug to move those functions
into the backend permanently.

Regards, Dave.

Hi,

On Sat, Jun 11, 2005 at 05:36:34PM +0100, Dave Page wrote:

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

Wouldn't MAXSIZE be more appropriate?

Yes, of cause.

- Storage of this information in the system "tablespace" relation

- Determine the actual size of a tables space
--> Already exists in contrib/dbsize/dbsize.c

A patch was recently submitted by Andreas Pflug to move those functions
into the backend permanently.

I think that's why I found it there ;-)

Regards,
Yann

Jonah,

A quota is significantly different from a maximum size. I was thinking
more along the lines of the following:

Hmmm. Can you give me a case where we need per-user quotas that would not be
satisfied by tablespace maximums? I'm not understanding the rationale, and
I see several serious implementation issues with user-based quotas. But I'm
not the target audience so maybe I just don't understand.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh,

Don't get me wrong, I think we need tablespace maximums. What I'm
looking at is a user/group-based quota which would allow a superuser to
grant say, 2G of space to a user or group. Any object that user owned
would be included in the space allocation.

So, if the user owns three tablespaces, they can still only have a
maximum of 2G total. This is where I think it would be wise to allow
the tablespace owner and/or superuser to set the maximum size of a
tablespace.

As I see it, these seem to be two distinct issues. Is this correct?

Josh Berkus wrote:

Jonah,

A quota is significantly different from a maximum size. I was thinking
more along the lines of the following:

Hmmm. Can you give me a case where we need per-user quotas that would not be
satisfied by tablespace maximums? I'm not understanding the rationale, and
I see several serious implementation issues with user-based quotas. But I'm
not the target audience so maybe I just don't understand.

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

A hacker on a roll may be able to produce, in a period of a few
months, something that a small development group (say, 7-8 people)
would have a hard time getting together over a year. IBM used to
report that certain programmers might be as much as 100 times as
productive as other workers, or more.

-- Peter Seebach

Jonah,

Don't get me wrong, I think we need tablespace maximums. What I'm
looking at is a user/group-based quota which would allow a superuser to
grant say, 2G of space to a user or group. Any object that user owned
would be included in the space allocation.

So, if the user owns three tablespaces, they can still only have a
maximum of 2G total. This is where I think it would be wise to allow
the tablespace owner and/or superuser to set the maximum size of a
tablespace.

Yeah, the problem is that with the upcoming "group ownership" I see
user-based quotas as being rather difficult to implement unambiguously.
Even more so when we get "local users" in the future. So I'd only want
to do it if there was a real-world use case that tablespace quotas
wouldn't satisfy.

For the basic ISP space, tablespace quotas seem a lot more apt for that
case. You give each user a database, and put it in its own tablespace and
don't give them permissions to change it. That way you could have user
e-mail, web, and database in the same directory tree for easy
backup/transfer. It also means that you can use filesystem controls to
double-check the tablespace maximums.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

* Josh Berkus (josh@agliodbs.com) wrote:

Yeah, the problem is that with the upcoming "group ownership" I see
user-based quotas as being rather difficult to implement unambiguously.

I'm not sure it'd be terribly different with roles than with
user/groups. A role gets a quota, anything which that role is the owner
of counts towards that quota. The only possibly annoying part about
this is that there isn't a direct way (yet) to create an object owned by
someone other than yourself.

An example:

Role john isn't really supposted to use up much disk space.
Role admin can use up lots of disk space.
Role john is a member of role admin.

In order to use up much disk space, john needs to, say, create a table
and then change ownership to admin before populating that table. An
interesting idea would be to allow for a session variable which the user
could set to a particular role (which that user is a member of) and have
that role then own any objects created. In that instance it's possible
john's quota could be 0.

I'm not entirely sure if that's something CURRENT_USER/SESSION_USER/etc
could be correctly used for.

Even more so when we get "local users" in the future. So I'd only want
to do it if there was a real-world use case that tablespace quotas
wouldn't satisfy.

Local users actually makes me think the per-user quota would make *more*
sense, following along your example below...

For the basic ISP space, tablespace quotas seem a lot more apt for that
case. You give each user a database, and put it in its own tablespace and
don't give them permissions to change it. That way you could have user
e-mail, web, and database in the same directory tree for easy
backup/transfer. It also means that you can use filesystem controls to
double-check the tablespace maximums.

In the basic ISP scenario, you give each customer a database, in its own
tablespace, with quotas on that tablespace. With local roles that
customer may then have multiple users and want to establish different
quotas for them.

Just some thoughts.

Thanks,

Stephen

Hi Josh, hi jonah,

On Mon, Jun 13, 2005 at 12:36:12PM -0700, Josh Berkus wrote:

Don't get me wrong, I think we need tablespace maximums. What I'm
looking at is a user/group-based quota which would allow a superuser to
grant say, 2G of space to a user or group. Any object that user owned
would be included in the space allocation.

So, if the user owns three tablespaces, they can still only have a
maximum of 2G total. This is where I think it would be wise to allow
the tablespace owner and/or superuser to set the maximum size of a
tablespace.

Yeah, the problem is that with the upcoming "group ownership" I see
user-based quotas as being rather difficult to implement unambiguously.
Even more so when we get "local users" in the future. So I'd only want
to do it if there was a real-world use case that tablespace quotas
wouldn't satisfy.

Well, I think in one way jona is right, that I mixed up two things.
Indeed a max size for a tablespace is something different, than a quota.
In my opinion, it makes only sense to use quotas for ressource-owners on
ressources, i.e. tablespaces. To as an example I think about some
tablespace whith a MAXSIZE of 2 GB (that it won't grow until the disk is
full) and a QUOTA of 500 MB for user A on that certain tablespace. In
general (of cause this is only my experience in using quotas in dbms)
you will create different tablespaces for different object kinds/types
i.e. one for indexes, one for dimensions and at least one for the fact
data in a dwh. So to allow users to store their comparable tables in the
appropriate tablespace you'd set up a quota for them.

Regards,
Yann

Well... a maximum tablespace size would be much easier to implement and
would still accomplish this level of quota for larger organizations and
database systems.

I vote for implmenting the maximum tablespace size and revisiting actual
user/group quotas when the need arises.

Was someone going to implement this? If not, I can probably get it done
in a couple days.

-Jonah

Yann Michel wrote:

Hi Josh, hi jonah,

On Mon, Jun 13, 2005 at 12:36:12PM -0700, Josh Berkus wrote:

Don't get me wrong, I think we need tablespace maximums. What I'm
looking at is a user/group-based quota which would allow a superuser to
grant say, 2G of space to a user or group. Any object that user owned
would be included in the space allocation.

So, if the user owns three tablespaces, they can still only have a
maximum of 2G total. This is where I think it would be wise to allow
the tablespace owner and/or superuser to set the maximum size of a
tablespace.

Yeah, the problem is that with the upcoming "group ownership" I see
user-based quotas as being rather difficult to implement unambiguously.
Even more so when we get "local users" in the future. So I'd only want
to do it if there was a real-world use case that tablespace quotas
wouldn't satisfy.

Well, I think in one way jona is right, that I mixed up two things.
Indeed a max size for a tablespace is something different, than a quota.
In my opinion, it makes only sense to use quotas for ressource-owners on
ressources, i.e. tablespaces. To as an example I think about some
tablespace whith a MAXSIZE of 2 GB (that it won't grow until the disk is
full) and a QUOTA of 500 MB for user A on that certain tablespace. In
general (of cause this is only my experience in using quotas in dbms)
you will create different tablespaces for different object kinds/types
i.e. one for indexes, one for dimensions and at least one for the fact
data in a dwh. So to allow users to store their comparable tables in the
appropriate tablespace you'd set up a quota for them.

Regards,
Yann

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

A hacker on a roll may be able to produce, in a period of a few
months, something that a small development group (say, 7-8 people)
would have a hard time getting together over a year. IBM used to
report that certain programmers might be as much as 100 times as
productive as other workers, or more.

-- Peter Seebach

Jonah,

Was someone going to implement this?  If not, I can probably get it done
in a couple days.

Don't let me stop you.

I'd like to avoid a GUC for "percent_full_warning" if we can. Can anyone
see a way around this? Should we just assume 90% full?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:

Yeah, the problem is that with the upcoming "group ownership" I see
user-based quotas as being rather difficult to implement unambiguously.
Even more so when we get "local users" in the future. So I'd only want
to do it if there was a real-world use case that tablespace quotas
wouldn't satisfy.

There's also the point that having both user- and tablespace-related
limits would mean (at least) double the implementation overhead, for
a lot less than double the usefulness.

I'm with Josh on this one: I want to see something a lot more convincing
than "it would be nice" or "Oracle has it" before buying into more than
one type of quota.

BTW, I think it is actually impossible to do global per-user limits
within anything approaching the current system structure, because you'd
have no way to know which tables of other databases belong to which
user. Per-tablespace quotas can at least be done by reference to just
the filesystem, without needing inaccessible catalogs of other
databases.

regards, tom lane

I'd like to avoid a GUC for "percent_full_warning" if we can. Can anyone
see a way around this? Should we just assume 90% full?

Well, it was only an idea of not leaving the admin out in the rain but
giving im a hint by time of what might happen if there was no action. I
have absolutely no idea if it is usefull of introducing a new GUC or
setting this value to a fixed size of 90 or whatever percent. Maybe 95
percent are enough, too?

Regards,
Yann

So, are we going to go with 90% or 95% as the assumed assumption for a
warning :)

Yann Michel wrote:

Guys,

I'd like to avoid a GUC for "percent_full_warning" if we can. Can
anyone see a way around this? Should we just assume 90% full?

On second thought, we need to have a GUC for this, whether I want it or not.
It needs to be optional to the log, yes? So it would be:
log_tablespace_full = %
with the default being "0" (don't log).

--
Josh Berkus
Aglio Database Solutions
San Francisco

People,

On second thought, we need to have a GUC for this, whether I want it or
not. It needs to be optional to the log, yes? So it would be:
log_tablespace_full = %
with the default being "0" (don't log).

On third thought, could we do this as part of the maximum size declaration?
Like:

ALTER TABLESPACE tbsp1 SET MAXSIZE 128M MAXWARN 80

That would be ideal, since the % you might want could vary per tablespace.
This would be emitted as a WARNING to the log every time you run a check
(e.g. after each commit).

--
Josh Berkus
Aglio Database Solutions
San Francisco

I prefer this option over a GUC.

Josh Berkus wrote:

Hi folks!

On Tue, Jun 14, 2005 at 11:39:06AM -0600, Jonah H. Harris wrote:

On second thought, we need to have a GUC for this, whether I want it or
not. It needs to be optional to the log, yes? So it would be:
log_tablespace_full = %
with the default being "0" (don't log).

On third thought, could we do this as part of the maximum size
declaration? Like:

ALTER TABLESPACE tbsp1 SET MAXSIZE 128M MAXWARN 80

That would be ideal, since the % you might want could vary per tablespace.
This would be emitted as a WARNING to the log every time you run a check
(e.g. after each commit).

Yes, that is the best idea, I think. What I don't like with the GUC
variable is, that it is _one_ warninglevel for all tablespaces
independent of their character. If I have two tables in different
tablespaces both with the global 90 percent threshold I may become
warned that tablespace A is 90 percent full, but if this tbsp. will only
grow one percent per day I still have 10 days left. Otherwise if I have
a tablespace B with 9 percent full but 5 percent growth per day that
will only be two days. So normaly id would have been fine to be warned 3
days ago for tablespace B but 5 "to early" for tablespace A.
Did you get the idea?

Regards,
Yann

Hi again,

On Mon, Jun 13, 2005 at 04:47:20PM -0600, Jonah H. Harris wrote:

Well... a maximum tablespace size would be much easier to implement and
would still accomplish this level of quota for larger organizations and
database systems.

I vote for implmenting the maximum tablespace size and revisiting actual
user/group quotas when the need arises.

Was someone going to implement this? If not, I can probably get it done
in a couple days.

are you still working on this or what has hapened to the idea of
MAXSIZE?

Regards,
Yann

If I recall correctly, I never got a response. I can still get it done
quickly and probably before the July 1st feature freeze (if that's still
the date). Tom, Bruce, Josh, et al what are your thoughts if I submit a
patch in the next few days? Is everyone already too busy reviewing the
current patches?

-Jonah

Yann Michel wrote:

"Jonah H. Harris" <jharris@tvi.edu> writes:

If I recall correctly, I never got a response. I can still get it done
quickly and probably before the July 1st feature freeze (if that's still
the date). Tom, Bruce, Josh, et al what are your thoughts if I submit a
patch in the next few days? Is everyone already too busy reviewing the
current patches?

I don't actually believe that this can be done at the drop of a hat ---
at least not in a way that will perform acceptably. I haven't seen a
design proposal that looks like it will work, anyway. What do you
intend to check exactly, where, and how often?

regards, tom lane

Tom,

You're right, this is going to take more work to make sure all is
perfect. Let me work up a formal definition and send it to the group.

Thanks for bringing me back to my senses.

-Jonah

Tom Lane wrote: