Обсуждение: 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
someuserQUOTA 500K;
*** GIVE USER AN UNLIMITED QUOTA *** ALTER USER someuser NOQUOTA;
=======================================================================
-- INTERNAL CHANGES ---------------------------------------------------
=======================================================================
pg_shadow has added attribute userquota of type int4
pg_user view has added attribute (pg_shadow.userquota) to selection.
ADDED KEYWORDS
QUOTA NOQUOTA
--
Jonah H. Harris, UNIX Administrator | phone: 505.224.4814
Albuquerque TVI | fax: 505.224.3014
525 Buena Vista SE | jharris@tvi.edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/
"All great truths begin as blasphemies." -- George Bernard Shaw
* 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 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 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, 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
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)
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 BerkusAglio Database SolutionsSan 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, Pennsylvania19073
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, Pennsylvania19073
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, Pennsylvania19073
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, Pennsylvania19073
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: >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
thata 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
percentis 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: >>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 > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
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: >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). > > >
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: >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 > >
"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: >"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 > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > >