Re: [HACKERS] WAL, xl_heap_insert and tuple oid mystry
On 11.04.2011 23:35, jagan wrote: Hi, Suppose I create a table as follows: CREATE TABLE test2 (name TEXT, age INTEGER) WITH oids; Now, for every tuple in this table is associated with a unique oid, which I can retrieve by: SELECT oid, name, age FROM test2; which works great. So far so good. Now, if look at the corresponding WAL entry for any insert into this relation, it creates the following WAL entry which I can decode. XLogRecord --- Followed by -- xl_heap_insert struct --followed by -- xl_heap_header -- Followed by -- tuple data If I use the macro HeapTupleHeaderGetOid([xl_heap_header struct]) Yeah, that won't work because xl_heap_header is not a HeapTupleHeader. or equivalently if I test ([xl_heap_header struct]-t_infomask HEAP_HASOID), it tells me that the tuple oid is not stored with this record. That's strange, the flag should be set. Where is the OID of tuple stored in a WAL record of a tuple? If not with xl_heap_header, where is it stored? Is it stored at all? It's stored in the tuple data portion. See the code that writes the WAL record, here: http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/backend/access/heap/heapam.c;h=1fbd8b39b4a73f6aee337fccf2dcce548d0850bb;hb=HEAD#l1987 Maybe the WAL record you're looking at is a full-page image? A record with a full-page image includes a verbatim copy of the page, and the individual tuple is omitted in that case. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Back branch update releases this week; beta postponed
--On 10. April 2011 13:53:52 -0400 Tom Lane t...@sss.pgh.pa.us wrote: In view of the recently-discovered data loss bug in pg_upgrade, it seems imperative to push out update releases fixing that as soon as possible. The core team has therefore decided to wrap back-branch update releases this Thursday for release Monday 4/18. Hmm, I would like to see the patch for http://archives.postgresql.org/pgsql-bugs/2011-03/msg00261.php going in for 8.4.8. We are currently running a patched RPM on a production standby on the customers site, and it seems to work well, no hangs occured so far. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows build issues
On Mon, Apr 11, 2011 at 22:16, Peter Eisentraut pete...@gmx.net wrote: On tor, 2011-04-07 at 16:20 -0400, Robert Haas wrote: It sure would be nice if someone would write a doc patch, or at least a wiki page, explaining all the permutations here... I get the impression it's not that hard to set up if you are reasonable comfortable working in a Windows environment, but it's pretty intimidating if you aren't. Here's a patch I came up with that matches my experience, but evidently there could be many others. Have we shown for sure that you can't build it with the compiler that's in the SDK? If not, I still think that should be our primary option - I certainly don't see how it's obsolete. (and you can, afaics, still get the platform sdk with the correct version of the compiler (non-vs2010), but I haven't tested it) Or did I miss something in this thread? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows build issues
On 04/12/2011 08:23 AM, Magnus Hagander wrote: On Mon, Apr 11, 2011 at 22:16, Peter Eisentrautpete...@gmx.net wrote: On tor, 2011-04-07 at 16:20 -0400, Robert Haas wrote: It sure would be nice if someone would write a doc patch, or at least a wiki page, explaining all the permutations here... I get the impression it's not that hard to set up if you are reasonable comfortable working in a Windows environment, but it's pretty intimidating if you aren't. Here's a patch I came up with that matches my experience, but evidently there could be many others. Have we shown for sure that you can't build it with the compiler that's in the SDK? If not, I still think that should be our primary option - I certainly don't see how it's obsolete. (and you can, afaics, still get the platform sdk with the correct version of the compiler (non-vs2010), but I haven't tested it) Or did I miss something in this thread? I haven't actually tried using the SDK alone. I'll try testing that on a cloud appliance when I get a chance, as I'm out of Windows boxes I can reasonable perturb. I do know you *need* the SDK for 64 bit builds with VSE 2008, as it doesn't include a 64 bit compiler. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows build issues
On Tue, Apr 12, 2011 at 14:51, Andrew Dunstan and...@dunslane.net wrote: On 04/12/2011 08:23 AM, Magnus Hagander wrote: On Mon, Apr 11, 2011 at 22:16, Peter Eisentrautpete...@gmx.net wrote: On tor, 2011-04-07 at 16:20 -0400, Robert Haas wrote: It sure would be nice if someone would write a doc patch, or at least a wiki page, explaining all the permutations here... I get the impression it's not that hard to set up if you are reasonable comfortable working in a Windows environment, but it's pretty intimidating if you aren't. Here's a patch I came up with that matches my experience, but evidently there could be many others. Have we shown for sure that you can't build it with the compiler that's in the SDK? If not, I still think that should be our primary option - I certainly don't see how it's obsolete. (and you can, afaics, still get the platform sdk with the correct version of the compiler (non-vs2010), but I haven't tested it) Or did I miss something in this thread? I haven't actually tried using the SDK alone. I'll try testing that on a cloud appliance when I get a chance, as I'm out of Windows boxes I can Yeah, I'm in the same situation on that, which is why I haven't tested anythning (yet) myself. reasonable perturb. I do know you *need* the SDK for 64 bit builds with VSE 2008, as it doesn't include a 64 bit compiler. That alone seems reason enough to recommend the SDK build method over VSE, so we don't have to recommend different methods depending. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Back branch update releases this week; beta postponed
Bernd Helmle maili...@oopsware.de writes: --On 10. April 2011 13:53:52 -0400 Tom Lane t...@sss.pgh.pa.us wrote: The core team has therefore decided to wrap back-branch update releases this Thursday for release Monday 4/18. Hmm, I would like to see the patch for http://archives.postgresql.org/pgsql-bugs/2011-03/msg00261.php going in for 8.4.8. Simon, was there a reason you only back-patched that to 9.0? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL, xl_heap_insert and tuple oid mystry
Hi, Where is the OID of tuple stored in a WAL record of a tuple? If not with xl_heap_header, where is it stored? Is it stored at all? It's stored in the tuple data portion. Is the OID also recorded with xl_heap_delete record as well or just the xl_heaptid? From my reading of the code it is not recorded. In general, why is OID of a tuple relegated as just another tuple data, when it can replace xl_heaptid as a much more stable tuple identifier. I understand that by recording tuples in terms of xl_heaptid (i.e., page/offset) means that the db can recover quickly but isn't there some value to storing a much higher level information in the WAL logs? For example, tuple manipulations, if recorded in terms of manipulation on tuple oids, can support applications such as these: http://blogs.enterprisedb.com/2011/03/03/more-musings-on-logical-replication/ Just a thought. Thanks, Jagan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL, xl_heap_insert and tuple oid mystry
On 12.04.2011 19:42, jagan wrote: Where is the OID of tuple stored in a WAL record of a tuple? If not with xl_heap_header, where is it stored? Is it stored at all? It's stored in the tuple data portion. Is the OID also recorded with xl_heap_delete record as well or just the xl_heaptid? From my reading of the code it is not recorded. Correct, it's not. In general, why is OID of a tuple relegated as just another tuple data, when it can replace xl_heaptid as a much more stable tuple identifier. I understand that by recording tuples in terms of xl_heaptid (i.e., page/offset) means that the db can recover quickly but isn't there some value to storing a much higher level information in the WAL logs? For example, tuple manipulations, if recorded in terms of manipulation on tuple oids, can support applications such as these: http://blogs.enterprisedb.com/2011/03/03/more-musings-on-logical-replication/ Possibly. On the other hand, another common complaint is that PostgreSQL writes a lot of WAL, much more than other DBMSs. Adding more higher-level information to WAL records would make that issue even worse. At the moment we only include the minimum amount of information needed for recovery. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] fn_collation in FmgrInfo considered harmful
I wrote: So, unless there's a really good reason why fn_collation should be in FmgrInfo and not FunctionCallInfo, I'm going to see about moving it. It looks like the single largest PITA involved in this change is that the FunctionCallN/OidFunctionCallN/DirectFunctionCallN families of functions really ought to take a collation argument, and there are approximately 540 existing calls of those functions in the source tree. Of those calls, a pretty substantial majority don't really need collation info, because they are calling functions that are known not to care about collations. So while I could go around and add an InvalidOid argument to each one, it seems like an invasive change for rather small benefit. What I'm thinking about doing instead is establishing these conventions: 1. The existing names with a C appended (eg, OidFunctionCall2C) will take a collation argument (in particular, this replaces the existing DirectFunctionCall1WithCollation and DirectFunctionCall2WithCollation, which seem a bit verbosely named for my tastes). 2. The actual functions in fmgr.c will just be the C versions. We'll preserve source-level compatibility by #define'ing the old names as macros that expand to call the C functions with InvalidOid for the collation. This will avoid needing source-code changes except in the places where collations actually have to be passed. I don't think this is quite what we would have done if starting in a green field, but I doubt it's worth the hassle to convert all the existing calls to add an argument. Objections, better ideas? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] fn_collation in FmgrInfo considered harmful
On Tuesday, April 12, 2011 08:09:53 PM Tom Lane wrote: 1. The existing names with a C appended (eg, OidFunctionCall2C) will take a collation argument (in particular, this replaces the existing DirectFunctionCall1WithCollation and DirectFunctionCall2WithCollation, which seem a bit verbosely named for my tastes). The first thing I though when I saw OidFunctionCall2C was Function Call with C collation or such and that you wanted to rename all the existing calls to that... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] fn_collation in FmgrInfo considered harmful
Andres Freund and...@anarazel.de writes: On Tuesday, April 12, 2011 08:09:53 PM Tom Lane wrote: 1. The existing names with a C appended (eg, OidFunctionCall2C) will take a collation argument (in particular, this replaces the existing DirectFunctionCall1WithCollation and DirectFunctionCall2WithCollation, which seem a bit verbosely named for my tastes). The first thing I though when I saw OidFunctionCall2C was Function Call with C collation or such and that you wanted to rename all the existing calls to that... Hm, well, you got a better idea? I definitely want it *short*, because these are going to be in a lot of places. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] fn_collation in FmgrInfo considered harmful
On Tuesday, April 12, 2011 09:00:40 PM Tom Lane wrote: Andres Freund and...@anarazel.de writes: On Tuesday, April 12, 2011 08:09:53 PM Tom Lane wrote: 1. The existing names with a C appended (eg, OidFunctionCall2C) will take a collation argument (in particular, this replaces the existing DirectFunctionCall1WithCollation and DirectFunctionCall2WithCollation, which seem a bit verbosely named for my tastes). The first thing I though when I saw OidFunctionCall2C was Function Call with C collation or such and that you wanted to rename all the existing calls to that... Hm, well, you got a better idea? I definitely want it *short*, because these are going to be in a lot of places. Not really. Maybe DirectFunctionCall1Coll or even DirectFCall1Coll... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] fn_collation in FmgrInfo considered harmful
Andres Freund and...@anarazel.de writes: On Tuesday, April 12, 2011 09:00:40 PM Tom Lane wrote: Hm, well, you got a better idea? I definitely want it *short*, because these are going to be in a lot of places. Not really. Maybe DirectFunctionCall1Coll or even DirectFCall1Coll... xxxFunctionCallNColl would probably work. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows build issues
On Tue, 12 Apr 2011 08:51:57 -0400, Andrew Dunstan and...@dunslane.net wrote: that's in the SDK? If not, I still think that should be our primary option - I certainly don't see how it's obsolete. (and you can, afaics, still get the platform sdk with the correct version of the compiler (non-vs2010), but I haven't tested it) Or did I miss something in this thread? Have we shown for sure that you can't build it with the compiler I haven't actually tried using the SDK alone. I'll try testing that on a cloud appliance when I get a chance, as I'm out of Windows boxes I can reasonable perturb. I do know you *need* the SDK for 64 bit builds with VSE 2008, as it doesn't include a 64 bit compiler. I've just built current git HEAD on a clean Windows XP virtual machine with only Microsoft Windows SDK for Windows 7 and .NET Framework 3.5 SP 1 (the latest pre VS 2010 SDK) on it. It builds without problems (a few warnings) and passes vcregress check. VS or VS EXPRESS is probably not needed to build Postgres with any recent Windows SDK that includes compilers (= 6.0). This Wikipedia article might shed some more light on the MS SDK chaos: http://en.wikipedia.org/wiki/Microsoft_Windows_SDK Regards, Brar -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL, xl_heap_insert and tuple oid mystry
On Tue, Apr 12, 2011 at 7:55 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Maybe the WAL record you're looking at is a full-page image? A record with a full-page image includes a verbatim copy of the page, and the individual tuple is omitted in that case. It is? I thought full page images were only in addition to, not instead of any data in the record. Doesn't pg_lesslog depend on this? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Fix that NOSUPERUSER implies REPLICATION unless specified contrarily
Also add some regression tests for that behaviour. Found after seing a report about it in IRC by Daniel Grace. --- src/backend/commands/user.c |3 +- src/test/regress/expected/privileges.out | 35 src/test/regress/sql/privileges.sql | 37 ++ 3 files changed, 74 insertions(+), 1 deletions(-) diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index f13eb28..f917184 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -244,7 +244,8 @@ CreateRole(CreateRoleStmt *stmt) * Superusers get replication by default, but only if * NOREPLICATION wasn't explicitly mentioned */ - if (!(disreplication intVal(disreplication-arg) == 0)) + if (issuper + !(disreplication intVal(disreplication-arg) == 0)) isreplication = 1; } if (dinherit) diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 5cda230..11aaa3e 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -12,6 +12,7 @@ DROP ROLE IF EXISTS regressuser3; DROP ROLE IF EXISTS regressuser4; DROP ROLE IF EXISTS regressuser5; DROP ROLE IF EXISTS regressuser6; +DROP ROLE IF EXISTS regressusercreaterole; SELECT lo_unlink(oid) FROM pg_largeobject_metadata; lo_unlink --- @@ -26,6 +27,7 @@ CREATE USER regressuser4; CREATE USER regressuser5; CREATE USER regressuser5; -- duplicate ERROR: role regressuser5 already exists +CREATE USER regressusercreaterole CREATEROLE; CREATE GROUP regressgroup1; CREATE GROUP regressgroup2 WITH USER regressuser1, regressuser2; ALTER GROUP regressgroup1 ADD USER regressuser4; @@ -1216,6 +1218,36 @@ SELECT has_function_privilege('regressuser1', 'testns.testfunc(int)', 'EXECUTE') SET client_min_messages TO 'warning'; DROP SCHEMA testns CASCADE; RESET client_min_messages; +-- CREATEROLE/SUPERUSER/REPLICATION tests +\c +CREATE USER regressuser7 SUPERUSER; +DROP USER regressuser7; +CREATE USER regressuser7 NOSUPERUSER; +DROP USER regressuser7; +CREATE USER regressuser7 SUPERUSER NOREPLICATION; +DROP USER regressuser7; +SET SESSION AUTHORIZATION regressuser1; +CREATE USER regressuser7; --fail +ERROR: permission denied to create role +DROP USER regressuser7; --fail +ERROR: permission denied to drop role +SET SESSION AUTHORIZATION regressusercreaterole; +CREATE USER regressuser7 SUPERUSER; --fail +ERROR: must be superuser to create superusers +DROP USER regressuser7; --fail +ERROR: role regressuser7 does not exist +CREATE USER regressuser7 NOSUPERUSER; +DROP USER regressuser7; +CREATE USER regressuser7 CREATEROLE; +DROP USER regressuser7; +CREATE USER regressuser7 NOSUPERUSER NOREPLICATION NOCREATEROLE; +DROP USER regressuser7; +CREATE USER regressuser7 REPLICATION; --fail +ERROR: must be superuser to create replication users +DROP USER regressuser7; --fail +ERROR: role regressuser7 does not exist +CREATE USER regressuser7 NOREPLICATION; +DROP USER regressuser7; -- clean up \c drop sequence x_seq; @@ -1260,3 +1292,6 @@ DROP USER regressuser4; DROP USER regressuser5; DROP USER regressuser6; ERROR: role regressuser6 does not exist +DROP USER regressuser7; +ERROR: role regressuser7 does not exist +DROP USER regressusercreaterole; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index a87ce77..d01455f 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -16,6 +16,7 @@ DROP ROLE IF EXISTS regressuser3; DROP ROLE IF EXISTS regressuser4; DROP ROLE IF EXISTS regressuser5; DROP ROLE IF EXISTS regressuser6; +DROP ROLE IF EXISTS regressusercreaterole; SELECT lo_unlink(oid) FROM pg_largeobject_metadata; @@ -29,6 +30,7 @@ CREATE USER regressuser3; CREATE USER regressuser4; CREATE USER regressuser5; CREATE USER regressuser5; -- duplicate +CREATE USER regressusercreaterole CREATEROLE; CREATE GROUP regressgroup1; CREATE GROUP regressgroup2 WITH USER regressuser1, regressuser2; @@ -670,6 +672,39 @@ SET client_min_messages TO 'warning'; DROP SCHEMA testns CASCADE; RESET client_min_messages; +-- CREATEROLE/SUPERUSER/REPLICATION tests +\c +CREATE USER regressuser7 SUPERUSER; +DROP USER regressuser7; + +CREATE USER regressuser7 NOSUPERUSER; +DROP USER regressuser7; + +CREATE USER regressuser7 SUPERUSER NOREPLICATION; +DROP USER regressuser7; + +SET SESSION AUTHORIZATION regressuser1; +CREATE USER regressuser7; --fail +DROP USER regressuser7; --fail + +SET SESSION AUTHORIZATION regressusercreaterole; +CREATE USER regressuser7 SUPERUSER; --fail +DROP USER regressuser7; --fail + +CREATE USER regressuser7 NOSUPERUSER; +DROP USER regressuser7; + +CREATE USER regressuser7 CREATEROLE; +DROP USER regressuser7; + +CREATE USER regressuser7 NOSUPERUSER NOREPLICATION
Re: [HACKERS] WAL, xl_heap_insert and tuple oid mystry
Greg Stark gsst...@mit.edu writes: On Tue, Apr 12, 2011 at 7:55 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Maybe the WAL record you're looking at is a full-page image? A record with a full-page image includes a verbatim copy of the page, and the individual tuple is omitted in that case. It is? I thought full page images were only in addition to, not instead of any data in the record. No, that's not the case. Doesn't pg_lesslog depend on this? One hopes not. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL, xl_heap_insert and tuple oid mystry
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 12.04.2011 19:42, jagan wrote: In general, why is OID of a tuple relegated as just another tuple data, when it can replace xl_heaptid as a much more stable tuple identifier. Possibly. On the other hand, another common complaint is that PostgreSQL writes a lot of WAL, much more than other DBMSs. Adding more higher-level information to WAL records would make that issue even worse. Actually, the real problem with that proposal is that it assumes that the OID (a) exists and (b) is unique, neither of which is guaranteed. Especially not when you consider that WAL log entries have to correctly distinguish which MVCC version of a tuple is being modified. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL, xl_heap_insert and tuple oid mystry
(2011/04/13 8:34), Tom Lane wrote: Greg Starkgsst...@mit.edu writes: Doesn't pg_lesslog depend on this? One hopes not. AFAIK it's safe because pg_lesslog removes full-page image only when it has enough information for substitute incremental log. For example of XLOG_HEAP_INSERT, pg_lesslog generates incremental logs such as xl_heap_header and tuple image from the inserted tuple in the full-page image, and replaces full-page image with them. Regards, -- Shigeru Hanada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers