Re: [HACKERS] User Quota Implementation
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] User Quota Implementation
Jonah H. Harris [EMAIL PROTECTED] 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
Re: [HACKERS] User Quota Implementation
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Quota Implementation
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Quota Implementation
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] User Quota Implementation
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] User Quota Implementation
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Quota Implementation
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] User Quota Implementation
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). ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] User Quota Implementation
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Quota Implementation
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 | [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] User Quota Implementation
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] User Quota Implementation
* 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 signature.asc Description: Digital signature
Re: [HACKERS] User Quota Implementation
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] User Quota Implementation
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 | [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] User Quota Implementation
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] User Quota Implementation
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] User Quota Implementation
Hi Josh,
On Fri, Jun 10, 2005 at 02:25:11PM -0700, Josh Berkus wrote:
O.K. This makes sens to me. Otherwise I'd like to see quotas per
tablespace. As far as I got it, a tablespace may grow in size untile the
volume is full. Here a grace quota might be usefull as well. Let's say a
5% threshold like the ext filesystem as an default for generating a
warning to th elogs files letting the admin extend the volum(s) by time.
Hmmm ... Tablespace quotas would be *even more* useful than database
quotas. If it's just as easy for you?
Well, lets see...
What do we need:
- Extension of the CREATE TABLESPACE command:
CREATE TABLESPACE tablespacename
[ OWNER username ]
[ SIZE integerK | M | G | T ]
LOCATION 'directory'
- Extension of the ALTER TABLESPACE command:
ALTER TABLESPACE name
{RENAME TO newname |
SIZE integerK | 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
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] User Quota Implementation
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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 integerK | M | G | T ]
LOCATION 'directory'
- Extension of the ALTER TABLESPACE command:
ALTER TABLESPACE name
{RENAME TO newname |
SIZE integerK | 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.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] User Quota Implementation
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 integerK | M | G | T ]
LOCATION 'directory'
- Extension of the ALTER TABLESPACE command:
ALTER TABLESPACE name
{RENAME TO newname |
SIZE integerK | 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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
Hi, did anything happen to implementing quotas, yet? though I did not see anything on the TODO List I was wondering what is going on. Regards, Yann ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
Yann Michel wrote: Hi, did anything happen to implementing quotas, yet? though I did not see anything on the TODO List I was wondering what is going on. No work has been done on it, and I don't even see a TODO item for it. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] User Quota Implementation
Yann Michel wrote: Hi Bruce, On Fri, Jun 10, 2005 at 09:45:32AM -0400, Bruce Momjian wrote: did anything happen to implementing quotas, yet? though I did not see anything on the TODO List I was wondering what is going on. No work has been done on it, and I don't even see a TODO item for it. Do you think that it is possible that one can generate a TODO item out of the request or do you rather think different? Yes, sure. Ah, I found it. TODO has now: * Allow limits on per-db/user connections That is pretty vague, but it is all we have so far. In fact, that refers more to the number of connections rather than say disk space or CPU. The issue we have had with these issues in the past is that we aren't sure how such limits would be implemented or used. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
Hi Bruce, On Fri, Jun 10, 2005 at 09:45:32AM -0400, Bruce Momjian wrote: did anything happen to implementing quotas, yet? though I did not see anything on the TODO List I was wondering what is going on. No work has been done on it, and I don't even see a TODO item for it. Do you think that it is possible that one can generate a TODO item out of the request or do you rather think different? Regards, Yann ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] User Quota Implementation
On Fri, Jun 10, 2005 at 10:07:59AM -0400, Bruce Momjian wrote: Do you think that it is possible that one can generate a TODO item out of the request or do you rather think different? Yes, sure. Ah, I found it. TODO has now: * Allow limits on per-db/user connections Fine! That is pretty vague, but it is all we have so far. In fact, that refers more to the number of connections rather than say disk space or CPU. The issue we have had with these issues in the past is that we aren't sure how such limits would be implemented or used. Well, I have realy a lot of experiences with oracle usage and with its limitation capabilities. What I need the most is space-limitation per tablespace. Since 9i there is also a possibility to restrict cpu-usage for a certain consumer or group but in fact I din't need to to so since most of the apps have their own database. Maybe it could be useful to have these groups later on. As far as I understood the thread above (and any other mails) the space limitation would not only be nice for me. BTW: Is there any patch available, yet? This thread dealt with a patch but I didn't see any!? Regards, Yann ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] User Quota Implementation
Yann Michel wrote: On Fri, Jun 10, 2005 at 10:07:59AM -0400, Bruce Momjian wrote: Do you think that it is possible that one can generate a TODO item out of the request or do you rather think different? Yes, sure. Ah, I found it. TODO has now: * Allow limits on per-db/user connections Fine! That is pretty vague, but it is all we have so far. In fact, that refers more to the number of connections rather than say disk space or CPU. The issue we have had with these issues in the past is that we aren't sure how such limits would be implemented or used. Well, I have realy a lot of experiences with oracle usage and with its limitation capabilities. What I need the most is space-limitation per tablespace. Since 9i there is also a possibility to restrict cpu-usage for a certain consumer or group but in fact I din't need to to so since most of the apps have their own database. Maybe it could be useful to have these groups later on. As far as I understood the thread above (and any other mails) the space limitation would not only be nice for me. I assume you can't use file system quotas for the tablespace partitions? BTW: Is there any patch available, yet? This thread dealt with a patch but I didn't see any!? Oh, there is no patch, just a discussion. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] User Quota Implementation
Hi, On Fri, Jun 10, 2005 at 10:59:46AM -0400, Bruce Momjian wrote: Well, I have realy a lot of experiences with oracle usage and with its limitation capabilities. What I need the most is space-limitation per tablespace. Since 9i there is also a possibility to restrict cpu-usage for a certain consumer or group but in fact I din't need to to so since most of the apps have their own database. Maybe it could be useful to have these groups later on. As far as I understood the thread above (and any other mails) the space limitation would not only be nice for me. I assume you can't use file system quotas for the tablespace partitions? No, that's definetely no solution, due to I'm interested in a general solution which should be applicable for all platforms. BTW: Is there any patch available, yet? This thread dealt with a patch but I didn't see any!? Oh, there is no patch, just a discussion. O.K. so I was not wrong about that. Is it usefull to re-discuss some of the aspects to get a gist of what should probably be implemented/extended? Regards, Yann ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] User Quota Implementation
Yann Michel wrote: Hi, On Fri, Jun 10, 2005 at 10:59:46AM -0400, Bruce Momjian wrote: Well, I have realy a lot of experiences with oracle usage and with its limitation capabilities. What I need the most is space-limitation per tablespace. Since 9i there is also a possibility to restrict cpu-usage for a certain consumer or group but in fact I din't need to to so since most of the apps have their own database. Maybe it could be useful to have these groups later on. As far as I understood the thread above (and any other mails) the space limitation would not only be nice for me. I assume you can't use file system quotas for the tablespace partitions? No, that's definetely no solution, due to I'm interested in a general solution which should be applicable for all platforms. BTW: Is there any patch available, yet? This thread dealt with a patch but I didn't see any!? Oh, there is no patch, just a discussion. O.K. so I was not wrong about that. Is it usefull to re-discuss some of the aspects to get a gist of what should probably be implemented/extended? Sure. Basically there has not been a lot of interest in this, and we are not sure how to implement it without a huge amount of work. Considering the other things we are working on, it hasn't been a priority, and lots of folks don't like the Oracle approach either. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
Bruce, Yann, Sure. Basically there has not been a lot of interest in this, and we are not sure how to implement it without a huge amount of work. Considering the other things we are working on, it hasn't been a priority, and lots of folks don't like the Oracle approach either. Yeah. I'd prefer per-database quotas, rather than per-user quotas, which seem kind of useless. The hard part is making any transaction which would exceed the per-database quota roll back cleanly with a comprehensible error message rather than just having the database shut down. If we had per-database user quotas, and per-database users, it would pretty much wind up all of the issues which ISPs have with Postgres. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] User Quota Implementation
Josh Berkus josh@agliodbs.com writes: Yeah. I'd prefer per-database quotas, rather than per-user quotas, which seem kind of useless. The hard part is making any transaction which would exceed the per-database quota roll back cleanly with a comprehensible error message rather than just having the database shut down. That part doesn't seem hard to me: we already recover reasonably well from smgrextend failures. The real difficulty is in monitoring the total database size to know when it's time to complain. We don't currently make any effort at all to measure that, let alone keep track of it in real time. Given that there might be lots of processes concurrently adding pages in different places, I don't think you could hope for an exact stop-on-a-dime limit, but maybe if you're willing to accept some fuzz it is doable ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] User Quota Implementation
Hi Josh! On Fri, Jun 10, 2005 at 10:13:52AM -0700, Josh Berkus wrote: Yeah. I'd prefer per-database quotas, rather than per-user quotas, which seem kind of useless. The hard part is making any transaction which would exceed the per-database quota roll back cleanly with a comprehensible error message rather than just having the database shut down. If we had per-database user quotas, and per-database users, it would pretty much wind up all of the issues which ISPs have with Postgres. O.K. This makes sens to me. Otherwise I'd like to see quotas per tablespace. As far as I got it, a tablespace may grow in size untile the volume is full. Here a grace quota might be usefull as well. Let's say a 5% threshold like the ext filesystem as an default for generating a warning to th elogs files letting the admin extend the volum(s) by time. Regards, Yann ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] User Quota Implementation
Hi Tom, On Fri, Jun 10, 2005 at 01:37:54PM -0400, Tom Lane wrote: Josh Berkus josh@agliodbs.com writes: Yeah. I'd prefer per-database quotas, rather than per-user quotas, which seem kind of useless. The hard part is making any transaction which would exceed the per-database quota roll back cleanly with a comprehensible error message rather than just having the database shut down. That part doesn't seem hard to me: we already recover reasonably well from smgrextend failures. The real difficulty is in monitoring the total database size to know when it's time to complain. We don't currently make any effort at all to measure that, let alone keep track of it in real time. Given that there might be lots of processes concurrently adding pages in different places, I don't think you could hope for an exact stop-on-a-dime limit, but maybe if you're willing to accept some fuzz it is doable ... Well I think a fuzzy test is better than none. But I think one should be able to calculate how much later the quota is detected as exceeded than it is planed to be. Therefor a threshold is usefull as well (for alerting) Regards, Yann ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Quota Implementation
Yann, O.K. This makes sens to me. Otherwise I'd like to see quotas per tablespace. As far as I got it, a tablespace may grow in size untile the volume is full. Here a grace quota might be usefull as well. Let's say a 5% threshold like the ext filesystem as an default for generating a warning to th elogs files letting the admin extend the volum(s) by time. Hmmm ... Tablespace quotas would be *even more* useful than database quotas. If it's just as easy for you? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
I have a patch for user quotas in (I think) 7.4.2. I was going to update it for 8.x but have been too busy. The discussion (in the past) was related to whether quotas would be applied to users or groups and whether it would be on tablespaces (which I think it should). I can spend some time reviving the patch this weekend if it is of interest to you. Just let me know. -Jonah Josh Berkus wrote: Yann, O.K. This makes sens to me. Otherwise I'd like to see quotas per tablespace. As far as I got it, a tablespace may grow in size untile the volume is full. Here a grace quota might be usefull as well. Let's say a 5% threshold like the ext filesystem as an default for generating a warning to th elogs files letting the admin extend the volum(s) by time. Hmmm ... Tablespace quotas would be *even more* useful than database quotas. If it's just as easy for you? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] User Quota Implementation
this leads me to the first question I asked... do you want me to pull the latest cvs and patch it... or distribute my patch for 7.4.3? Tom Lane wrote: [ catching up on this discussion a bit late... ] Alvaro Herrera [EMAIL PROTECTED] 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 | [EMAIL PROTECTED] Albuquerque, New Mexico 87106| http://w3.tvi.edu/~jharris/ All great truths begin as blasphemies. -- George Bernard Shaw ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] User Quota Implementation
Jonah H. Harris [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
Jonah H. Harris [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] User Quota Implementation
* Jonah H. Harris ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] User Quota Implementation
Personally, I would love to see this in PostgreSQL. It'd be great if it could get into 7.5. An issue I see with that is that (similar to Oracle...) I think people would want to be able to specify per-tablespace quotas. Perhaps that wouldn't be too hard to add? 7.5 is already closed for new features I believe... Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] User Quota Implementation
* Christopher Kings-Lynne ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] User Quota Implementation
On Fri, 9 Jul 2004, Stephen Frost wrote: * Christopher Kings-Lynne ([EMAIL PROTECTED]) 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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] User Quota Implementation
On Fri, 9 Jul 2004, Christopher Kings-Lynne wrote: Personally, I would love to see this in PostgreSQL. It'd be great if it could get into 7.5. An issue I see with that is that (similar to Oracle...) I think people would want to be able to specify per-tablespace quotas. Perhaps that wouldn't be too hard to add? 7.5 is already closed for new features I believe... Very much so ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Quota Implementation
On Fri, 2004-07-09 at 09:29, Stephen Frost wrote: * Christopher Kings-Lynne ([EMAIL PROTECTED]) 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? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] User Quota Implementation
On Thu, 8 Jul 2004, Jonah H. Harris wrote: Hi, 3. The maximum quota size is (currently) the maximum of int4*1024 bytes. why is this? This is very limiting ... Using a 64bit value would be a lot more straight foreward. Greetings, Klaus -- Full Name : Klaus Naumann | (http://www.mgnet.de/) (Germany) Phone / FAX : ++49/177/7862964 | E-Mail: ([EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
On Fri, 2004-07-09 at 10:14, Rod Taylor wrote: On Fri, 2004-07-09 at 09:29, Stephen Frost wrote: * Christopher Kings-Lynne ([EMAIL PROTECTED]) 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Quota Implementation
* Rod Taylor ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] User Quota Implementation
Simply setup a tablespace for a given user with permissions to allow only that user to create new objects within it and make it the default location) -- tie their schema to their tablespace? -- then set a kernel level quota on their tablespace. Since the user accessing/writing to the tablespaces would be the postgres user I don't really think this 'solution' works in reality. I had assumed it would be a directory based quota rather than a user based one. Or do we expect a PostgreSQL implementation to do more than that, to only count active data by ignoring data pending a vacuum? Certainly, it should. Okay. But just so we all know that this means the user with a 5MB quota could still (potentially) fill 1TB of physical diskspace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
* Rod Taylor ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] User Quota Implementation
Since the user accessing/writing to the tablespaces would be the postgres user I don't really think this 'solution' works in reality. I had assumed it would be a directory based quota rather than a user based one. It's been a while since I played with quotas but I don't recall this option being available. Group quotas should be sufficient. Create directory readable/writable to only the pgsql user, but have the group ownership be representative of the user in question. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] User Quota Implementation
* Rod Taylor ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] User Quota Implementation
On Thu, Jul 08, 2004 at 03:27:34PM -0600, Jonah H. Harris wrote: Out of necessity, I've implemented user quotas in 7.4.3. What would the process be for having this reviewed and combined? I have a patch for 7.4.3 ready, but wanted to know if you suggest that I patch the latest cvs instead. Below if some information on the implementation. At exactly what time is the quota enforced? Does the enforcement somehow serialize that operation? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Thou shalt study thy libraries and strive not to reinvent them without cause, that thy code may be short and readable and thy days pleasant and productive. (7th Commandment for C Programmers) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] User Quota Implementation
* Klaus Naumann ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] User Quota Implementation
There are a couple of modifications that I'd still like to make to user quotas. Because 7.5 is locked, this may be a good time to discuss the implementation (possibly for 7.6?) I have seen some discussion about using OS-level quotas on a user or group level, however, like our Oracle system, not all database users have a system account. This is why I needed to implement user-specific quota functionality within the database itself. Also note, my quota implementation currently expects a well-vacuumed database. I always use pg_autovacuum but don't know about most other people. I'd like to make the following changes... Let me know your thoughts. - Change userquota from int4 to int8. - userquota is stored in units of kilobytes... is this adequate? Would anyone ever use a quota 1K other than for allowing a user no space? If the user has no space, why not just disable the account and/or make them read-only? - Would anyone want to use a group quota in PGSQL (rather than user-only)? - I assume that, based on discussion, not everyone sets up auto-vacuuming and therefore I would need to change the way I perform calculations. - Quota acts on any object owned by the user. Is this adequate for everyone? - Hard limits vs. soft limits... does anyone think it's a good idea to truncate someone's data? I personally don't think it's a good idea. - What do you think about userquota being the attribute in pg_shadow... would you rather see something else. Is there any additional functionality you would like to see in a quota implementation? Would you rather see ALTER USER SET for quota rather than ALTER USER? Likewise, I had originally used ALTER USER username QUOTA UNLIMITED (in Oracle style) but found that it didn't meet PostgreSQL's common syntax... which is why I changed it to ALTER USER username NOQUOTA... does everyone agree with NOQUOTA over QUOTA UNLIMITED? -Jonah Stephen Frost wrote: * Rod Taylor ([EMAIL PROTECTED]) 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 | [EMAIL PROTECTED] Albuquerque, New Mexico 87106| http://w3.tvi.edu/~jharris/ All great truths begin as blasphemies. -- George Bernard Shaw ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] User Quota Implementation
Group quotas should be sufficient. Create directory readable/writable to only the pgsql user, but have the group ownership be representative of the user in question. Rather ugly, and you'll run out of groups if you have alot of users (the postgres user can only be in so many groups). It's a cute idea but I really don't see it as being viable. The postgres user doesn't need to be in any of the groups, the group simply exists simply to give the directory representation for a quota. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] User Quota Implementation
Quota is currently enforced on commit. I've considered checking during insert/update/copy and throwing an abort but within a transaction they may be deleting data as well. However, even as a delete may take place before a massive insert/update, a vacuum cannot be run within a transaction block and therefore the commit would fail at the same point as an insert/update within the transaction itself. So, essentially, there is no difference at which point to check other than to reduce the calculation overhead by placing the check at the commit point. What's the consensus on when to check? Alvaro Herrera wrote: On Thu, Jul 08, 2004 at 03:27:34PM -0600, Jonah H. Harris wrote: Out of necessity, I've implemented user quotas in 7.4.3. What would the process be for having this reviewed and combined? I have a patch for 7.4.3 ready, but wanted to know if you suggest that I patch the latest cvs instead. Below if some information on the implementation. At exactly what time is the quota enforced? Does the enforcement somehow serialize that operation? -- Jonah H. Harris, UNIX Administrator | phone: 505.224.4814 Albuquerque TVI | fax: 505.224.3014 525 Buena Vista SE | [EMAIL PROTECTED] Albuquerque, New Mexico 87106| http://w3.tvi.edu/~jharris/ All great truths begin as blasphemies. -- George Bernard Shaw ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] User Quota Implementation
On Jul 9, 2004, at 12:04 PM, Jonah H. Harris wrote: - Quota acts on any object owned by the user. Is this adequate for everyone? Does changing owner also trigger new quota calculations on both the new and old owner? Is there any additional functionality you would like to see in a quota implementation? Quotas per user per tablespace, assuming 7.5 gets tablespaces. User quotas would make postgres on a shared university box much more pleasant. James Robinson Socialserve.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] User Quota Implementation
On Fri, 2004-07-09 at 11:47, Stephen Frost wrote: * Klaus Naumann ([EMAIL PROTECTED]) 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? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] User Quota Implementation
On Fri, 9 Jul 2004, Stephen Frost wrote: Hi, why is this? This is very limiting ... It's 2TB... Yeah, you're right. I didn't take into account, that you multiply it with 1kb - my fault. 2TB is enough - at the moment at least. But implementing it in 64 from now on could save a lot of work in the future ... It sounded to me like it might be a limitation forced by some other part of postgres, but I don't really know... Good question though. I'm not sure about it either - anyone? Greetings, Klaus -- Full Name : Klaus Naumann | (http://www.mgnet.de/) (Germany) Phone / FAX : ++49/177/7862964 | E-Mail: ([EMAIL PROTECTED]) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] User Quota Implementation
On Fri, Jul 09, 2004 at 10:04:01AM -0600, Jonah H. Harris wrote: I'd like to make the following changes... Let me know your thoughts. - userquota is stored in units of kilobytes... is this adequate? Would anyone ever use a quota 1K other than for allowing a user no space? If the user has no space, why not just disable the account and/or make them read-only? We don't have the functionality for read-only, unless you REVOKE all his privileges except select. Anyway I think that you can't really enforce with such granularity, so this is moot. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) La felicidad no es mañana. La felicidad es ahora ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] User Quota Implementation
* Rod Taylor ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] User Quota Implementation
James Robinson wrote: On Jul 9, 2004, at 12:04 PM, Jonah H. Harris wrote: - Quota acts on any object owned by the user. Is this adequate for everyone? Does changing owner also trigger new quota calculations on both the new and old owner? Quota calculations are performed per-owner at commit time. As only a superuser can perform an ALTER TABLE OWNER, my implementation allows quotas to be handled automatically at run-time for the current-owner. Therefore, there is no need to perform new calculations for each user. I'm still testing large objects. Is there any additional functionality you would like to see in a quota implementation? Quotas per user per tablespace, assuming 7.5 gets tablespaces. I agree. User quotas would make postgres on a shared university box much more pleasant. Oh yeah! Our college is using PostgreSQL for student user accounts, which is why I originally implemented this :). James Robinson Socialserve.com -- Jonah H. Harris, UNIX Administrator | phone: 505.224.4814 Albuquerque TVI | fax: 505.224.3014 525 Buena Vista SE | [EMAIL PROTECTED] Albuquerque, New Mexico 87106| http://w3.tvi.edu/~jharris/ All great truths begin as blasphemies. -- George Bernard Shaw ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] User Quota Implementation
* Rod Taylor ([EMAIL PROTECTED]) wrote: On Fri, 2004-07-09 at 11:47, Stephen Frost wrote: * Klaus Naumann ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] User Quota Implementation
Stephen Frost wrote: * Rod Taylor ([EMAIL PROTECTED]) wrote: On Fri, 2004-07-09 at 11:47, Stephen Frost wrote: * Klaus Naumann ([EMAIL PROTECTED]) 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 | [EMAIL PROTECTED] Albuquerque, New Mexico 87106| http://w3.tvi.edu/~jharris/ All great truths begin as blasphemies. -- George Bernard Shaw ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] User Quota Implementation
On Fri, 9 Jul 2004, Jonah H. Harris wrote: - Would anyone want to use a group quota in PGSQL (rather than user-only)? Yes ... I could see this as being more useful, not less ... where you have a dept working on a database, but individual logins for audit logging ... - I assume that, based on discussion, not everyone sets up auto-vacuuming and therefore I would need to change the way I perform calculations. With 7.5 and beyond, what is the chances that auto-vacuuming isn't used? It would definitely change the direction of questions from why is my database so slow? to why is it telling me I'm out of space when my hard drive is empty? ... the fix for the second would eliminate the first :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] User Quota Implementation
Stephen Frost wrote: * Rod Taylor ([EMAIL PROTECTED]) 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 | [EMAIL PROTECTED] Albuquerque, New Mexico 87106| http://w3.tvi.edu/~jharris/ All great truths begin as blasphemies. -- George Bernard Shaw ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] User Quota Implementation
[ catching up on this discussion a bit late... ] Alvaro Herrera [EMAIL PROTECTED] 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
[HACKERS] User Quota Implementation
All, Out of necessity, I've implemented user quotas in 7.4.3. What would the process be for having this reviewed and combined? I have a patch for 7.4.3 ready, but wanted to know if you suggest that I patch the latest cvs instead. Below if some information on the implementation. === -- FACTS -- === 1. Only a superuser can modify user quotas. 2. The minimum quota size is 1K. 3. The maximum quota size is (currently) the maximum of int4*1024 bytes. 4. Altering a user's quota does not make a change to their current data. 5. If you modify a user's quota to smaller than their current amount, they will be unable to add any more data... but their current schema is not truncated. === -- SQL CHANGES === COMMAND: ALTER USER *** NEW SYNTAX *** ALTER USER name [ [ WITH ] option [ ... ] ] where option can be: [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | VALID UNTIL 'abstime' | QUOTA file_size | NOQUOTA where file_size is: integer[K | M | G] K = Kilobytes M = Megabytes G = Gigabytes === -- USAGE EXAMPLES - === *** VARIATIONS OF QUOTA SIZES *** ALTER USER someuser QUOTA 50M; ALTER USER someuser QUOTA 1G; ALTER USER someuser QUOTA 500K; *** GIVE USER AN UNLIMITED QUOTA *** ALTER USER someuser NOQUOTA; === -- INTERNAL CHANGES --- === pg_shadow has added attribute userquota of type int4 pg_user view has added attribute (pg_shadow.userquota) to selection. ADDED KEYWORDS QUOTA NOQUOTA -- Jonah H. Harris, UNIX Administrator | phone: 505.224.4814 Albuquerque TVI | fax: 505.224.3014 525 Buena Vista SE | [EMAIL PROTECTED] Albuquerque, New Mexico 87106| http://w3.tvi.edu/~jharris/ All great truths begin as blasphemies. -- George Bernard Shaw ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
