Re: [HACKERS] User/Group Quotas Revisited
> - Who has permissions to set the user's quota per tablespace, the > superuser and the tablespace owner? It would be nice if this were nestable, that is, if the sysadmin could carve out a tablespace for a user then the user could carve that into seperately quotated sub tables.. The idea being, a user may have several tables, some of which are likely to get big and fat and gain lots of crud, but some of which will never grow too big but you really don't want to fail just because someone floodded the other table and used up your quota. It would be nice if the user could manage that subassignment as he saw fit without assistance from the admin. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] User/Group Quotas Revisited
I've sent this again as it hadn't appeared on the list in over 4 hours;
hopefully it isn't posted twice :).
Anyway, on to the main topic...
I've spent some time looking at my user/group quota patch and have
decided to start a new one for 8.x and need some decisions made:
- When to check quota (I'm leaning toward at commit)
- Is quota determined by the ACTUAL sizes of user-owned objects.
- Quota creation syntax
CREATE USER foo QUOTA 10M ON bar;
ALTER USER foo QUOTA 1G ON bar;
CREATE GROUP group QUOTA 100M ON sometbspc;
...
- Quota removal syntax
ALTER USER foo NOQUOTA ON bar;
OR
ALTER USER foo QUOTA UNLIMITED ON bar;
- Size parameter - Currently the following is allowed (I removed
kilobytes):
file_size {digit}+[m|M|g|G|t|T]
- Who has permissions to set the user's quota per tablespace, the
superuser and the tablespace owner?
- How do we want to store quotas in the catalog? I can go with an
aclitem-like array approach at the tablespace level or create a
pg_quota relation (aclitem-like array approach seems nicer, but you may
know something I don't)
Anything else you can think of, let me know.
Thanks.
--
Jonah H. Harris, Sr. Web 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] The Contrib Roundup (long)
Tom Lane wrote: > Bruce Momjian writes: > > Kaare Rasmussen wrote: > >> I consider this a bug, or at least a badly thought out name. I can't > >> understand that someone approved 'reindex database' to mean 'reindex the > >> system tables of a database'. > > > Agreed. > > It's always bothered me too. How about > > REINDEX SYSTEM -> system tables (current meaning of R. DATABASE) > REINDEX USER -> all non-system tables > REINDEX DATABASE -> both of the above I like that. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Two-phase commit issues
On Sat, 11 Jun 2005, Jochem van Dieten wrote: The "OSI" CCR format, which appears to refer to ISO/IEC 9805-1. ISO/IEC 9805-1:1998 15-12-1998 Information technology - Open Systems Interconnection - Protocol for the Commitment, Concurrency and Recovery service element: Protocol specification This standard is to be applied by reference from other specifications. Specifies a use of the ACSE, Presentation adn Session services to carry the CCR semantics. Specifies the static and dynamic conformance requirements for systems implementing these procedures. Specifies the protocol elements that support the following functional untis: - static commitment; - dynamic commitment; - read only; - one-phase commitment; - cancel; and overlapped recovery. Unfortunately that standard is not included in my universities subscription to ISO standards so I can't tell you what it says about the format. Great, thanks anyway! Anyone here with access to the content? - Heikki ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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 ]
> > LOCATION 'directory'
> >
> > - Extension of the "ALTER TABLESPACE" command:
> > ALTER TABLESPACE name
> > {RENAME TO newname |
> > SIZE }
>
> 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] The Contrib Roundup (long)
Bruce Momjian writes: > Kaare Rasmussen wrote: >> I consider this a bug, or at least a badly thought out name. I can't >> understand that someone approved 'reindex database' to mean 'reindex the >> system tables of a database'. > Agreed. It's always bothered me too. How about REINDEX SYSTEM -> system tables (current meaning of R. DATABASE) REINDEX USER -> all non-system tables REINDEX DATABASE -> both of the above regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
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 ]
> LOCATION 'directory'
>
> - Extension of the "ALTER TABLESPACE" command:
> ALTER TABLESPACE name
> {RENAME TO newname |
> SIZE }
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] proposed TODO: non-locking CREATE INDEX / REINDEX
Hannu Krosing <[EMAIL PROTECTED]> writes: > On R, 2005-06-10 at 12:12 -0400, Tom Lane wrote: >> Have you forgotten Zeno's paradox? I don't see a >> reason to assume the indexer can *ever* catch up --- it's entirely >> likely that adding a new unindexed row is faster than adding an index >> entry for it. > The same is true of doing a lazy vacuum over a table where tuples are > constantly added - there is no guarantee that the vacuum will ever > finish. No, there definitely is such a guarantee: the vacuum only scans as many blocks as were in the relation when it started. The vacuum need not worry about tuples added after it starts, because it couldn't delete them under MVCC rules. And there is no logical-consistency requirement for it to promise to scan every tuple, anyway. >> This implies that you are hoping for an asynchronous change in the >> behavior of other processes, which you are not going to get without >> taking out locks, which is what you wanted to avoid. > One way to avoid locking, is to allow the "add tuple to index" routine > silently succeed if the index already has it. ... thereby silently breaking unique-index checking, you mean? > Then we can announce the change in behaviour to running backends, wait > for all backends to confirm they have learned about it and only then > record CTID_INDEX_MAX. You can't wait for confirmation from all other backends without expecting to create deadlock issues left and right. And what is it you are waiting for, anyway? For a backend to confirm that it is prepared to insert into an index that it can't even see yet (because the CREATE INDEX hasn't committed)? In the case of REINDEX, are you expecting that backends will be prepared to insert into *both* old and new versions of the index? They'd better, since there's still every prospect of the REINDEX failing and rolling back, leaving the old version as the live copy. Speaking of rollback, what happens when those backends try to insert into the new copy just after the REINDEX has failed and rolled back and deleted the new copy? Or equivalently, what happens when they are still trying to insert into the old copy just after the REINDEX commits and deletes that one? The complexity and fragility of what you are proposing vastly outweighs any potential benefit, even if it could be made to work at all, which I continue to doubt. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] The Contrib Roundup (long)
On 6/10/2005 3:04 PM, Tom Lane wrote: pgbench: I see repeated complaints on -performance about how pgbench results are misleading. Why are we shipping it with PostgreSQL then? It's handy to have *some* simple concurrent-behavior test included, even if it's not something we put a lot of stock in. The parallel regression tests are a joke as far as exercising concurrent updates go --- I think pg_bench is an important test tool for that reason. I'd not vote to remove this without a better replacement. In any case it shouldn't have a name that suggests any relationship with performance measurement. Maybe we can rename this one to pgstresstest1 or something similar? spi: contains TimeTravel functions. Do these actually still work? The spi stuff is good for documentation purposes anyway ... but if the functions aren't working, should be in the docs and not /contrib. Not only do they work, several of them are used in the regression tests. But I wonder about their general usefullness. Most of the functions in here are rather examples how to develop simple triggers in C. Triggers that can be defined in 5 lines of pl/pgsql and looking at the logs they all predate procedural languages (and foreign keys in the refint case). I'd say they have more educational character and should move into documentation. Those functions used by the regression test are supposed to be under src/test/regression. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposed toast info extraction function for disaster
Tom Lane wrote: Hmm. Maybe we need something more like a "lint check" for tables, ie run through and look for visibly corrupt data, such as obviously impossible lengths for varlena fields. Come to think of it, didn't someone already write something close to this a few years ago? Sounds like pgfsck: http://svana.org/kleptog/pgsql/pgfsck.html Unfortunately it doesn't seem to have been updated to work with recent versions of PG (> 7.3) -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Request for Comments: ALTER [OBJECT] SET SCHEMA
--On Freitag, Juni 10, 2005 21:20:33 +0200 [EMAIL PROTECTED] wrote: Wouldn't ALTER [OBJECT] RENAME TO [schema.][name] be a better? After all, this is essentially a rename operation, so maybe it is better to extend existing syntax... I don't think it's a good idea to merge two different semantics: Renaming a table and "moving" a table to a different schema should be distinguished. Furthermore, i think it's too error prone, because people could accidently issue a "schema move" and renaming a table by a typo -- Bernd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] The Contrib Roundup (long)
Kaare Rasmussen wrote: > > > Either you're misunderstanding what "reindex database" does (it reindexes > > only the system catalogs), or you're misunderstanding what reindexdb does > > OK, I was taking the face value here. > > I consider this a bug, or at least a badly thought out name. I can't > understand that someone approved 'reindex database' to mean 'reindex the > system tables of a database'. Agreed. -- 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] Two-phase commit issues
On 6/11/05, Heikki Linnakangas wrote: > > It matches with the format in the JTA spec, but the JTA spec also mentions > the OCI CCR format The "OSI" CCR format, which appears to refer to ISO/IEC 9805-1. ISO/IEC 9805-1:1998 15-12-1998 Information technology - Open Systems Interconnection - Protocol for the Commitment, Concurrency and Recovery service element: Protocol specification This standard is to be applied by reference from other specifications. Specifies a use of the ACSE, Presentation adn Session services to carry the CCR semantics. Specifies the static and dynamic conformance requirements for systems implementing these procedures. Specifies the protocol elements that support the following functional untis: - static commitment; - dynamic commitment; - read only; - one-phase commitment; - cancel; and overlapped recovery. Unfortunately that standard is not included in my universities subscription to ISO standards so I can't tell you what it says about the format. Jochem ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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 ]
LOCATION 'directory'
- Extension of the "ALTER TABLESPACE" command:
ALTER TABLESPACE name
{RENAME TO newname |
SIZE }
- 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] Two-phase commit issues
On Tue, 7 Jun 2005, Alvaro Herrera wrote: On Sat, May 21, 2005 at 06:57:24PM +0300, Heikki Linnakangas wrote: Heikki, I took a closer look at the JTA spec and saw that the Xid, which is translated to a gid in the jdbc driver, consists of a format identifier (32-bit int), a branch qualifier (max 64 bytes) and a global transaction identifier (max 64 bytes). That means that gid needs to hold 132 raw bytes minimum. Also, it would be nice if the driver could send the gid as a bytea, without converting it to a string. Similar to using parameter markers and parse / bind messages with regular queries. That would require a change in the FE/BE protocol, right? The branch qualifier and global transaction id structure comes from the OSI CCR specification. Anyone here that knows more about OSI CCR? I think I'm going to try to do this by hacking the lexer some (this has the added benefit of me learning a little about lexers). Do you have an URL to those specs you mention? How authoritative they are, I mean, they are not the SQL spec, right? The JTA spec http://java.sun.com/products/jta/ Relevant X/Open XA documents: http://www.opengroup.org/bookstore/catalog/tp.htm See especially page 19 of the "Distributed Transaction Processing: The XA Specification", it contains xa.h header file that specifies the format of the transaction identifier. It matches with the format in the JTA spec, but the JTA spec also mentions the OCI CCR format which I haven't been able to find: http://java.sun.com/products/jta/jta-1_0_1B-doc/javax/transaction/xa/Xid.html In addition to those two, I bumped into RFC2371. It basically allows any format. I don't have access to the SQL spec, so I can't comment on that. I'd regard the XA spec as the most authoritative standard in the field. - Heikki ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] The Contrib Roundup (long)
> Either you're misunderstanding what "reindex database" does (it reindexes > only the system catalogs), or you're misunderstanding what reindexdb does OK, I was taking the face value here. I consider this a bug, or at least a badly thought out name. I can't understand that someone approved 'reindex database' to mean 'reindex the system tables of a database'. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
