Re: [HACKERS] Must be owner to truncate?
Does truncate not being MVCC-safe cause problems in your situation? It certainly doesn't in mine and I expect the same is true for alot of others in the same situation. Well, it is done inside a transaction, plus has concurrent use... Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Must be owner to truncate?
Stephen Frost wrote: delete from x;/truncate x; -- Creates a new, empty, file and makes it the 'current' file -- Marks the old file for deletion, but it is kept around for any transactions which were started before the truncate; -- New transactions use the empty file -- Once all transactions using the old file have completed, the old file can be deleted. -- Old transactions which insert rows would need to use the new file or scan the old file for rows which they added, I suppose. And when the transaction that issued the TRUNCATE aborts after step 3, but newer transactions commit? Mike Mascari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Must be owner to truncate?
* Mike Mascari (mascarm@mascari.com) wrote: Stephen Frost wrote: delete from x;/truncate x; -- Creates a new, empty, file and makes it the 'current' file -- Marks the old file for deletion, but it is kept around for any transactions which were started before the truncate; -- New transactions use the empty file -- Once all transactions using the old file have completed, the old file can be deleted. -- Old transactions which insert rows would need to use the new file or scan the old file for rows which they added, I suppose. And when the transaction that issued the TRUNCATE aborts after step 3, but newer transactions commit? The newer transactions would have to check for that situation. It's not completely thought through, but at the same time I don't necessairly think it's something that would be completely impossible to do and still retain most of the performance benefits, at least in the most common case. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Must be owner to truncate?
Stephen Frost [EMAIL PROTECTED] writes: * Mike Mascari (mascarm@mascari.com) wrote: And when the transaction that issued the TRUNCATE aborts after step 3, but newer transactions commit? The newer transactions would have to check for that situation. How would they do that? They might be long gone by the time the truncating transaction rolls back. It might be possible to do something that preserves full MVCC-ness for concurrent readers, but I don't believe there is any choice but to lock out concurrent writers until the truncate commits. If you try to allow that, there's no way to keep straight whose change goes into which file. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] roles question
After months of being unable to keep up with what's going on here, I'm trying to educate myself on some of the latest developments. I was playing with roles a bit, and I don't know if I'm doing something wrong, or if I found a hole: works fine: regression=# create role testrole2 with user testuser; CREATE ROLE doesn't work: regression=# alter role testrole with user testuser; ERROR: option rolemembers not recognized works fine: regression=# alter group testrole add user testuser; ALTER ROLE doesn't work (no surprise, is a syntax error): regression=# alter role testrole add user testuser; ERROR: syntax error at or near add at character 21 LINE 1: alter role testrole add user testuser; It seems I can create a role with a user, and I can use ALTER GROUP syntax to add a user to a role, but I can't figure out how to add a user to a role using ALTER ROLE. Similarly, these might be just plain abuse of the new syntax, but appear to be allowed by gram.y: regression=# alter user testuser in role testrole; ERROR: option addroleto not recognized regression=# alter user testuser in group testrole; ERROR: option addroleto not recognized Thanks, Joe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] roles question
* Joe Conway ([EMAIL PROTECTED]) wrote: After months of being unable to keep up with what's going on here, I'm trying to educate myself on some of the latest developments. I was playing with roles a bit, and I don't know if I'm doing something wrong, or if I found a hole: Things have changed a bit (we're more closely following the SQL spec, for one thing :). works fine: regression=# create role testrole2 with user testuser; CREATE ROLE I don't think it's actually required (per spec) for us to allow this, but we do because it makes some sense. doesn't work: regression=# alter role testrole with user testuser; ERROR: option rolemembers not recognized To give 'testuser' the rights of 'testrole' you should do: grant testrole to testuser; works fine: regression=# alter group testrole add user testuser; ALTER ROLE This works for backwards compatibility, really, not because it's something the 'ALTER ROLE' command is supposted to be able to do. doesn't work (no surprise, is a syntax error): regression=# alter role testrole add user testuser; ERROR: syntax error at or near add at character 21 LINE 1: alter role testrole add user testuser; I had made this work in my original patch (iirc) but I believe Tom dropped it because you really should be using GRANT instead. It seems I can create a role with a user, and I can use ALTER GROUP syntax to add a user to a role, but I can't figure out how to add a user to a role using ALTER ROLE. Using the 'grant role statement', per the SQL spec. Similarly, these might be just plain abuse of the new syntax, but appear to be allowed by gram.y: regression=# alter user testuser in role testrole; ERROR: option addroleto not recognized regression=# alter user testuser in group testrole; ERROR: option addroleto not recognized This is because create role, alter role, alter user, etc, use the same set of options (since there's a large overlap) in the syntax, though some things don't make sense for some of those commands. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] roles question
Stephen Frost wrote: To give 'testuser' the rights of 'testrole' you should do: grant testrole to testuser; This is because create role, alter role, alter user, etc, use the same set of options (since there's a large overlap) in the syntax, though some things don't make sense for some of those commands. Ah, that clears things up considerably -- Thanks Stephen! Joe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
On R, 2005-07-08 at 14:45 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I don't think we should care too much about indexes. We can rebuild them...but losing heap sectors means *data loss*. There might be some merit in idea to disabling WAL/PITR for indexes, where one can accept some (and possibly a lot) time when recovering. If you're so concerned about *data loss* then none of this will be acceptable to you at all. We are talking about going from a system that can actually survive torn-page cases to one that can only tell you whether you've lost data to such a case. Arguing about the probability with which we can detect the loss seems beside the point. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Must be owner to truncate?
On L, 2005-07-09 at 09:47 -0400, Mike Mascari wrote: Stephen Frost wrote: delete from x;/truncate x; -- Creates a new, empty, file and makes it the 'current' file -- Marks the old file for deletion, but it is kept around for any transactions which were started before the truncate; -- New transactions use the empty file -- Once all transactions using the old file have completed, the old file can be deleted. -- Old transactions which insert rows would need to use the new file or scan the old file for rows which they added, I suppose. And when the transaction that issued the TRUNCATE aborts after step 3, but newer transactions commit? should be the same as when newer transactions had used a file after a DELETE ; had been issued. Could the new file not be made to cover the next available 1GB of file space, that is a new physical file ? This could made using of same kind of machinery my proposal for concurrent index does (i.e. locks that forbid putting new tuples in certain tuple ranges) Then, if the truncating transaction commits, the N first pgysical 1GB files are removed, and just the remaining ones are used. if it aborts, the first files stay, and we just have some tuples placed sparcely starting at the next 1GB boundary. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Must be owner to truncate?
On Sat, Jul 09, 2005 at 11:43:52PM +0300, Hannu Krosing wrote:
Could the new file not be made to cover the next available 1GB of file
space, that is a new physical file ?
This could made using of same kind of machinery my proposal for
concurrent index does (i.e. locks that forbid putting new tuples in
certain tuple ranges)
I think your proposals are too handwavy, but there is a similar
mechanism outlined for on-line index reorganizarion, whereby new tuples
can be inserted concurrently with the reorganization, being stored on a
spill area. See
@inproceedings{DBLP:conf/sigmod/ZouS96,
author= {C. Zou and B. Salzberg},
editor= {H. V. Jagadish and Inderpal Singh Mumick},
title = {On-line Reorganization of Sparsely-populated B+trees},
booktitle = {Proceedings of the 1996 ACM SIGMOD International Conference on
Management of Data, Montreal, Quebec, Canada, June 4-6, 1996},
publisher = {ACM Press},
year = {1996},
pages = {115-124},
bibsource = {DBLP, \url{http://dblp.uni-trier.de}}
}
--
Alvaro Herrera (alvherre[a]alvh.no-ip.org)
Para tener más hay que desear menos
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] another roles related question
Roles cause a problem for the information schema view table_privileges. For example: CREATE TABLE tbl_1 ( f1int, f2text ); INSERT INTO tbl_1 VALUES(1, 'a'); REVOKE ALL ON tbl_1 FROM public; CREATE USER user1; CREATE USER user2; CREATE ROLE role1; GRANT ALL ON tbl_1 TO role1; GRANT ALL ON tbl_1 TO user1; GRANT role1 TO user2; -- information_schema.table_privileges is correct for user1 SET SESSION AUTHORIZATION user1; select * from information_schema.table_privileges where table_name = 'tbl_1' and privilege_type = 'SELECT'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy --+-+---+--+++--+ postgres | user1 | regression| public | tbl_1 | SELECT | NO | NO (1 row) SELECT * FROM tbl_1; f1 | f2 + 1 | a (1 row) -- information_schema.table_privileges is incorrect for user2 SET SESSION AUTHORIZATION user2; select * from information_schema.table_privileges where table_name = 'tbl_1' and privilege_type = 'SELECT'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy -+-+---+--+++--+ (0 rows) SELECT * FROM tbl_1; f1 | f2 + 1 | a (1 row) I think the problem lies with the fact that user2 lies a level down from that which is actually granted access. And since roles/users are hierarchical, it is possible to go more than 1 level deep -- hence a recursive join is really needed to fix this AFAICS. Is this something we should worry about? Or do we just put a warning in the docs? Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] another roles related question
* Joe Conway ([EMAIL PROTECTED]) wrote: Roles cause a problem for the information schema view table_privileges. Right. [...] Is this something we should worry about? Or do we just put a warning in the docs? I've already submitted a patch which should correct this. It also adds a new SQL function which determines if a given user is in a specific role. It also implements SET ROLE, CURRENT_ROLE and SYSTEM_USER. Hopefully it'll get applied in some form (I havn't had any response to it yet at all but I'm guessing people are just busy atm...). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] another roles related question
Stephen Frost wrote: Is this something we should worry about? Or do we just put a warning in the docs? I've already submitted a patch which should correct this. It also adds a new SQL function which determines if a given user is in a specific role. It also implements SET ROLE, CURRENT_ROLE and SYSTEM_USER. Hopefully it'll get applied in some form (I havn't had any response to it yet at all but I'm guessing people are just busy atm...). Oh, cool. Sorry for the noise. I'll rummage through the archives and have a look. Thanks (again), Joe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] another roles related question
* Joe Conway ([EMAIL PROTECTED]) wrote: Stephen Frost wrote: I've already submitted a patch which should correct this. It also adds a new SQL function which determines if a given user is in a specific role. It also implements SET ROLE, CURRENT_ROLE and SYSTEM_USER. Oh, cool. Sorry for the noise. I'll rummage through the archives and have a look. What you want to look for is, specifically: Subject: Re: [PATCHES] Roles - SET ROLE Updated Message-ID: [EMAIL PROTECTED] It went through a couple revisions. Thanks, Stephen signature.asc Description: Digital signature
