Re: [HACKERS] SOC user quotas

2007-03-02 Thread Robert Treat
On Thursday 01 March 2007 17:35, Andrew Dunstan wrote:
 Tom Lane wrote:
  Joshua D. Drake [EMAIL PROTECTED] writes:
  Andrew Dunstan wrote:
  Hitting a file system quota limit is likely to bring you down pretty
  hard, isn't it?
 
  Yes, and likely corrupt the database.
 
  As long as you don't keep WAL on the restricted filesystem, it won't
  stop or corrupt your database.  Whether you can get anything much done
  is another story :-(

 Yeah. Including recovery. Maybe we could do something that would work in
 cooperation with FS quotas - I have no idea what though.


I've actually run postgresql systems out of disk space both on data partitions 
and wal partitions and never suffered corruption.  Certainly I don't 
recommend the practice, but pg can be amazingly resilient at times. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Zeugswetter Andreas ADI SD

  Then you cannot run any queries that extend the size of 
 your relations 
  (for example INSERT, UPDATE etc.). Unless you drop your tables or 
  DELETE something
 
 Interesting. Well my two cents is don't go any deeper than database.
 I.e; don't try and track to the individual relation.

I thought the intuitive level for such a feature would be tablespace.
If you need per user each user would get it's own tablespace, if you
need database each database ...

Andreas

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Jim C. Nasby
On Wed, Feb 28, 2007 at 02:29:52PM -0800, Joshua D. Drake wrote:
 
  I don't know, but in my opinion, I don't see anything bad in requiring
  dropping the data if the quota is full. That's what usually occurs in
  the case of normal filesystem quota... If you don't have a space there,
  you cannot edit files, copy them etc...
  And that solution should be definitely better than the filesystem quota
  for the PostgreSQL user for example.
 
 The bad point is not that we would rollback the transaction. The bad
 point is what happens when you need to rollback a transaction and in
 your scenario it is quite plausible that a large rollback could occur,
 more than once, causing the requirement of something like a vacuum full
 to clean things up.

ISTM that if the transaction is that big it's likely going to be
extending the heap, which means you'd get space back on a plain vacuum.

As for things like CLUSTER, and REINDEX it would probably be useful to
make an exception, since we know that those operations are intended to
shrink the size of a relation.

I also think there's a lot to be said for a soft limit.
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Jeff Davis
On Wed, 2007-02-28 at 12:56 -0500, Tom Lane wrote:
 Sergey E. Koposov [EMAIL PROTECTED] writes:
  Per user AND per database (as Tom noted).  But I dont see what's odd in 
  it... It exists in Oracle, and I need quotas in the project on which I'm 
  working. And I remember user requests for quotas in the mailing lists ...
 
 It hasn't ever made it onto the TODO list, which means there's not a
 consensus that we need it.  If it were a simple, small, low-impact patch
 then you probably wouldn't need to do much convincing that it's an
 important feature to have, but I'm afraid the patch will be none of
 those things.
 

If we allowed an admin to revoke CREATE from the pg_default tablespace,
then anyone who needed that feature could limit people to their own
tablespace (at which point it's the filesystem's job to limit their
usage). Is there some reason that we can't do this now?

Regards,
Jeff Davis


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Tom Lane
Jeff Davis [EMAIL PROTECTED] writes:
 If we allowed an admin to revoke CREATE from the pg_default tablespace,
 then anyone who needed that feature could limit people to their own
 tablespace (at which point it's the filesystem's job to limit their
 usage). Is there some reason that we can't do this now?

The reason it appears not to work now is that the code is deliberately
set up not to check permissions on the database's default tablespace, on
the theory that if you have regular CREATE permissions in the database
then you probably are supposed to be able to create things.  However,
if you want to restrict someone to a filesystem-limited tablespace, you
can surely make that tablespace be the default for their database.
This would make the quota include system catalogs as well as user
tables, which seems to me a good idea anyway because it's not hard for a
user to bloat the system catalogs.  (The fact that pg_largeobject is a
communal table makes per-user quotas useless for BLOB users already.)

Now if you want quota-limited users to share a database with
not-quota-limited users, this doesn't work too well.  The non-limited
users can surely be set up with access to a non-default tablespace,
but if the system catalogs hit the quota limit then everyone's gonna
be unhappy.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Andrew Dunstan

Jeff Davis wrote:


If we allowed an admin to revoke CREATE from the pg_default tablespace,
then anyone who needed that feature could limit people to their own
tablespace (at which point it's the filesystem's job to limit their
usage). Is there some reason that we can't do this now?

  



Hitting a file system quota limit is likely to bring you down pretty 
hard, isn't it?


cheers

andrew.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Joshua D. Drake
Andrew Dunstan wrote:
 Jeff Davis wrote:

 If we allowed an admin to revoke CREATE from the pg_default tablespace,
 then anyone who needed that feature could limit people to their own
 tablespace (at which point it's the filesystem's job to limit their
 usage). Is there some reason that we can't do this now?

   
 
 
 Hitting a file system quota limit is likely to bring you down pretty
 hard, isn't it?

Yes, and likely corrupt the database.

Joshua D. Drake

 
 cheers
 
 andrew.
 
 ---(end of broadcast)---
 TIP 3: Have you checked our extensive FAQ?
 
   http://www.postgresql.org/docs/faq
 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Tom Lane
Joshua D. Drake [EMAIL PROTECTED] writes:
 Andrew Dunstan wrote:
 Hitting a file system quota limit is likely to bring you down pretty
 hard, isn't it?

 Yes, and likely corrupt the database.

As long as you don't keep WAL on the restricted filesystem, it won't
stop or corrupt your database.  Whether you can get anything much done
is another story :-(

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Andrew Dunstan

Tom Lane wrote:

Joshua D. Drake [EMAIL PROTECTED] writes:
  

Andrew Dunstan wrote:


Hitting a file system quota limit is likely to bring you down pretty
hard, isn't it?
  


  

Yes, and likely corrupt the database.



As long as you don't keep WAL on the restricted filesystem, it won't
stop or corrupt your database.  Whether you can get anything much done
is another story :-(


  


Yeah. Including recovery. Maybe we could do something that would work in 
cooperation with FS quotas - I have no idea what though.


cheers

andrew


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Jeff Davis
On Thu, 2007-03-01 at 14:13 -0800, Joshua D. Drake wrote:
 Andrew Dunstan wrote:
  Jeff Davis wrote:
 
  If we allowed an admin to revoke CREATE from the pg_default tablespace,
  then anyone who needed that feature could limit people to their own
  tablespace (at which point it's the filesystem's job to limit their
  usage). Is there some reason that we can't do this now?
 

  
  
  Hitting a file system quota limit is likely to bring you down pretty
  hard, isn't it?
 
 Yes, and likely corrupt the database.
 

Is it possible to make the database safe in this case? I can see why it
would be difficult to handle pg_xlog filling up (or for that matter
pg_log), but couldn't any other area be turned into just a simple ERROR?

pg_xlog is cluster-wide, so even if you had the databases in separate
quota-limited areas, that wouldn't mean pg_xlog would fill up.

If it's possible to allow disk-is-full to become a simple error, that
might satisfy this request without much effort. If it is a lot of
effort, then I retract my suggestion.

By the way, is there some good documentation about what actually happens
in different cases when the disk is full? I treat it as never let this
happen and I don't really know what to expect if it did happen.

Regards,
Jeff Davis


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Tom Lane
Jeff Davis [EMAIL PROTECTED] writes:
 Is it possible to make the database safe in this case? I can see why it
 would be difficult to handle pg_xlog filling up (or for that matter
 pg_log), but couldn't any other area be turned into just a simple ERROR?

It is --- Josh is mistaken about the severity of the code's response to
out-of-space.  We have to panic if out of WAL or CLOG space because we
can't advance the transaction counter anymore in such cases, but
otherwise it's just ERROR.  See the archives for some recorded cases of
PG's response to out-of-space situations.

The real problem though is whether you can get anything much done if up
against a hard limit; especially if that limit also affects the system
catalogs.  Remember that UPDATE requires the ability to insert new tuple
versions, so there are a whole lot of things that will draw that ERROR.

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Jeff Davis
On Thu, 2007-03-01 at 17:53 -0500, Tom Lane wrote:
 The real problem though is whether you can get anything much done if up
 against a hard limit; especially if that limit also affects the system
 catalogs.  Remember that UPDATE requires the ability to insert new tuple
 versions, so there are a whole lot of things that will draw that ERROR.
 

You can pg_dump, drop the database, and make it again (maybe after
deleting a few lines).

I know it's not pretty, but the request is mostly centered around
virtual hosting. The admin can easily generate emails when space is low,
and when someone actually hits the quota the admin does have a path to
get them out of the mess without disturbing other customers.

It's not fool-proof. Someone can generate huge amounts of WAL traffic,
hog the CPU, use the RAM, all kinds of things. But virtual hosting can't
easily prevent that kind of thing anyway.

So it seems like we already have a solution to quotas at the database
level.

Another point is that shared virtual hosting is becoming less important
as OS virtualization (like Xen) becomes more prevalent.

Regards,
Jeff Davis



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Sergey E. Koposov

So it seems like we already have a solution to quotas at the database
level.


I still  don't understand how FS quotas can help with DB quotas. ..
All the FS quotas are setup for limiting  the space for specific user and 
specific mount point. AFAIK They do not allow to limit the space in one 
directory. And since all the PG tablespaces will be owned by postgres 
user, I dont think the FS quotas can do the job.


regards,
Sergey

***
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg 
Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Tom Lane
Sergey E. Koposov [EMAIL PROTECTED] writes:
 I still  don't understand how FS quotas can help with DB quotas. ..
 All the FS quotas are setup for limiting  the space for specific user and 
 specific mount point. AFAIK They do not allow to limit the space in one 
 directory. And since all the PG tablespaces will be owned by postgres 
 user, I dont think the FS quotas can do the job.

Well, you have to put the tablespace on a mount point that's not
otherwise used by Postgres, but beyond that I don't see the problem.

The point here is not that it's amazingly simple or convenient to use;
obviously it isn't.  The point is that it's *possible* to get this sort
of effect today, and so we have to ask whether it's really worth a good
deal of development effort and code complexity to provide somewhat
more convenient, but basically duplicate, functionality within Postgres.

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Sergey E. Koposov

On Thu, 1 Mar 2007, Tom Lane wrote:


Sergey E. Koposov [EMAIL PROTECTED] writes:

I still  don't understand how FS quotas can help with DB quotas. ..
All the FS quotas are setup for limiting  the space for specific user and
specific mount point. AFAIK They do not allow to limit the space in one
directory. And since all the PG tablespaces will be owned by postgres
user, I dont think the FS quotas can do the job.


Well, you have to put the tablespace on a mount point that's not
otherwise used by Postgres, but beyond that I don't see the problem.



And if you have 5-10 users which should be under quota , you need 5-10
different mount points. From my humble opinion that's an absolutely crazy 
solution and nobody will ever do that.



The point here is not that it's amazingly simple or convenient to use;
obviously it isn't.  The point is that it's *possible* to get this sort
of effect today, and so we have to ask whether it's really worth a good
deal of development effort and code complexity to provide somewhat
more convenient, but basically duplicate, functionality within Postgres.



Yes, I completely agree that it's up to the comunity to decide whether 
the feature is worth the complexity of its development, but I don't agree 
that's a duplication of existing functionality. IMHO.


regards,
Sergey

***
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg 
Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [HACKERS] SOC user quotas

2007-03-01 Thread Jeff Davis
On Fri, 2007-03-02 at 03:31 +0300, Sergey E. Koposov wrote:
  So it seems like we already have a solution to quotas at the database
  level.
 
 I still  don't understand how FS quotas can help with DB quotas. ..
 All the FS quotas are setup for limiting  the space for specific user and 
 specific mount point. AFAIK They do not allow to limit the space in one 
 directory. And since all the PG tablespaces will be owned by postgres 
 user, I dont think the FS quotas can do the job.
 

Some filesystems help you more than others. In ZFS you can create a
filesystem that uses a pre-existing storage pool and set a quota on that
filesystem, and then mount it for the user that you want. In effect,
it's basically a directory quota (no space reservation necessary, but a
space limit).

For the project you're working on, can you just set the default
tablespaces to be restricted-size areas for the scientists? If you trust
the scientists setting their default tablespace should be enough, and
they don't need to be in a separate database.

Or if you don't trust the scientists, could you use a separate database
and the scientists could query with something like dblink?

Regards,
Jeff Davis


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Tom Lane
Sergey E. Koposov [EMAIL PROTECTED] writes:
 1) The main idea is to implement the per-user quota (not per tablespace 
 for example). So, during the creation of the new user some quota can be 
 specified, and after that the size of all the relations *owned* by that 
 user should be limited by that number.

This seems impractical as stated; there is no way to determine what a
user owns in some other database.  Possibly you could do it if the quota
were both per-user and per-database.

 2) I looked into the code, and from my understanding, the main part of the 
 code which should be affected by the quotas is storage/smgr/md.c.

md.c is too low level to do catalog accesses and thus too low level to
know who owns what.

 3) How the quota should be controlled: I think, that generally, for all 
 the users which have quotas, the shared memory should contain the number 
 of blocks left from the quota. And each backend extending or truncating 
 the relations owned by the user should appropriately change that number of 
 blocks left in the shared memory.

What will you do with ALTER TABLE OWNER?  What if such a command is
rolled back?  (Likewise for some other commands such as TRUNCATE, or
even just DROP TABLE.)  What if there are too many users to fit in your
(necessarily fixed size) shared memory area?  What sort of contention
will there be for access to this area?

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Joshua D. Drake
Sergey E. Koposov wrote:
 Hello hackers,
 
 I was starting to think about next SOC and the project for it. And for a
 long time I wanted to implement the user quotas in PG.
 So, I'll try to explain my understanding of the implementation, and I'll
 be happy to hear any comments, objections, or pointings to my
 misunderstanding. This is very first very rough idea, but I still would
 like to hear whether it contains some obvious flaws...
 
 1) The main idea is to implement the per-user quota (not per tablespace
 for example). So, during the creation of the new user some quota can be
 specified, and after that the size of all the relations *owned* by that
 user should be limited by that number.

I could see this being useful per database, maybe. It seems like kind of
an odd feature.

Sincerely,

Joshua D. Drake


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Sergey E. Koposov

On Wed, 28 Feb 2007, Tom Lane wrote:


Sergey E. Koposov [EMAIL PROTECTED] writes:

1) The main idea is to implement the per-user quota (not per tablespace
for example). So, during the creation of the new user some quota can be
specified, and after that the size of all the relations *owned* by that
user should be limited by that number.


This seems impractical as stated; there is no way to determine what a
user owns in some other database.  Possibly you could do it if the quota
were both per-user and per-database.


yes, agreed. I didn't think of that.


3) How the quota should be controlled: I think, that generally, for all
the users which have quotas, the shared memory should contain the number
of blocks left from the quota. And each backend extending or truncating
the relations owned by the user should appropriately change that number of
blocks left in the shared memory.


What will you do with ALTER TABLE OWNER?  What if such a command is
rolled back?


I don't know, but I guess the ALTER OWNER  should be considered 
differently. It probably should proceed only if it sees that there are 
enough place to perform the whole operation. If there are, then it should 
block any writing to the tables of the user, perform the alter owner and 
unblock everything again.



(Likewise for some other commands such as TRUNCATE, or
even just DROP TABLE.)


I didn't think of yet, but I will.


What if there are too many users to fit in your
(necessarily fixed size) shared memory area?


We really don't need to create the array for all users. We only need to 
create that array for users 1) having quotas 2) the users,

whose tables are accessed at the moment
So I don't think that in that case the amount of required space is a 
problem here.



What sort of contention
will there be for access to this area?


I think, that the only requirement is that the incrementation or
decrementation of number of blocks left for each user 
should be atomic operation.


regards,
Sergey

***
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg 
Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Sergey E. Koposov

On Wed, 28 Feb 2007, Joshua D. Drake wrote:


I could see this being useful per database, maybe. It seems like kind of
an odd feature.


Per user AND per database (as Tom noted).  But I dont see what's odd in 
it... It exists in Oracle, and I need quotas in the project on which I'm 
working. And I remember user requests for quotas in the mailing lists ...


regards,
Sergey

***
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg 
Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 1: 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] SOC user quotas

2007-02-28 Thread Joshua D. Drake
Sergey E. Koposov wrote:
 On Wed, 28 Feb 2007, Joshua D. Drake wrote:
 
 I could see this being useful per database, maybe. It seems like kind of
 an odd feature.
 
 Per user AND per database (as Tom noted).  But I dont see what's odd in
 it... It exists in Oracle, and I need quotas in the project on which I'm
 working. And I remember user requests for quotas in the mailing lists ...

Well Oracle isn't really our goal is it? I am not questioning that you
are well intended but I just don't see a use case.

For example, what happens if I hit my quota?

Joshua D. Drake

 
 regards,
 Sergey
 
 ***
 Sergey E. Koposov
 Max Planck Institute for Astronomy/Cambridge Institute for
 Astronomy/Sternberg Astronomical Institute
 Tel: +49-6221-528-349
 Web: http://lnfm1.sai.msu.ru/~math
 E-mail: [EMAIL PROTECTED]
 
 ---(end of broadcast)---
 TIP 1: 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
 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 1: 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] SOC user quotas

2007-02-28 Thread Sergey E. Koposov

On Wed, 28 Feb 2007, Joshua D. Drake wrote:


Sergey E. Koposov wrote:

On Wed, 28 Feb 2007, Joshua D. Drake wrote:
Per user AND per database (as Tom noted).  But I dont see what's odd in
it... It exists in Oracle, and I need quotas in the project on which I'm
working. And I remember user requests for quotas in the mailing lists ...


Well Oracle isn't really our goal is it? I am not questioning that you
are well intended but I just don't see a use case.

For example, what happens if I hit my quota?


Then you cannot run any queries that extend the size of your relations 
(for example INSERT, UPDATE etc.). Unless you drop your tables or DELETE 
something


The use case for that is the situation when you provide the access to 
different people to do something on the DB. The real world example (in 
which I'm interested) is when the large science project produce a huge 
amount of data, store it in large database, and let different scientists 
work on that data, having their little accounts there. (example 
http://casjobs.sdss.org/CasJobs/Guide.aspx ). That's the way how most of 
large astronomical projects start to work now.


Regards,
Sergey

***
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg 
Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Joshua D. Drake

 Then you cannot run any queries that extend the size of your relations
 (for example INSERT, UPDATE etc.). Unless you drop your tables or DELETE
 something

Interesting. Well my two cents is don't go any deeper than database.
I.e; don't try and track to the individual relation.

Joshua D. Drake


 
 The use case for that is the situation when you provide the access to
 different people to do something on the DB. The real world example (in
 which I'm interested) is when the large science project produce a huge
 amount of data, store it in large database, and let different scientists
 work on that data, having their little accounts there. (example
 http://casjobs.sdss.org/CasJobs/Guide.aspx ). That's the way how most of
 large astronomical projects start to work now.
 
 Regards,
 Sergey
 
 ***
 Sergey E. Koposov
 Max Planck Institute for Astronomy/Cambridge Institute for
 Astronomy/Sternberg Astronomical Institute
 Tel: +49-6221-528-349
 Web: http://lnfm1.sai.msu.ru/~math
 E-mail: [EMAIL PROTECTED]
 
 ---(end of broadcast)---
 TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match
 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Tom Lane
Sergey E. Koposov [EMAIL PROTECTED] writes:
 Per user AND per database (as Tom noted).  But I dont see what's odd in 
 it... It exists in Oracle, and I need quotas in the project on which I'm 
 working. And I remember user requests for quotas in the mailing lists ...

It hasn't ever made it onto the TODO list, which means there's not a
consensus that we need it.  If it were a simple, small, low-impact patch
then you probably wouldn't need to do much convincing that it's an
important feature to have, but I'm afraid the patch will be none of
those things.

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Joshua D. Drake
Tom Lane wrote:
 Sergey E. Koposov [EMAIL PROTECTED] writes:
 Per user AND per database (as Tom noted).  But I dont see what's odd in 
 it... It exists in Oracle, and I need quotas in the project on which I'm 
 working. And I remember user requests for quotas in the mailing lists ...
 
 It hasn't ever made it onto the TODO list, which means there's not a
 consensus that we need it.  If it were a simple, small, low-impact patch
 then you probably wouldn't need to do much convincing that it's an
 important feature to have, but I'm afraid the patch will be none of
 those things.

Tom what about at just the DB level?

E.g; if user foo then pg_database_size may not be  than X?

I guess the big question would be when do we check though? At each
transaction seems like it would add significant overhead, especially if
we had to rollback the transaction because it was going to go over their
quota.

Egad.

Joshua D. Drake



 
   regards, tom lane
 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Sergey E. Koposov

On Wed, 28 Feb 2007, Tom Lane wrote:

2) I looked into the code, and from my understanding, the main part of the
code which should be affected by the quotas is storage/smgr/md.c.


md.c is too low level to do catalog accesses and thus too low level to
know who owns what.


That's probably a dumb question(I dont know the PG infrastructrure that 
well), but Is it possible to put the information about the owner into 
SMgrRelation/Relation structures? As I see the smgrextend() in smgr.c get 
the SMgrRelation agrument...


regards,
Sergey

***
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg 
Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Martijn van Oosterhout
On Wed, Feb 28, 2007 at 09:58:52AM -0800, Joshua D. Drake wrote:
 E.g; if user foo then pg_database_size may not be  than X?
 
 I guess the big question would be when do we check though? At each
 transaction seems like it would add significant overhead, especially if
 we had to rollback the transaction because it was going to go over their
 quota.

Generally, rolling back a transaction doesn't reduce the amount of disk
used. Only VACUUM FULL actually shrinks relations.

Seem to me if the RelationOpen stores a pointer to a counter that gets
incremented on mdextend, it should work reasonably well. Extending
doesn't happen that often relative to other database activity.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Joshua D. Drake
Martijn van Oosterhout wrote:
 On Wed, Feb 28, 2007 at 09:58:52AM -0800, Joshua D. Drake wrote:
 E.g; if user foo then pg_database_size may not be  than X?

 I guess the big question would be when do we check though? At each
 transaction seems like it would add significant overhead, especially if
 we had to rollback the transaction because it was going to go over their
 quota.
 
 Generally, rolling back a transaction doesn't reduce the amount of disk
 used. Only VACUUM FULL actually shrinks relations.

Right, but what I mean was -- if we rollback because we hit quota we
could potentially cause even more maintenance to have to happen (vacuum).

J


 
 Seem to me if the RelationOpen stores a pointer to a counter that gets
 incremented on mdextend, it should work reasonably well. Extending
 doesn't happen that often relative to other database activity.
 
 Have a nice day,


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Oleg Bartunov

On Wed, 28 Feb 2007, Joshua D. Drake wrote:


Tom Lane wrote:

Sergey E. Koposov [EMAIL PROTECTED] writes:

Per user AND per database (as Tom noted).  But I dont see what's odd in
it... It exists in Oracle, and I need quotas in the project on which I'm
working. And I remember user requests for quotas in the mailing lists ...


It hasn't ever made it onto the TODO list, which means there's not a
consensus that we need it.  If it were a simple, small, low-impact patch
then you probably wouldn't need to do much convincing that it's an
important feature to have, but I'm afraid the patch will be none of
those things.


We need this kind of feature in our scientific project I and Sergey are
working on. We provide access to big pool of astronomical catalogs and
ability to match users data with these huge catalogs and we want to
be able to provide sort of QoS.

Pg became very popular in Russia, especially after the biggest
accounting and enteprize management software developer 1C 
(about 800,000 installations) has been supporting Pg and I expect

a large interest to Pg this year, especially from the application providers,
shared environment.

btw, this  should be announced in -advocacy, I and Teodor 
worked on Pg port, some patches we have submitted was grown from that work.



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Tom Lane
Joshua D. Drake [EMAIL PROTECTED] writes:
 Martijn van Oosterhout wrote:
 Generally, rolling back a transaction doesn't reduce the amount of disk
 used. Only VACUUM FULL actually shrinks relations.

 Right, but what I mean was -- if we rollback because we hit quota we
 could potentially cause even more maintenance to have to happen (vacuum).

It's worse than that, because VACUUM FULL will actually bloat the
indexes on the way to being able to reduce the table size (since it has
to make new index entries for rows it moves).  If the limit is strictly
enforced then a user who has reached his quota is really totally
screwed: the only easy way to get back under quota will be to completely
drop tables, ie, discard data.  VACUUM probably won't reduce the
physical table size much, and VACUUM FULL will fail, and other
approaches such as CLUSTER won't work either.

[ thinks for a bit... ]  Possibly you could drop all your indexes,
VACUUM FULL, reconstruct indexes.  But it would be painful and would
certainly prevent you from working normally until you finish that
maintenance.  If the quota limit includes temp files you might find that
rebuilding the indexes fails, too, because of the transient space needed
to rebuild.

Plus, all that forced maintenance activity will be degrading response
for other users while it happens.

On the whole I'm not convinced that a quota is a good idea.

regards, tom lane

---(end of broadcast)---
TIP 1: 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] SOC user quotas

2007-02-28 Thread Oleg Bartunov

On Wed, 28 Feb 2007, Tom Lane wrote:


Joshua D. Drake [EMAIL PROTECTED] writes:

Martijn van Oosterhout wrote:

Generally, rolling back a transaction doesn't reduce the amount of disk
used. Only VACUUM FULL actually shrinks relations.



Right, but what I mean was -- if we rollback because we hit quota we
could potentially cause even more maintenance to have to happen (vacuum).


It's worse than that, because VACUUM FULL will actually bloat the
indexes on the way to being able to reduce the table size (since it has
to make new index entries for rows it moves).  If the limit is strictly
enforced then a user who has reached his quota is really totally
screwed: the only easy way to get back under quota will be to completely
drop tables, ie, discard data.  VACUUM probably won't reduce the
physical table size much, and VACUUM FULL will fail, and other
approaches such as CLUSTER won't work either.

[ thinks for a bit... ]  Possibly you could drop all your indexes,
VACUUM FULL, reconstruct indexes.  But it would be painful and would
certainly prevent you from working normally until you finish that
maintenance.  If the quota limit includes temp files you might find that
rebuilding the indexes fails, too, because of the transient space needed
to rebuild.

Plus, all that forced maintenance activity will be degrading response
for other users while it happens.

On the whole I'm not convinced that a quota is a good idea.


On database level it's possible to have soft user quote, just measure
disk usage and warn user if database size is over. This could be realized
using external tools. But Sergey wanted finer granulation. As a workaround,
we could have function which return size of db objects owned by user and
let administrator run cron job to realize soft quota. This will not provide
foundation for enterprize level of QoS, but we certainly don't want to
introduce too much overhead. It's interesting and challenging task though.

Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 1: 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] SOC user quotas

2007-02-28 Thread Sergey E. Koposov

On Wed, 28 Feb 2007, Tom Lane wrote:


Joshua D. Drake [EMAIL PROTECTED] writes:

Martijn van Oosterhout wrote:

Generally, rolling back a transaction doesn't reduce the amount of disk
used. Only VACUUM FULL actually shrinks relations.



Right, but what I mean was -- if we rollback because we hit quota we
could potentially cause even more maintenance to have to happen (vacuum).


It's worse than that, because VACUUM FULL will actually bloat the
indexes on the way to being able to reduce the table size (since it has
to make new index entries for rows it moves).  If the limit is strictly
enforced then a user who has reached his quota is really totally
screwed: the only easy way to get back under quota will be to completely
drop tables, ie, discard data.  VACUUM probably won't reduce the
physical table size much, and VACUUM FULL will fail, and other
approaches such as CLUSTER won't work either.


I don't know, but in my opinion, I don't see anything bad in requiring 
dropping the data if the quota is full. That's what usually occurs in the 
case of normal filesystem quota... If you don't have a space there, you 
cannot edit files, copy them etc...
And that solution should be definitely better than the filesystem quota 
for the PostgreSQL user for example.


regards,
Sergey
***
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg 
Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Martijn van Oosterhout
On Wed, Feb 28, 2007 at 03:57:56PM -0500, Tom Lane wrote:
 It's worse than that, because VACUUM FULL will actually bloat the
 indexes on the way to being able to reduce the table size (since it has
 to make new index entries for rows it moves).  If the limit is strictly

I was thinking that indexes and temp tables wouldn't be counted. I
thought it was more of a stop people using up lots of disk space
rather than specifically stopping at a hard limit.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Joshua D. Drake

 I don't know, but in my opinion, I don't see anything bad in requiring
 dropping the data if the quota is full. That's what usually occurs in
 the case of normal filesystem quota... If you don't have a space there,
 you cannot edit files, copy them etc...
 And that solution should be definitely better than the filesystem quota
 for the PostgreSQL user for example.

The bad point is not that we would rollback the transaction. The bad
point is what happens when you need to rollback a transaction and in
your scenario it is quite plausible that a large rollback could occur,
more than once, causing the requirement of something like a vacuum full
to clean things up.

Sincerely,

Joshua D. Drake



 
 regards,
 Sergey
 ***
 Sergey E. Koposov
 Max Planck Institute for Astronomy/Cambridge Institute for
 Astronomy/Sternberg Astronomical Institute
 Tel: +49-6221-528-349
 Web: http://lnfm1.sai.msu.ru/~math
 E-mail: [EMAIL PROTECTED]
 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Joachim Wieland
On Wed, Feb 28, 2007 at 12:56:13PM -0500, Tom Lane wrote:
 It hasn't ever made it onto the TODO list, which means there's not a
 consensus that we need it.

Such a patch could improve the acceptance of PostgreSQL in shared hosting
environments. Note that a database without quotas can be filled up easily
and the database will stop serving requests to other users' databases.

There is a quota implementation already in the archives but I don't know
more about it than that it exists:

http://archives.postgresql.org/pgsql-hackers/2004-07/msg00392.php


Joachim



---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Sergey E. Koposov

On Wed, 28 Feb 2007, Joshua D. Drake wrote:


The bad point is not that we would rollback the transaction. The bad
point is what happens when you need to rollback a transaction and in
your scenario it is quite plausible that a large rollback could occur,
more than once, causing the requirement of something like a vacuum full
to clean things up.


Yes, I understand, but I think, the scenario of working and always 
hitting a quota is not normal. I think you shouldn't expect perfect, 
stable performance etc. in that case. It's similar to if you'll try to 
work with PG with very little memory and/or shared memory. You can work, 
but you can easily hit these limits, and than something will not work...


regards,
Sergey

***
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg 
Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Sergey E. Koposov

On Wed, 28 Feb 2007, Joachim Wieland wrote:


On Wed, Feb 28, 2007 at 12:56:13PM -0500, Tom Lane wrote:

It hasn't ever made it onto the TODO list, which means there's not a
consensus that we need it.


Such a patch could improve the acceptance of PostgreSQL in shared hosting
environments. Note that a database without quotas can be filled up easily
and the database will stop serving requests to other users' databases.


Yes, I agree.


There is a quota implementation already in the archives but I don't know
more about it than that it exists:

http://archives.postgresql.org/pgsql-hackers/2004-07/msg00392.php


I remember that thread, but I think there was no patch at all, at least I 
didn't see it.


Sergey

***
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg 
Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Robert Treat
On Wednesday 28 February 2007 18:02, Sergey E. Koposov wrote:
 On Wed, 28 Feb 2007, Joachim Wieland wrote:
  On Wed, Feb 28, 2007 at 12:56:13PM -0500, Tom Lane wrote:
  It hasn't ever made it onto the TODO list, which means there's not a
  consensus that we need it.
 
  Such a patch could improve the acceptance of PostgreSQL in shared hosting
  environments. Note that a database without quotas can be filled up easily
  and the database will stop serving requests to other users' databases.

 Yes, I agree.

  There is a quota implementation already in the archives but I don't know
  more about it than that it exists:
 
  http://archives.postgresql.org/pgsql-hackers/2004-07/msg00392.php

 I remember that thread, but I think there was no patch at all, at least I
 didn't see it.


Sergey, could you do a little research on what behavior other databases that 
support user quotes exhibit?  This might help folks judge whether any 
proposed solution for postgres will be above or below potential users 
expectations. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] SOC user quotas

2007-02-28 Thread Sergey E. Koposov

On Wed, 28 Feb 2007, Robert Treat wrote:


Sergey, could you do a little research on what behavior other databases that
support user quotes exhibit?  This might help folks judge whether any
proposed solution for postgres will be above or below potential users
expectations.


Ok, I will.

Regards,
Sergey

***
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg 
Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org