Re: [HACKERS] reducing the overhead of frequent table locks, v4
* Jeff Davis: Does this happen to be based on some academic research? I don't necessarily expect it to be; just thought I'd ask. Paul E. McKenney's thesis contains a few references. It's called asymmetrical reader-writer locking there, and Ingo Molnar implemented this as brlock in Linux 2.4. The earliest citation seems to be W.C. Hsiesh, W. E. Weihl, Scalable reader-writer locks for parallel systems., MIT-LCS-TR-521, published in 1991. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] [GENERAL] Creating temp tables inside read only transactions
Christopher Browne wrote: Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1, there's a pretty good reason NOT to support that, namely that this breaks relational handling of tables. PostgreSQL is a *relational* database system, hence it's preferable for structures to be relational, as opposed to hierarchical, which is what any of the suggested nestings are. I won't argue with whether or not nested naming is a good idea, but I will argue with your other comment about breaking relational handling. A relational database is a database in which all data is kept in relation-typed variables, which SQL calls tables, and you can perform all queries and updates with just relation-valued expressions and statements. Organizing the tables into a multi-level namespace, either fixed-depth or variable-depth, rather than using a flat namespace, does not make the database any less relational, because the above definition and any others still hold. The less relational argument above is a red herring or distraction. One can argue against namespace nesting just fine without saying that. -- Darren Duncan -- 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] Select For Update and Left Outer Join
On 11.07.2011 05:45, Patrick Earl wrote: The ability to lock on outer joins is quite useful. I've even been contacted to ask if I was aware of any progress in this area. 9.1 has a truly serializable isolation level, so I would suggest using that instead of SELECT FOR UPDATE. -- 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] [BUGS] make_greater_string() does not return a string in some cases
Thanks for your suggestion, I'll do so. At Fri, 8 Jul 2011 23:28:32 -0400, Robert Haas robertmh...@gmail.com wrote: Please add your patch to the next CommitFest. https://commitfest.postgresql.org/action/commitfest_view/open -- Kyotaro Horiguchi NTT Open Source Software Center -- 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] [GENERAL] Creating temp tables inside read only transactions
Christopher Browne wrote: Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1, there's a pretty good reason NOT to support that, namely that this breaks relational handling of tables. PostgreSQL is a *relational* database system, hence it's preferable for structures to be relational, as opposed to hierarchical, which is what any of the suggested nestings are. Organizing the tables into a multi-level namespace, either fixed-depth or variable-depth, rather than using a flat namespace, does not make the database any less relational, because the above definition and any others still hold. The less relational argument above is a red herring or distraction. One can argue against namespace nesting just fine without saying that. -- Darren Duncan I agree with Darren. One thought that came to my mind was to use a different separator between two namespaces and/or between the database identifier and the rest of the path. Examples: ns1!ns2.table OR database@ns1.table OR database@ns1!ns2.table I've been following only some of the discussion but it seems that much ambiguity would be lost by using different separators. Schemas themselves are already non-standard so it isn't like we are constrained here in what is chosen. Just some quick thoughts I've had but haven't fully considered how they would fit in to the existing setup. But is there is any major reason why choosing different separators would not work? Also, within search_path, some form of wild-card selector would be desirable: ns1!*. I'm not opposed to having to be explicit about the search_path in order to avoid name collisions; though it would be nice if VIEWS had some kind of SET syntax, like functions do, so that the definer can specify the search_path that the view will resolve against. David J. -- 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] [GENERAL] Creating temp tables inside read only transactions
On Jul8, 2011, at 08:21 , Darren Duncan wrote: Also, the proper way to do temporary tables would be to put them in another database than the main one, where the whole other database has the property of being temporary. FWIW, Microsoft SQL Server does it that way, and as a result temporary tables are severely restricted in a number of ways. For example, custom datatypes defined in a non-temporary database cannot be used in temporary table definitions, because datatypes may only be used within the database they're defined in. You can of course re-define the data type in the temporary database, but then obviously have to do so every time you start new session because you start out with an empty tempdb. best regards, Florian Pflug -- 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] Need help understanding pg_locks
On Jul11, 2011, at 05:47 , Bruce Momjian wrote: Thank you. I think my confusion is that virtualtransaction is the lock holder/waiter, and the other two are actual locks. The attached doc patch clarifies that. I had actually realized this a few weeks ago and forgot, meaning this is pretty confusing. For consistency, I guess it should say lock object instead of simply object the description of all the columns up to (and including) objsubid, not only those of virtualxid and transactionid. I'd also slightly prefer locked object over lock object, because the lock itself probably isn't a standalone entity in the mind of most users. And for people familiar with our locking infrastructure, the actually correct term would be lock tag I believe. In any case, +1 for improving the description there. best regards, Florian Pflug -- 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] [GENERAL] Creating temp tables inside read only transactions
On Jul11, 2011, at 07:08 , Darren Duncan wrote:
Christopher Browne wrote:
Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1,
there's a pretty good reason NOT to support that, namely that this
breaks relational handling of tables. PostgreSQL is a *relational*
database system, hence it's preferable for structures to be
relational, as opposed to hierarchical, which is what any of the
suggested nestings are.
A relational database is a database in which all data is kept in
relation-typed variables, which SQL calls tables, and you can perform
all queries and updates with just relation-valued expressions and
statements.
Organizing the tables into a multi-level namespace, either fixed-depth
or variable-depth, rather than using a flat namespace, does not make
the database any less relational, because the above definition and
any others still hold.
The point was not, I think, that tables aren't suddenly relations once
namespaces are nested, but that the data model of the dbms *itself*,
i.e. the data model that defines the relationship between namespaces,
types, columns, type, ... becomes harder to map to the relational model.
For example, if namespaces can be nested, you'll need to resort to
recursive SQL and/or arrays far more often if you inspect the structure
of a database.
Btw, another argument against nested namespaces is that it actually
doesn't buy you anything in SQL, even if you solve the parsing
ambiguities. In programming languages, namespaces not only prevent
name clashes, the also defines the possible scopes to resolve unqualified
names with. For example, if you do
void f() { printf(outer); }
namespace a {
void f() { printf(inner); }
namespace b {
void g() {f();}
}
}
in C++, then a::b::g() prints inner. But in PostgreSQL, the scope in
which to resolve unqualified function is entirely determined by the the
search_path setting, *not* by the scope of the object containing the
unqualified name. Nested namespaces thus simply become of matter of
syntax - i.e., whether you can write a.b.c, or need to write a.b.c.
best regards,
Florian Pflug
--
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] Full GUID support
On Sun, Jul 10, 2011 at 20:59, Josh Berkus j...@agliodbs.com wrote: On 7/3/11 2:02 PM, Tom Lane wrote: Yeah. If there were One True Way to create a UUID, I would probably agree that we should push that functionality into core. But there are a lot of ways (and the reason for that is that they all suck in one fashion or another :-(). Between that and the lack of portability of many of the better ways, this is something I'm happy to keep at arm's length. Also, I think that UUIDs fall into the class of datatypes used by less than 10% of users which should always remain extensions. I'd consider CITEXT for core before UUID. UUID *is* in core. It's just the generation functions that aren't. -- 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] [GENERAL] Creating temp tables inside read only transactions
On Mon, Jul 11, 2011 at 10:12 AM, Florian Pflug f...@phlo.org wrote: On Jul11, 2011, at 07:08 , Darren Duncan wrote: Christopher Browne wrote: Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1, there's a pretty good reason NOT to support that, namely that this breaks relational handling of tables. PostgreSQL is a *relational* database system, hence it's preferable for structures to be relational, as opposed to hierarchical, which is what any of the suggested nestings are. A relational database is a database in which all data is kept in relation-typed variables, which SQL calls tables, and you can perform all queries and updates with just relation-valued expressions and statements. Organizing the tables into a multi-level namespace, either fixed-depth or variable-depth, rather than using a flat namespace, does not make the database any less relational, because the above definition and any others still hold. The point was not, I think, that tables aren't suddenly relations once namespaces are nested, but that the data model of the dbms *itself*, i.e. the data model that defines the relationship between namespaces, types, columns, type, ... becomes harder to map to the relational model. Just so. It's not that it suddenly becomes no longer relational. Rather, the argument is that it was intentional for the structuring of table naming to, itself, be relational, and changing that definitely has some undesirable characteristics. The need for recursive queries is the most obvious undesirable, but it's not the only undesirable thing, by any means. Sure, there's some cool stuff that we can get out of nested namespaces, but I think we'd pay a pretty big price for it, and it shouldn't be treated as obvious that: a) It's a good thing to do so, b) It is desirable to do so, c) There will be agreement to do so. To the contrary, there are pretty good reasons to reject the idea. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_xlog error
Hi, I have problems in my database. I think it is corrupted. Folow my log when I tried to start it standalone. I have some questions: 1. I saw that the error is in base/30518/449778670_vm file. Can I rebuild this file or somethink like this? 2. In the last line of log, we can see DEBUG: shmem_exit(1): 8 callbacks to make. Where can I find these transactions to do callback and/or how can I do these callbacks? If anyone can help me, I will be really glad. DEBUG: invoking IpcMemoryCreate(size=39149568) DEBUG: removing file pg_notify/ DEBUG: InitPostgres DEBUG: my backend id is 1 LOG: database system shutdown was interrupted; last known up at 2011-07-11 11:20:55 BRT DEBUG: checkpoint record is at 601/C760AA08 DEBUG: redo record is at 601/C760AA08; shutdown TRUE DEBUG: next transaction ID: 0/2960582638; next OID: 476667689 DEBUG: next MultiXactId: 234588; next MultiXactOffset: 522535 DEBUG: oldest unfrozen transaction ID: 914493351, in database 18415351 DEBUG: transaction ID wrap limit is 3061976998, limited by database with OID 18415351 LOG: database system was not properly shut down; automatic recovery in progress LOG: consistent recovery state reached at 601/C760AA60 LOG: redo starts at 601/C760AA60 LOG: record with zero length at 601/CA6A0CE8 LOG: redo done at 601/CA6A0CA0 LOG: last completed transaction was at log time 2011-07-10 15:34:02.372812-03 LOG: checkpoint starting: end-of-recovery immediate FATAL: xlog flush request 61B/245DD1F0 is not satisfied --- flushed only to 601/CA6A0CE8 CONTEXT: writing block 0 of relation base/30518/449778670_vm DEBUG: shmem_exit(1): 8 callbacks to make -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-xlog-error-tp4575216p4575216.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] txid_current() forces a real xid
Right now, calling txid_current() causes a session to create a
non-virtual xid if not already assigned, so observing the xid creates
it, which seems kind of odd. Is that intended? Here is the C code:
TransactionId
GetTopTransactionId(void)
{
if (!TransactionIdIsValid(TopTransactionStateData.transactionId))
AssignTransactionId(TopTransactionStateData);
return TopTransactionStateData.transactionId;
}
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] Need help understanding pg_locks
Florian Pflug f...@phlo.org writes: On Jul11, 2011, at 05:47 , Bruce Momjian wrote: Thank you. I think my confusion is that virtualtransaction is the lock holder/waiter, and the other two are actual locks. The attached doc patch clarifies that. I had actually realized this a few weeks ago and forgot, meaning this is pretty confusing. For consistency, I guess it should say lock object instead of simply object the description of all the columns up to (and including) objsubid, not only those of virtualxid and transactionid. Yeah, I think this patch is going in the wrong direction altogether. It would be better to modify the description of virtualtransaction and pid to say that those are the locking entity. 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] Full GUID support
Magnus Hagander mag...@hagander.net writes: On Sun, Jul 10, 2011 at 20:59, Josh Berkus j...@agliodbs.com wrote: Also, I think that UUIDs fall into the class of datatypes used by less than 10% of users which should always remain extensions. I'd consider CITEXT for core before UUID. UUID *is* in core. It's just the generation functions that aren't. Remind me again *why* it's in core? Seems like something that ought to be an extension. 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] txid_current() forces a real xid
Bruce Momjian br...@momjian.us writes: Right now, calling txid_current() causes a session to create a non-virtual xid if not already assigned, so observing the xid creates it, which seems kind of odd. Is that intended? GetTopTransactionId (and friends) should only be called in places where the intent is to assign an xid if we haven't already got one. I'm not sure what the use case is for txid_current(), but it's at least plausible that applications using it would have the same intention. 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] Need help understanding pg_locks
On Jul11, 2011, at 17:11 , Tom Lane wrote: Florian Pflug f...@phlo.org writes: On Jul11, 2011, at 05:47 , Bruce Momjian wrote: Thank you. I think my confusion is that virtualtransaction is the lock holder/waiter, and the other two are actual locks. The attached doc patch clarifies that. I had actually realized this a few weeks ago and forgot, meaning this is pretty confusing. For consistency, I guess it should say lock object instead of simply object the description of all the columns up to (and including) objsubid, not only those of virtualxid and transactionid. Yeah, I think this patch is going in the wrong direction altogether. It would be better to modify the description of virtualtransaction and pid to say that those are the locking entity. Hm, we already kinda of say that. Both descriptions include the phrase ... holding or awaiting this lock.. The column mode says ... held or desired by this process, which I guess is similar enough to make it clear that these are related. Its the columns which refer to the locked object which simply say object, and thus leave it open if that means locked or a locking. Could we split that table in two parts, one for the fields referring to the locked object and one for the locking entity, or does that depart too far from the way we document other system catalogs and views? If splitting it into two parts is too radical, how about adding a column Refers To which says either Locked Object or Locking Entity? best regards, Florian Pflug -- 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] Need help understanding pg_locks
Florian Pflug f...@phlo.org writes: On Jul11, 2011, at 17:11 , Tom Lane wrote: Yeah, I think this patch is going in the wrong direction altogether. It would be better to modify the description of virtualtransaction and pid to say that those are the locking entity. Hm, we already kinda of say that. Both descriptions include the phrase ... holding or awaiting this lock.. The column mode says ... held or desired by this process, which I guess is similar enough to make it clear that these are related. Its the columns which refer to the locked object which simply say object, and thus leave it open if that means locked or a locking. Could we split that table in two parts, one for the fields referring to the locked object and one for the locking entity, or does that depart too far from the way we document other system catalogs and views? Then you'd have to join them, which would not be an improvement from anybody's standpoint. Maybe we could just add a paragraph above the pg_locks Columns table that says explicitly that virtualtransaction and pid describe the entity holding or awaiting the lock, and the others describe the object being locked? Any way you slice it, putting this information into the per-column table is going to be repetitive. 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] Need help understanding pg_locks
Tom Lane wrote: Florian Pflug f...@phlo.org writes: On Jul11, 2011, at 05:47 , Bruce Momjian wrote: Thank you. I think my confusion is that virtualtransaction is the lock holder/waiter, and the other two are actual locks. The attached doc patch clarifies that. I had actually realized this a few weeks ago and forgot, meaning this is pretty confusing. For consistency, I guess it should say lock object instead of simply object the description of all the columns up to (and including) objsubid, not only those of virtualxid and transactionid. Yeah, I think this patch is going in the wrong direction altogether. It would be better to modify the description of virtualtransaction and pid to say that those are the locking entity. OK, so as I understand it, in pg_locks: Column | Type | Modifiers +--+--- locktype | text | database | oid | relation | oid | page | integer | tuple | smallint | virtualxid | text | transactionid | xid | classid| oid | objid | oid | objsubid | smallint | virtualtransaction | text | pid| integer | mode | text | granted| boolean | It is the last four that are related to the locking entity. I don't see a way of improving the description of the last four columns: http://developer.postgresql.org/pgdocs/postgres/view-pg-locks.html What was unclear to me was that the earlier columns (illogically) vaguely represented the locked object. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Need help understanding pg_locks
Tom Lane wrote: Florian Pflug f...@phlo.org writes: On Jul11, 2011, at 17:11 , Tom Lane wrote: Yeah, I think this patch is going in the wrong direction altogether. It would be better to modify the description of virtualtransaction and pid to say that those are the locking entity. Hm, we already kinda of say that. Both descriptions include the phrase ... holding or awaiting this lock.. The column mode says ... held or desired by this process, which I guess is similar enough to make it clear that these are related. Its the columns which refer to the locked object which simply say object, and thus leave it open if that means locked or a locking. Could we split that table in two parts, one for the fields referring to the locked object and one for the locking entity, or does that depart too far from the way we document other system catalogs and views? Then you'd have to join them, which would not be an improvement from anybody's standpoint. Maybe we could just add a paragraph above the pg_locks Columns table that says explicitly that virtualtransaction and pid describe the entity holding or awaiting the lock, and the others describe the object being locked? Any way you slice it, putting this information into the per-column table is going to be repetitive. Frankly, whenever anyone says object, they might as well call it thing. It seems to be a content-less word. Maybe just replace the word object with lock. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Need help understanding pg_locks
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Maybe we could just add a paragraph above the pg_locks Columns table that says explicitly that virtualtransaction and pid describe the entity holding or awaiting the lock, and the others describe the object being locked? Any way you slice it, putting this information into the per-column table is going to be repetitive. Frankly, whenever anyone says object, they might as well call it thing. It seems to be a content-less word. Maybe just replace the word object with lock. No, because that conflates the lock with the thing being locked. Fuzzing that semantic difference isn't going to make it less confusing. 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] Need help understanding pg_locks
On Jul11, 2011, at 17:31 , Bruce Momjian wrote: Tom Lane wrote: Florian Pflug f...@phlo.org writes: On Jul11, 2011, at 17:11 , Tom Lane wrote: Yeah, I think this patch is going in the wrong direction altogether. It would be better to modify the description of virtualtransaction and pid to say that those are the locking entity. Hm, we already kinda of say that. Both descriptions include the phrase ... holding or awaiting this lock.. The column mode says ... held or desired by this process, which I guess is similar enough to make it clear that these are related. Its the columns which refer to the locked object which simply say object, and thus leave it open if that means locked or a locking. Could we split that table in two parts, one for the fields referring to the locked object and one for the locking entity, or does that depart too far from the way we document other system catalogs and views? Then you'd have to join them, which would not be an improvement from anybody's standpoint. Maybe we could just add a paragraph above the pg_locks Columns table that says explicitly that virtualtransaction and pid describe the entity holding or awaiting the lock, and the others describe the object being locked? Any way you slice it, putting this information into the per-column table is going to be repetitive. Frankly, whenever anyone says object, they might as well call it thing. It seems to be a content-less word. Maybe just replace the word object with lock. I like that, as long as we make it .. lock is/isn't *on* a ..., and not just ... lock is/isn't a. After all, the lock very clearly isn't a relation or xid or whatever - it's a, well, lock. We'd then have OID of the database in which the lock exists, or zero if the lock is on a shared object, or null if the lock is on a transaction ID. OID of the relation, or null if the lock is not on a relation or part of a relation. ... ID of a transaction, or null if the lock is not on a transaction ID ... best regards, Florian Pflug -- 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] per-column generic option
On lör, 2011-07-09 at 23:49 -0400, Alvaro Herrera wrote: The new ALTER TABLE grammar seems a bit strange -- ADD, SET, DROP. Is this defined by the SQL/MED standard? It seems at odds with our handling of attoptions Well, I believe the SQL/MED options were actually implemented first and the attoptions afterwards. But it's probably not unwise to keep them separate, even though the syntaxes could have been made more similar. -- 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] Select For Update and Left Outer Join
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 11.07.2011 05:45, Patrick Earl wrote: The ability to lock on outer joins is quite useful. I've even been contacted to ask if I was aware of any progress in this area. 9.1 has a truly serializable isolation level, so I would suggest using that instead of SELECT FOR UPDATE. Heikki beat me to the big point, but I'll elaborate a bit. First, 9.1 is in beta testing, and will probably be released this summer Next, when using this feature be sure to use transactional annotations and set things up so that a transaction which fails with SQLSTATE 40001 is retried from the start. If you use serializable transactions consistently, you can drop all FOR UPDATE and FOR SHARE clauses, and most likely all explicit locks. (In our in-house testing I've so far found one place where we needed to take an explicit lock on a dummy table we created just to control access to a sequence -- sequences don't follow normal transactional semantics.) Third, review this section, and consider the performance tips there: http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html#XACT-SERIALIZABLE I'd be interested in hearing how it goes. -Kevin -- 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] Full GUID support
On mån, 2011-07-11 at 11:13 -0400, Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: On Sun, Jul 10, 2011 at 20:59, Josh Berkus j...@agliodbs.com wrote: Also, I think that UUIDs fall into the class of datatypes used by less than 10% of users which should always remain extensions. I'd consider CITEXT for core before UUID. UUID *is* in core. It's just the generation functions that aren't. Remind me again *why* it's in core? Seems like something that ought to be an extension. I think at the time, making something an add-on would have placed an excessive burden on potential users. The claim was that most UUIDs are generated by applications, so having the type in core would be important, but having the generation functions not so much. That said, there have been several proposals over the years to move a few things out of the core into add-ons, and now that extension support exists, we could potentially reopen that discussion. -- 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] Select For Update and Left Outer Join
On 11.07.2011 18:44, Kevin Grittner wrote: (In our in-house testing I've so far found one place where we needed to take an explicit lock on a dummy table we created just to control access to a sequence -- sequences don't follow normal transactional semantics.) Hmm, is that something we should do something about? Can you give an example of that? Not in 9.1, except in the docs if we don't mention that already, but in the future... -- 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
[HACKERS] remove README.mb.jp and README.mb.big5?
These files are last updated 2001 or 2002 and I'm pretty sure they are outdated. It looks like no one is maintaining them, so we should remove them. -- 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] Need help understanding pg_locks
Bruce Momjian br...@momjian.us wrote: OK, so as I understand it, in pg_locks: Column | Type | Modifiers +--+--- locktype | text | database | oid | relation | oid | page | integer | tuple | smallint | virtualxid | text | transactionid | xid | classid| oid | objid | oid | objsubid | smallint | virtualtransaction | text | pid| integer | mode | text | granted| boolean | It is the last four that are related to the locking entity. vaguely represented the locked object. I think more accurately: Information about the lock requester: virtualtransaction, pid Information about what is being locked: database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid (where NULL means not applicable to this lock) Information about the lock itself: locktype, mode, granted -Kevin -- 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] Select For Update and Left Outer Join
On Jul 11, 2011, at 10:44 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 11.07.2011 05:45, Patrick Earl wrote: The ability to lock on outer joins is quite useful. I've even been contacted to ask if I was aware of any progress in this area. 9.1 has a truly serializable isolation level, so I would suggest using that instead of SELECT FOR UPDATE. Heikki beat me to the big point, but I'll elaborate a bit. First, 9.1 is in beta testing, and will probably be released this summer Next, when using this feature be sure to use transactional annotations and set things up so that a transaction which fails with SQLSTATE 40001 is retried from the start. If you use serializable transactions consistently, you can drop all FOR UPDATE and FOR SHARE clauses, and most likely all explicit locks. (In our in-house testing I've so far found one place where we needed to take an explicit lock on a dummy table we created just to control access to a sequence -- sequences don't follow normal transactional semantics.) Third, review this section, and consider the performance tips there: http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html#XACT-SERIALIZABLE I'd be interested in hearing how it goes. I find these responses to be a bit off point. Not everyone can or will want to use SERIALIZABLE. The OP's point is that we - particularly Tom - have argued in the past that we shouldn't allow this because it's too ill-defined and/or confusing. Evidently our competition does not agree, and I think that's a point worth noting. ...Robert -- 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] Full GUID support
Excerpts from Peter Eisentraut's message of lun jul 11 11:48:22 -0400 2011: That said, there have been several proposals over the years to move a few things out of the core into add-ons, and now that extension support exists, we could potentially reopen that discussion. Surely we ought to find a way to distribute binaries first, at least for those platforms on which compiling stuff from source is cumbersome. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Select For Update and Left Outer Join
Robert Haas robertmh...@gmail.com writes: I find these responses to be a bit off point. Not everyone can or will want to use SERIALIZABLE. The OP's point is that we - particularly Tom - have argued in the past that we shouldn't allow this because it's too ill-defined and/or confusing. Evidently our competition does not agree, and I think that's a point worth noting. Has anyone looked into what the competition thinks the appropriate definition is, or whether they all agree on the details? 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] reducing the overhead of frequent table locks, v4
* ... It's also possible that * we're acquiring a second or third lock type on a relation we have * already locked using the fast-path, but for now we don't worry about * that case either. */ How common is that case? There are only 16 entries in the fast path lock table, so it seems like it would frequently fill up. So, if there are common code paths that acquire different weak locks on the same relation, then we might commonly miss a fast-path opportunity. One path that acquires multiple weak locks is an INSERT INTO foo SELECT ... FROM foo ... Is that common enough to worry about? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Launching debugger on self on SIGSEGV
Hi,
The attached patch registers a signal handler for SIGSEGV and launches
GDB in batch mode on its own pid so that the stack leading to the SEGV can
be dumped in the server logs. Also attached is an example of the stack
dumped by gdb in server log file (caused by a `kill -segv nnn` on the
backend).
Since this patch calls fork() inside a signal handler, I investigated a
bit and found that, per POSIX, fork() is asynch-signal-safe and hence it can
be called inside a handler.
This in itself might not be very useful because I haven't seen many
crash reports in the community, but it can be extended to dump stack on
Assert so that it helps developers and our beta testers. It can also be used
to dump stack of a process we are about to kill for deadlock reasons, and
before certain PANIC conditions too.
Right now it works only for gdb (setting the GUC to true actually check
for the presence of gdb), but it can be made generic, they way our
archive_command etc. work, so that we take a string and replace certain
parameters with binary path and pid so that any debugger can be used.
It also looks pretty easy to port it to Windows since all we really want
to do is create an external process with certain parameters, and
CreateProcess() is all we need. I haven't investigated seriously about that
but of there's interest in this patch then I can spend some time on that
too.
Regards,
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index dca5efc..04cd900 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -5049,3 +5049,34 @@ InitPostmasterDeathWatchHandle(void)
(int) GetLastError(;
#endif /* WIN32 */
}
+
+/* Fork a gdb process such that it emits my stack trace to the logs */
+static void
+print_self_stack()
+{
+ char pid_buf[30];
+ int child_pid;
+
+ sprintf(pid_buf, %d, getpid());
+ child_pid = fork();
+
+ if (child_pid == 0)
+ {
+ fprintf(stderr, stack trace for %s pid=%s\n, my_exec_path, pid_buf);
+ execlp(gdb, gdb, --batch, -n, -ex, bt, my_exec_path, pid_buf, NULL);
+ abort(); /* If gdb failed to start */
+ }
+ else
+ {
+ waitpid(child_pid,NULL,0);
+ }
+}
+
+/* SIGSEGV handler, controlled by GUC */
+void
+dump_stack(SIGNAL_ARGS)
+{
+ print_self_stack();
+
+ abort();
+}
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 5841631..7262839 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -40,6 +40,7 @@
#include libpq/auth.h
#include libpq/be-fsstubs.h
#include libpq/pqformat.h
+#include libpq/pqsignal.h
#include miscadmin.h
#include optimizer/cost.h
#include optimizer/geqo.h
@@ -167,6 +168,10 @@ static bool call_enum_check_hook(struct config_enum * conf, int *newval,
static bool check_log_destination(char **newval, void **extra, GucSource source);
static void assign_log_destination(const char *newval, void *extra);
+bool dump_stack_on_crash = false;
+static bool check_dump_stack_on_crash(bool *newval, void **extra, GucSource source);
+static void assign_dump_stack_on_crash(bool newval, void *extra);
+
#ifdef HAVE_SYSLOG
static int syslog_facility = LOG_LOCAL0;
#else
@@ -1422,6 +1427,17 @@ static struct config_bool ConfigureNamesBool[] =
NULL, NULL, NULL
},
+ {
+ {dump_stack_on_crash, PGC_USERSET, ERROR_HANDLING_OPTIONS,
+ gettext_noop(Use GDB to dump the stack of a crashing backend process.),
+ gettext_noop(This requires that GDB be already installed and accessible to Postgres.),
+ },
+ quote_all_identifiers,
+ false,
+ check_dump_stack_on_crash, assign_dump_stack_on_crash, NULL
+ },
+
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL
@@ -8672,4 +8688,35 @@ show_log_file_mode(void)
return buf;
}
+static bool
+check_dump_stack_on_crash(bool *newval, void **extra, GucSource source)
+{
+ /* TODO: Check if GDB is available. If not, then complain and return false */
+
+ char gdb_path[MAXPGPATH];
+
+ if (*newval == false)
+ return true;
+
+ Assert(*newval == true);
+
+ if (find_my_exec(gdb, gdb_path) 0)
+ {
+ elog(WARNING, Could not locate gdb.);
+
+ return false;
+ }
+ else
+ return true;
+}
+
+static void
+assign_dump_stack_on_crash(const bool newval, void *extra)
+{
+ if (newval)
+ pqsignal(SIGSEGV, dump_stack);
+ else
+ pqsignal(SIGSEGV, SIG_DFL);
+}
+
#include guc-file.c
diff --git a/src/include/postmaster/postmaster.h b/src/include/postmaster/postmaster.h
index be4f8a7..f624d51 100644
--- a/src/include/postmaster/postmaster.h
+++ b/src/include/postmaster/postmaster.h
@@ -55,6 +55,8 @@ extern int SubPostmasterMain(int argc, char *argv[]);
extern Size ShmemBackendArraySize(void);
extern void ShmemBackendArrayAllocation(void);
+extern void dump_stack(SIGNAL_ARGS);
+
#endif
#endif /* _POSTMASTER_H */
LOG: database system is ready to accept connections
stack trace for
Re: [HACKERS] pg_xlog error
On 11.07.2011 17:33, jcamera wrote: Hi, I have problems in my database. I think it is corrupted. Folow my log when I tried to start it standalone. I have some questions: 1. I saw that the error is in base/30518/449778670_vm file. Can I rebuild this file or somethink like this? *_vm files contain the visibility maps of each relation. Visibility maps don't contain any user data, it's just bookkeeping information for vacuum to skip parts of tables that don't need vacuuming. They can be safely removed, the next vacuum will just take somewhat longer than otherwise. But clearly that error is an indication of corruption, and the visibility map probably was not the only thing that got corrupted. It looks like you're missing some WAL files, or they got corrupted. It's likely that you need to restore from a backup. And you should try to figure out what caused the corruption in the first place. Broken hardware, perhaps, or fsync=off and a power outage. 2. In the last line of log, we can see DEBUG: shmem_exit(1): 8 callbacks to make. Where can I find these transactions to do callback and/or how can I do these callbacks? That's just an internal debugging line you get when the startup process ends. You can ignore it. -- 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] Select For Update and Left Outer Join
Robert Haas robertmh...@gmail.com wrote: I find these responses to be a bit off point. The OP is basically looking for what Florian tried to implement. This is perhaps a *bit* off point, but arguably not more than pointing someone who is requesting planner hints in another direction. And someone thought the issues were related here: http://archives.postgresql.org/pgsql-hackers/2010-12/msg01792.php ;-) Not everyone can or will want to use SERIALIZABLE. No argument on that. It's just that it is the only feature we have now (or soon) which solves the problem short of a table lock. The OP's point is that we - particularly Tom - have argued in the past that we shouldn't allow this because it's too ill-defined and/or confusing. And I have argued that what Florian wanted would be a valuable addition. The approach foundered on technical details, although in re-reading the thread I'm wondering if it wouldn't make sense to dodge all that by having SELECT FOR UPDATE simple *do* a no-op UPDATE RETURNING. This would cause behavior matching Oracle and MS SQL Server (when the latter is using MVCC without S2PL). DB2 is more strict, acquiring a predicate lock over the selected range, but we can't be compatible with both behaviors at the same time. Evidently our competition does not agree Neither on this nor on planner hints. ;-) -Kevin -- 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] Launching debugger on self on SIGSEGV
Gurjeet Singh singh.gurj...@gmail.com writes: The attached patch registers a signal handler for SIGSEGV and launches GDB in batch mode on its own pid so that the stack leading to the SEGV can be dumped in the server logs. Did you not read the thread last week about how we did not want any such thing? Quite aside from any postgres-specific reasons not to have any added delay in the signal-to-database-shutdown path, this patch makes a bunch of untenable assumptions about whether or where gdb is installed, whether there are usable debug symbols available, whether gdb's output will go somewhere useful, etc etc. And on top of all that, it adds *no functionality whatsoever* compared to a post-mortem gdb run on the core file. 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] Select For Update and Left Outer Join
Kevin Grittner kevin.gritt...@wicourts.gov wrote: I'm wondering if it wouldn't make sense to dodge all that by having SELECT FOR UPDATE simple *do* a no-op UPDATE RETURNING. Hmm. Patrick, would it be possible to change the PostgreSQL code for Hibernate to use UPDATE RETURNING instead of SELECT FOR UPDATE? That might allow portable Hibernate applications to work properly with recent PostgreSQL versions without going to SERIALIZABLE transactions. -Kevin -- 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] Full GUID support
On 07/10/2011 11:59 AM, Josh Berkus wrote: On 7/3/11 2:02 PM, Tom Lane wrote: Yeah. If there were One True Way to create a UUID, I would probably agree that we should push that functionality into core. But there are a lot of ways (and the reason for that is that they all suck in one fashion or another :-(). Between that and the lack of portability of many of the better ways, this is something I'm happy to keep at arm's length. Also, I think that UUIDs fall into the class of datatypes used by less than 10% of users which should always remain extensions. I'd consider CITEXT for core before UUID. Uh UUID/GUID is used pervasively throughout enterprise apps, especially Java apps. -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- 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] Launching debugger on self on SIGSEGV
On Mon, Jul 11, 2011 at 12:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Gurjeet Singh singh.gurj...@gmail.com writes: The attached patch registers a signal handler for SIGSEGV and launches GDB in batch mode on its own pid so that the stack leading to the SEGV can be dumped in the server logs. Did you not read the thread last week about how we did not want any such thing? Unfortunately, I did not. I'll catch up on it. Quite aside from any postgres-specific reasons not to have any added delay in the signal-to-database-shutdown path, this patch makes a bunch of untenable assumptions about whether or where gdb is installed, whether there are usable debug symbols available, whether gdb's output will go somewhere useful, etc etc. And on top of all that, it adds *no functionality whatsoever* compared to a post-mortem gdb run on the core file. I agree that it makes a bunch of assumptions, that's why I proposed that we make it user configurable parameter, like archive_command, so that users (or their packagers) can provide the command and all the relevant options. Regards, -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] Full GUID support
I'd have to agree on the importance of UUID support. It's pretty much essential for any sort of disconnected sync model. We use UUIDs (generated with the guid.comb technique) for our surrogate keys in around 50 apps, and it has served us well. We have also been seriously missing the 64-bit generator functionality. I've been watching the threads for half a year to see when it will pop up again. It's been a long wait. Regarding UUID generation, IMHO, the random approach is the standard at this point. That'd be v4 in the oisp library. It would be handy to be able to generate these without having to load in special extensions. It's not the biggest deal though since we can run initialization code to get the database set up... just more effort. Patrick Earl On Mon, Jul 11, 2011 at 11:19 AM, Joshua D. Drake j...@commandprompt.com wrote: Uh UUID/GUID is used pervasively throughout enterprise apps, especially Java apps. -- 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] Cascade replication
On Mon, Jul 11, 2011 at 10:26 AM, Fujii Masao masao.fu...@gmail.com wrote: On Mon, Jul 11, 2011 at 3:30 AM, Josh Berkus j...@agliodbs.com wrote: Do you think you'll submit a new version of the patch this commitfest? Yes. I'm now updating the patch according to Simon's comments. I will submit it today. Attached is the updated version which addresses all the issues raised by Simon. The risk you describe already exists in current code. I regard it as a non-risk. The unlink() and the rename() are executed consecutively, so the gap between them is small, so the chance of a SIGKILL in that gap at the same time as losing the archive seems low, and we can always get that file from the master again if we are streaming. Any code you add to fix this will get executed so rarely it probably won't work when we need it to. In the current scheme we restart archiving from the last restartpoint, which exists only on the archive. This new patch improves upon this by keeping the most recent files locally, so we are less expose in the case of archive unavailability. So this patch already improves things and we don't need any more than that. No extra code please, IMHO. Yes, I added no extra code for the risk I raised upthread. In #2, there is another problem; walsender might have the pre-existing file open, so the startup process would need to request walsenders to close the file before removing (or renaming) it, wait for new file to appear and open it again. I implemented this. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *** *** 1949,1954 SET ENABLE_SEQSCAN TO OFF; --- 1949,1956 The values of these parameters on standby servers are irrelevant, although you may wish to set them there in preparation for the possibility of a standby becoming the master. + Some of them need to be set in the standby for cascade replication + (see xref linkend=cascade-replication). /para variablelist *** *** 2019,2025 SET ENABLE_SEQSCAN TO OFF; doesn't keep any extra segments for standby purposes, so the number of old WAL segments available to standby servers is a function of the location of the previous checkpoint and status of WAL ! archiving. This parameter has no effect on restartpoints. This parameter can only be set in the filenamepostgresql.conf/ file or on the server command line. /para --- 2021,2027 doesn't keep any extra segments for standby purposes, so the number of old WAL segments available to standby servers is a function of the location of the previous checkpoint and status of WAL ! archiving. This parameter can only be set in the filenamepostgresql.conf/ file or on the server command line. /para *** *** 2121,2127 SET ENABLE_SEQSCAN TO OFF; synchronous replication is enabled, individual transactions can be configured not to wait for replication by setting the xref linkend=guc-synchronous-commit parameter to ! literallocal/ or literaloff/. /para para This parameter can only be set in the filenamepostgresql.conf/ --- 2123,2130 synchronous replication is enabled, individual transactions can be configured not to wait for replication by setting the xref linkend=guc-synchronous-commit parameter to ! literallocal/ or literaloff/. This parameter has no effect on ! cascade replication. /para para This parameter can only be set in the filenamepostgresql.conf/ *** a/doc/src/sgml/high-availability.sgml --- b/doc/src/sgml/high-availability.sgml *** *** 877,884 primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' --- 877,921 network delay, or that the standby is under heavy load. /para /sect3 + /sect2 + + sect2 id=cascade-replication +titleCascade Replication/title +indexterm zone=high-availability + primaryCascade Replication/primary +/indexterm +para + Cascade replication feature allows the standby to accept the replication + connections and stream WAL records to another standbys. This is useful + for reducing the number of standbys connecting to the master and reducing + the overhead of the master, when you have many standbys. +/para +para + The cascading standby sends not only WAL records received from the + master but also those restored from the archive. So even if the replication + connection in higher level is terminated, you can continue cascade replication. +/para +para + Cascade replication is asynchronous. Note that synchronous replication + (see xref
Re: [HACKERS] Select For Update and Left Outer Join
On Jul11, 2011, at 18:55 , Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: I find these responses to be a bit off point. The OP is basically looking for what Florian tried to implement. This is perhaps a *bit* off point, but arguably not more than pointing someone who is requesting planner hints in another direction. And someone thought the issues were related here: http://archives.postgresql.org/pgsql-hackers/2010-12/msg01792.php Hm, I'm not so sure we're looking for the same thing here. It seems to me that what the OP (or actually Hibernate) wants are two related, but different, things. (A) First, for a way to UPDATE a row that was returned by a previous SELECT, without the need to know a set of fields which comprise a candidate or primary key. So far, this has nothing to do with locking, and everything with to do with convenience. The postgres way of doing that is including the ctid of all to-be-updated relations in the SELECT's target lest, and using UPDATE ... WHERE ctid = ctid from select. Not sure how that behaves if the row has been updated after the SELECT but before the UPDATE, though... (B) Secondly (but I don't know if this is even Hibernate's intention, I'm no Hibernate expert), it might that Hibernate is trying to get true serializability by doing S2PL, i.e. taking a lock on every row it reads. That seems like a rather unwise thing to do on postgres, because of the way we handle row locks. Part (B) has some relationship to what I tried to archive by changing the way REPEATABLE READ transactions and row locks interact. Though my intention wasn't full serializability, only enough protection to make user-space FOREIGN KEYS work safely for REPEATABLE READ transactions. @OP, could you explain whether it is (A) or (B) or both that Hibernate tries to archive with FOR UPDATE. The OP's point is that we - particularly Tom - have argued in the past that we shouldn't allow this because it's too ill-defined and/or confusing. And I have argued that what Florian wanted would be a valuable addition. The approach foundered on technical details, although in re-reading the thread I'm wondering if it wouldn't make sense to dodge all that by having SELECT FOR UPDATE simple *do* a no-op UPDATE RETURNING. This would cause behavior matching Oracle and MS SQL Server (when the latter is using MVCC without S2PL). DB2 is more strict, acquiring a predicate lock over the selected range, but we can't be compatible with both behaviors at the same time. That'd make FOR UPDATE much more expensive than it is today. As it stands, FOR UPDATE causes the page containing to tuple to be dirtied, but it doesn't require new index entries and generate no new tuple version which must be cleaned up by VACUUM. (To be fair, however, HOT would reduce the impact somewhat, but still...). BTW, the technical issues that prevented my patch from working correctly are tightly related to the issues that plague the combination of sub-transactions and row locks. Namely that UPDATE overwrites the information about previous lock holders, and the information stays gone even if the UPDATE later rolls back. But so far, how to fix that evades me, at least without major hacks or changes to the on-disk format. best regards, Florian Pflug -- 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] Select For Update and Left Outer Join
Florian Pflug f...@phlo.org wrote: Part (B) has some relationship to what I tried to archive by changing the way REPEATABLE READ transactions and row locks interact. Though my intention wasn't full serializability, only enough protection to make user-space FOREIGN KEYS work safely for REPEATABLE READ transactions. Florian, I know that you looked at Oracle's treatment of SELECT FOR UPDATE, so could you respond to Tom's question about the semantics of that? (From what you and Patrick have posted I gather that from a user visible logical perspective SELECT FOR UPDATE is the same as a no-op UPDATE RETURNING, although there may be performance differences. From Patrick's recent post I gather that MS SQL Server [at least in some configuration -- it has many settings which might affect this] behaves the same as Oracle in this regard; while DB2 is more strict, using a predicate lock on the selected range. But my take on that is second-hand, based on those posts and discussions with Oracle users a PGEast -- it'd be better for a report from someone who looked at it directly.) -Kevin -- 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] Select For Update and Left Outer Join
On Jul11, 2011, at 20:16 , Kevin Grittner wrote: Florian Pflug f...@phlo.org wrote: Part (B) has some relationship to what I tried to archive by changing the way REPEATABLE READ transactions and row locks interact. Though my intention wasn't full serializability, only enough protection to make user-space FOREIGN KEYS work safely for REPEATABLE READ transactions. Florian, I know that you looked at Oracle's treatment of SELECT FOR UPDATE, so could you respond to Tom's question about the semantics of that? (From what you and Patrick have posted I gather that from a user visible logical perspective SELECT FOR UPDATE is the same as a no-op UPDATE RETURNING, although there may be performance differences. (CC'ing Tom now, hope thats OK) I can only comment with certainty on the behaviour of FOR UPDATE regarding serialization conflicts. There, Oracle treats FOR UPDATE exactly like UPDATE, i.e. UPDATE raises a serialization error if it encounters a row locked FOR UPDATE by a transaction invisible to the UPDATEing one. What Tom wanted to know, I believe, was whether FOR UPDATE locks only existing *rows* (i.e., locks nothing in case of a LEFT JOIN without a matching right row), or whether it actually locks the *fact* that no such row exists (i.e., prevents future inserts of matching rows). Now, I cannot comment on that with absolute certainty, and currently don't have an Oracle instance available to test, but I can say so much: I'd very *very*, *very* surprised if they did anything other than simply locking nothing in the case of a LEFT join without a matching right row. As far as I'm aware, Oracle simply doesn't do predicate locking, and doesn't do true serializability. Their SERIALIZABLE mode is actually snapshot isolation, just like ours used to be. It'd be very strange to do yet, but yet to do predicate locking when it comes to SELECT FOR UPDATE. From Patrick's recent post I gather that MS SQL Server [at least in some configuration -- it has many settings which might affect this] Yeah MS-SQL really isn't the idea target for comparison here. You can override pretty much any lock that MS-SQL takes with a stronger or weaker one from what I've seen. I wouldn't be at all surprised if you could convince it to work either way by putting some (probably rather obscure) incantations into your SQL statements. best regards, Florian Pflug -- 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] Select For Update and Left Outer Join
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 11.07.2011 18:44, Kevin Grittner wrote: (In our in-house testing I've so far found one place where we needed to take an explicit lock on a dummy table we created just to control access to a sequence -- sequences don't follow normal transactional semantics.) Hmm, is that something we should do something about? Can you give an example of that? Sequences behave in a non-transactional way for good reason; I certainly wouldn't advocate changing that. This came up in some fairly specialized code in our replication system. Existing PostgreSQL features were fine for dealing with it, although the ability to take out a lock on a sequence (just as one would on a table) would have been convenient (as it would have allowed us to avoid using a dummy table). The more important issue is probably around the docs making clear that the serializable transaction isolation level doesn't make certain things beyond DML serializable. Not in 9.1, except in the docs if we don't mention that already, but in the future... I thought it was mentioned in the docs, but in a scan through the Concurrency Control chapter I'm not seeing it. Basically, SSI is only going to work with objects and statements which are currently using MVCC snapshots for snapshot isolation; anything else behaves exactly as it did in 9.0 at the serializable isolation level. I'm not quite sure where this should be mentioned. Ideas? -Kevin -- 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] Select For Update and Left Outer Join
On Jul 11, 2011, at 11:55 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: I find these responses to be a bit off point. The OP is basically looking for what Florian tried to implement. This is perhaps a *bit* off point, but arguably not more than pointing someone who is requesting planner hints in another direction. And someone thought the issues were related here: http://archives.postgresql.org/pgsql-hackers/2010-12/msg01792.php ;-) Well, fair enough. I thought of the connection between this request and Florian's work, too. I would very much like to support what he proposed, but it doesn't appear viable without a heapam rewrite, or maybe a lock manager rewrite. However, I think that's a somewhat separate question from whether we need to forbid SFU on the outer side of a join. Tom's question seems to me to be right on target: what semantics do our competitors assign to this construct? And do they broadly agree with each other? Evidently our competition does not agree Neither on this nor on planner hints. ;-) Well, we are a pretty smart group of people. But I don't think we should completely ignore what other people are doing, on any topic. ...Robert -- 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] Select For Update and Left Outer Join
Florian Pflug f...@phlo.org wrote: Yeah MS-SQL really isn't the idea target for comparison here. You can override pretty much any lock that MS-SQL takes with a stronger or weaker one from what I've seen. I wouldn't be at all surprised if you could convince it to work either way by putting some (probably rather obscure) incantations into your SQL statements. I was thinking of some of the ALTER DATABASE SET options, like COMPATIBILITY_LEVEL or ALLOW_SNAPSHOT_ISOLATION, but you have a point about what overrides can be used at the statement level, too. -Kevin -- 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] [GENERAL] Creating temp tables inside read only transactions
On Mon, Jul 11, 2011 at 10:12 AM, Florian Pflug f...@phlo.org wrote:
On Jul11, 2011, at 07:08 , Darren Duncan wrote:
Christopher Browne wrote:
Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1,
there's a pretty good reason NOT to support that, namely that this
breaks relational handling of tables. PostgreSQL is a *relational*
database system, hence it's preferable for structures to be
relational, as opposed to hierarchical, which is what any of the
suggested nestings are.
Rather, the argument is that it was intentional for the structuring of
table naming to, itself, be relational, and changing that definitely has
some undesirable characteristics.
The need for recursive queries is the most obvious undesirable, but it's
not the only undesirable thing, by any means.
I do not see how recursive queries (really iteration of records) even enters
the picture...
Right now I can emulate a hierarchical schema structure via a naming scheme
- for example schemabase_sub1_sub2_etc. I am simply looking for a formal
way to do the above AND also tell the system that I want all schemas under
schemabase to be in the search path. Heck, I guess just allowing for
simply pattern matching in search_path would be useful in this case
regardless of the presence of an actual schema hierarchy. Using LIKE
syntax say: SET search_path TO schemabase_sub1_% or something similar.
The only missing ability becomes a way for graphical tools to represent the
schema hierarchy using a tree-structure with multiple depths.
I can see how adding . and .. and relative paths would confuse the issue
those are not necessary features of a multi-level schema depth.
The above, combined with a different separator for intra-level
namespace/schema delineation, would allow for an unambiguous way to define
and use a hierarchical schema with seemingly minimal invasion into the
current way of doing things. You could almost implement it just by requiring
a specific character to act as the separator and then construct the actual
schema using single-level literals and supporting functions that can convert
them into an hierarchy. In other words, the schema table would still only
contain one field with the full parent!child as opposed to (schema,
parent) with (VALUES('parent',null),('child','parent')).
In other words, if we use ! as the separator, any schema named
parent!child could be stored and referenced as such but then if you run a
getChildren(parent) function it would return child along with any other
schemas of the form parent!%. In this case the % sign could maybe only
match everything except ! and the * symbol could be used to match ! as
well.
I could give more examples but I hope the basic idea is obvious. The main
thing is that the namespace hierarchy usage is standardized in such a way
that pgAdmin and other GUI tools can reliably use for display purposes and
that search_path can be constructed in a more compact format so that every
schema and sub-schema is still absolutely referenced (you can even have the
SET command resolve search_path at execution time and then remain static
just like CREATE VIEW SELECT * FROM table.
David J.
--
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] [GENERAL] Creating temp tables inside read only transactions
I will put my support for David Johnston's proposal, in principle, though minor
details of syntax could be changed if using ! conflicts with something. --
Darren Duncan
David Johnston wrote:
On Mon, Jul 11, 2011 at 10:12 AM, Florian Pflug f...@phlo.org wrote:
On Jul11, 2011, at 07:08 , Darren Duncan wrote:
Christopher Browne wrote:
Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1,
there's a pretty good reason NOT to support that, namely that this
breaks relational handling of tables. PostgreSQL is a *relational*
database system, hence it's preferable for structures to be
relational, as opposed to hierarchical, which is what any of the
suggested nestings are.
Rather, the argument is that it was intentional for the structuring of
table naming to, itself, be relational, and changing that definitely has
some undesirable characteristics.
The need for recursive queries is the most obvious undesirable, but it's
not the only undesirable thing, by any means.
I do not see how recursive queries (really iteration of records) even enters
the picture...
Right now I can emulate a hierarchical schema structure via a naming scheme
- for example schemabase_sub1_sub2_etc. I am simply looking for a formal
way to do the above AND also tell the system that I want all schemas under
schemabase to be in the search path. Heck, I guess just allowing for
simply pattern matching in search_path would be useful in this case
regardless of the presence of an actual schema hierarchy. Using LIKE
syntax say: SET search_path TO schemabase_sub1_% or something similar.
The only missing ability becomes a way for graphical tools to represent the
schema hierarchy using a tree-structure with multiple depths.
I can see how adding . and .. and relative paths would confuse the issue
those are not necessary features of a multi-level schema depth.
The above, combined with a different separator for intra-level
namespace/schema delineation, would allow for an unambiguous way to define
and use a hierarchical schema with seemingly minimal invasion into the
current way of doing things. You could almost implement it just by requiring
a specific character to act as the separator and then construct the actual
schema using single-level literals and supporting functions that can convert
them into an hierarchy. In other words, the schema table would still only
contain one field with the full parent!child as opposed to (schema,
parent) with (VALUES('parent',null),('child','parent')).
In other words, if we use ! as the separator, any schema named
parent!child could be stored and referenced as such but then if you run a
getChildren(parent) function it would return child along with any other
schemas of the form parent!%. In this case the % sign could maybe only
match everything except ! and the * symbol could be used to match ! as
well.
I could give more examples but I hope the basic idea is obvious. The main
thing is that the namespace hierarchy usage is standardized in such a way
that pgAdmin and other GUI tools can reliably use for display purposes and
that search_path can be constructed in a more compact format so that every
schema and sub-schema is still absolutely referenced (you can even have the
SET command resolve search_path at execution time and then remain static
just like CREATE VIEW SELECT * FROM table.
David J.
--
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] txid_current() forces a real xid
On Mon, Jul 11, 2011 at 5:59 PM, Bruce Momjian br...@momjian.us wrote: Right now, calling txid_current() causes a session to create a non-virtual xid if not already assigned, so observing the xid creates it, which seems kind of odd. Is that intended? Here is the C code: Yes, it was intentional, the value will be written out. It could be even called before actual writing statement is run so returning anything that will become invalid later during transaction is dangerous. If you have use-case that requires frequent calling of that function in read-only transaction, and prefer to see virtual txids I suggest implementing it as new function. -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TODO list updated
I have updated the TODO wiki to remove the 9.1-completed items: http://wiki.postgresql.org/wiki/Todo This will allow us to now mark 9.2-completed items. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] marking old branches as no longer maintained
Andrew Dunstan wrote:
On 06/28/2011 05:31 PM, Peter Eisentraut wrote:
On tis, 2011-06-28 at 17:05 -0400, Andrew Dunstan wrote:
Couldn't you just put a text file on the build farm server with
recommended branches?
As I told Magnus, that gets ugly because of limitations in MinGW's SDK
perl. I suppose I could just not implement the feature for MinGW, but
I've tried damn hard not to make those sorts of compromises and I'm not
keen to start.
The buildfarm code can upload the build result via HTTP; why can't it
download a file via HTTP?
It has to use a separate script to do that. I don't really want to add
another one just for this.
(thinks a bit) I suppose I can make it do:
my $url = http://buildfarm.postgresql.org/branches_of_interest.txt;;
my $branches_of_interest = `perl -MLWP::Simple -e getprint(q{$url})`;
Maybe that's the best option. It's certainly going to be less code than
anything else :-)
Could you pull the list of active branches from our web site HTML?
\
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] marking old branches as no longer maintained
On 07/11/2011 07:59 PM, Bruce Momjian wrote:
Andrew Dunstan wrote:
On 06/28/2011 05:31 PM, Peter Eisentraut wrote:
On tis, 2011-06-28 at 17:05 -0400, Andrew Dunstan wrote:
Couldn't you just put a text file on the build farm server with
recommended branches?
As I told Magnus, that gets ugly because of limitations in MinGW's SDK
perl. I suppose I could just not implement the feature for MinGW, but
I've tried damn hard not to make those sorts of compromises and I'm not
keen to start.
The buildfarm code can upload the build result via HTTP; why can't it
download a file via HTTP?
It has to use a separate script to do that. I don't really want to add
another one just for this.
(thinks a bit) I suppose I can make it do:
my $url = http://buildfarm.postgresql.org/branches_of_interest.txt;;
my $branches_of_interest = `perl -MLWP::Simple -e getprint(q{$url})`;
Maybe that's the best option. It's certainly going to be less code than
anything else :-)
Could you pull the list of active branches from our web site HTML?
I can, but I'm not that keen on having to do web scraping. Currently my
test machine (crake) is using the above scheme and it's working fine.
It's not a huge burden to maintain, after all.
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] txid_current() forces a real xid
Marko Kreen wrote: On Mon, Jul 11, 2011 at 5:59 PM, Bruce Momjian br...@momjian.us wrote: Right now, calling txid_current() causes a session to create a non-virtual xid if not already assigned, so observing the xid creates it, which seems kind of odd. ?Is that intended? ?Here is the C code: Yes, it was intentional, the value will be written out. It could be even called before actual writing statement is run so returning anything that will become invalid later during transaction is dangerous. If you have use-case that requires frequent calling of that function in read-only transaction, and prefer to see virtual txids I suggest implementing it as new function. No, I just considered it strange that it assigned a permenant xid by asking for the value. I have added a C comment documenting this behavior. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] ToDo: list of active channels
Pavel Stehule wrote: Hello I use a LISTEN/NOTIFY. Now I have to check, if second application that creates channels is active. It should be simple with system view of active channels. I think you want pg_listening_channels(). -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [GENERAL] Creating temp tables inside read only transactions
On Mon, Jul 11, 2011 at 12:49 PM, David Johnston pol...@yahoo.com wrote:
I do not see how recursive queries (really iteration of records) even enters
the picture...
I agree, FWIW. If the feature was that desirable, we could look at
questions of implementation to make recursion either unnecessary or at
least well managed.
Right now I can emulate a hierarchical schema structure via a naming scheme
- for example schemabase_sub1_sub2_etc. I am simply looking for a formal
way to do the above AND also tell the system that I want all schemas under
schemabase to be in the search path. Heck, I guess just allowing for
simply pattern matching in search_path would be useful in this case
regardless of the presence of an actual schema hierarchy. Using LIKE
syntax say: SET search_path TO schemabase_sub1_% or something similar.
The only missing ability becomes a way for graphical tools to represent the
schema hierarchy using a tree-structure with multiple depths.
Right. Semantically myapp_schemaname_subschemaname is no less
hierarchical than myapp.schemaname.subschemaname. The larger issue is
that of potential ambiguity wrt cross-database references (I don't
have a lot of experience reading the SQL standards, but seeing how
different db's implement cross-db references suggests that the
standards contemplate semantic meaning to depth of the namespace).
I can see how adding . and .. and relative paths would confuse the issue
those are not necessary features of a multi-level schema depth.
The above, combined with a different separator for intra-level
namespace/schema delineation, would allow for an unambiguous way to define
and use a hierarchical schema with seemingly minimal invasion into the
current way of doing things. You could almost implement it just by requiring
a specific character to act as the separator and then construct the actual
schema using single-level literals and supporting functions that can convert
them into an hierarchy. In other words, the schema table would still only
contain one field with the full parent!child as opposed to (schema,
parent) with (VALUES('parent',null),('child','parent')).
In other words, if we use ! as the separator, any schema named
parent!child could be stored and referenced as such but then if you run a
getChildren(parent) function it would return child along with any other
schemas of the form parent!%. In this case the % sign could maybe only
match everything except ! and the * symbol could be used to match ! as
well.
Agreed that this would be helpful. I would personally have a lot of
use for this sort of feature, particularly with managing large numbers
of stored procedures. Right now I am using a double underscore which
is error-prone.
Best Wishes,
Chris Travers
--
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] [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID
Simon Riggs wrote: On Sun, Jul 3, 2011 at 7:51 PM, Peter Eisentraut pete...@gmx.net wrote: On tor, 2011-06-30 at 15:09 -0400, Alvaro Herrera wrote: Robert Hass (whose name I misspelled in the commit message above) just mentioned to me (in an answer to my apologizing about it) that he didn't think that mentioning sponsors for patch development was a good idea. I don't think we have a policy for this, but I have done it for some time now and nobody has complained, so I sort of assumed it was okay. Besides, some of the people pouring the money in does care about it; moreover, it provides a little incentive for other companies that might also be in a position to fund development but lack the peer approval of the idea, or a final little push. I think commit messages should be restricted to describing what was changed and who is responsible for it. ?Once we open it for things like sponsorship, what's to stop people from adding personal messages, what they had for breakfast, currently listening to, or just selling advertising space in each commit message for 99 cents? Agreed. We should credit people somewhere, but not here. Otherwise, we'll be forced to add Sponsored by RedHat, Sponsored by 2ndQuadrant etc onto commit messages. Agreed. On one level I like the sponsor message, but on the other having Sponsored by RedHat on every Tom Lane item will get tiring. ;-) Can we add text if the employer is _not_ the feature sponsor? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] relpersistence and temp table
Robert Haas wrote: On Fri, Jul 1, 2011 at 10:32 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Jul 1, 2011 at 8:06 AM, Amit Khandekar amit.khande...@enterprisedb.com wrote: In 9.1, if a table is created using an explicit pg_temp qualification, the pg_class.relpersistence is marked 'p', not 't'. That's a bug. ?Thanks for the report. OK, so I think the problem here is that, in 9.0, it was possible to figure out what value relistemp should take at a very late date, because it was entirely a function of the schema name. A temporary schema implies relistemp = true, while a non-temporary schema implies relistemp = false. However, in 9.1, that clearly won't do, since unlogged and permanent tables can share the same schema. Moreover, by the time we get as far as RelationBuildLocalRelation(), we've already made lots of other decisions based on relpersistence, so it seems that we need to make this correct as early as possible. It's not feasible to do that in the parser, because the creation namespace could also come from search_path: SET search_path = pg_temp; CREATE TABLE foo (a int); So it seems we can't fix this any earlier than RangeVarGetCreationNamespace(). In the attached patch, I took basically that approach, but created a new function RangeVarAdjustRelationPersistence() that does the actual adjusting (since de-constifying RangeVarGetCreationNamespace() didn't seem smart), plus adds a bunch of additional sanity-checking that I previously overlooked. Namely, it forbids: - creating unlogged tables in temporary schemas - creating relations in temporary schemas of other sessions On the other hand, it does allow CREATE TEMP TABLE pg_temp.foo(a int), which was somewhat pointlessly forbidden by previous releases. In short, the code now checks directly what it used to check by inference: that you're not creating a temporary table in a permanent schema, or the other way around. I also rearranged a few other bits of code to make sure that the appropriate fixups happen BEFORE we enforce the condition that temporary tables mustn't be created in security-restricted contexts. Does this affect tables created during 9.1 beta? I assume a server restart fixes all this, but I am just checking. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Deriving release notes from git commit messages
Tom Lane wrote: Martijn van Oosterhout klep...@svana.org writes: On Sat, Jul 02, 2011 at 03:45:03PM -0400, Robert Haas wrote: There are git notes which you can attach to a commit after the fact... I like the fact that they would keep the information in the repository (where they seem to belong). Yeah, but I think it's still basically append-only, which is kind of a nuisance, and it means they can only be updated by committers, which is not particularly helpful from my point of view. The documentation says: This command allows you to add/remove notes to/from objects, without changing the objects themselves. So it doesn't appear append only. I think the idea is that every object can have one note. How that works with versioning I have no idea. A look at the git-notes man page says that you can only have one note per commit, but you can edit that note, and git does track the revision history of each note. I think that we should adopt git notes as a better solution than making dummy whitespace changes when we want to put a commit-message correction into the commit history (you listening, Bruce?). Yes, I heard. I don't think I have done that since we moved to git. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] relpersistence and temp table
On Jul 11, 2011, at 8:55 PM, Bruce Momjian br...@momjian.us wrote: Does this affect tables created during 9.1 beta? I assume a server restart fixes all this, but I am just checking. Yes, I think a server restart will fix it, though there might be corner cases I'm not thinking of. ...Robert -- 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] per-column generic option
(2011/07/11 10:21), Robert Haas wrote: On Jul 9, 2011, at 10:49 PM, Alvaro Herreraalvhe...@commandprompt.com wrote: In short: in my opinion, attoptions and attfdwoptions need to be one thing and the same. I feel the opposite. In particular, what happens when a future release of PostgreSQL adds an attoption that happens to have the same name as somebody's per-column FDW option? Something breaks, that's what... Another point: We don't commingle these concepts at the table level. It doesn't make sense to have table reloptions separate from table FDW options but then go and make the opposite decision at the column level. I'm afraid that I've misunderstood the discussion. Do you mean that per-table options should be stored in reloptions, but per-column should be separated from attoptions? (I think I've misread...) Could you tell me little more detail why it doesn't make sense to have table reloptions separate from table FDW options? 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
