SEO

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.

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. If I
understand correctly, only functions like mdcreate & mdextend really
change the size of the user relations (I don't consider things like WAL,
and I don't think it should be subject for quota). And it seems to me,
that the machinery of smgr/md is moreless enough to control the space
occupied by the relations (within some 1 block size precision).

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. As soon as this number is equal to
zero, all the mdcreate, mdextend functions shouldn't do anything but
return the error. I don't know, but I hope these functions won't be
invoked if the user will do DELETE and/or VACUUM to recover the space ?
Also, I'm not completely sure that refusing the call of the mdextend
function in the case of quota excess won't lead to any corruption ? (in
the case of Btree splits for example ).

Any comments ?
Thank you.

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: math@sai.msu.ru

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

"Sergey E. Koposov" <math@sai.msu.ru> 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

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

On Wed, 28 Feb 2007, Tom Lane wrote:

> "Sergey E. Koposov" <math@sai.msu.ru> 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: math@sai.msu.ru

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

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

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: math@sai.msu.ru

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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: math@sai.msu.ru
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


--

=== 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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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: math@sai.msu.ru

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

> 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: math@sai.msu.ru
>
> ---------------------------(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

"Sergey E. Koposov" <math@sai.msu.ru> 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

Tom Lane wrote:
> "Sergey E. Koposov" <math@sai.msu.ru> 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