Re: [HACKERS] [PATCH] Docs: Make notes on sequences and rollback more obvious
On 08/21/2012 11:18 PM, Robert Haas wrote: On Mon, Aug 20, 2012 at 4:45 AM, Craig Ringer ring...@ringerc.id.au wrote: Trying again with the attachments; the archiver only seemed to see the first patch despite all three being attached. Including patches inline; if you want 'em prettier, see: https://github.com/ringerc/postgres/tree/sequence_documentation_fixes Subject: [PATCH 1/3] Make sure you can't read through mvcc.sgml without realising that not everything is MVCC. The first of these three patches looks good to me, so I committed it. I am not convinced that the others are ready to go in. AFAICS, there hasn't been any discussion of whether a list of non-transactional features would be a useful thing to have, or if so where it should be located in the docs and what should go into it. I'm not necessarily opposed to adding something, but I think it needs some actual discussion before we commit anything. Fine by me; I just thought a concrete proposed change might get people talking about it better than my doing some more broad hand-waving on the topic. Anyone? Should we add a section that lists exceptions to normal transactional behaviour in one place, so instead of having to say SEQUENCEs and some other features or various types, functions and features there's something *concrete* to point to when discussing transactional oddities? + + sect1 id=mvcc-exceptions +titleExceptions to normal transactional rules/title + +para + Some PostgreSQL features, functions and data types differ from the + usual transactional behaviour described in this chapter. Differences + are generally mentioned in the documentation sections for the + features they affect. Such exceptions are collected here for + easy reference. +/para + +para + The following actions and features don't follow the typical + transactional rules: +/para + +itemizedlist + listitem + para + Serial pseudo-types xref linkend=datatype-serial + /para + /listitem + listitem + para + literalSEQUENCE/literals - xref linkend=functions-sequence + /para + /listitem + listitem + para + Advisory locks - xref linkend=advisory-locks + /para + /listitem + listitem + para + Disk writes to files outside the database, as performed by + literalCOPY ... TO/literal, adminpack functions, and other add-ons. + See xref linkend=sql-copy, xref linkend=adminpack. + /para + /listitem + listitem + para + Any network I/O or inter-process communication not explicitly + described as transactional in its documentation. For example, + sending an email from PL/PerlU would not be transactional; + the email would be sent before the transaction commits and + could not be un-sent if the transaction were to roll back. + /listitem +/itemizedlist + +note + para + When working with external non-transactional resources like files + on disk or network sockets the two-phase commit feature can be + useful. See: xref linkend=sql-prepare-transaction + /para + para + LISTEN/NOTIFY provides a lighter weight but still transaction-friendly method of + triggering changes outside the database in response to changes inside the + database. A LISTENing helper program running outside the database can + perform actions when it gets a NOTIFY after a transaction commits. See: + xref linkend=sql-notify. + /para +/note + + /sect1 + /chapter -- 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] [PATCH] Docs: Make notes on sequences and rollback more obvious
On Mon, Aug 20, 2012 at 4:45 AM, Craig Ringer ring...@ringerc.id.au wrote: Trying again with the attachments; the archiver only seemed to see the first patch despite all three being attached. Including patches inline; if you want 'em prettier, see: https://github.com/ringerc/postgres/tree/sequence_documentation_fixes Subject: [PATCH 1/3] Make sure you can't read through mvcc.sgml without realising that not everything is MVCC. The first of these three patches looks good to me, so I committed it. I am not convinced that the others are ready to go in. AFAICS, there hasn't been any discussion of whether a list of non-transactional features would be a useful thing to have, or if so where it should be located in the docs and what should go into it. I'm not necessarily opposed to adding something, but I think it needs some actual discussion before we commit anything. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] [PATCH] Docs: Make notes on sequences and rollback more obvious
Trying again with the attachments; the archiver only seemed to see the first patch despite all three being attached. Including patches inline; if you want 'em prettier, see: https://github.com/ringerc/postgres/tree/sequence_documentation_fixes Subject: [PATCH 1/3] Make sure you can't read through mvcc.sgml without realising that not everything is MVCC. --- doc/src/sgml/mvcc.sgml | 12 1 file changed, 12 insertions(+) diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml new file mode 100644 index 8f88582..9dc65f5 *** a/doc/src/sgml/mvcc.sgml --- b/doc/src/sgml/mvcc.sgml *** *** 260,265 --- 260,277 command xref linkend=sql-set-transaction. /para +important + para +Some productnamePostgreSQL/productname data types and functions have +special rules regarding transactional behaviour. In particular, changes +made to a literalSEQUENCE/literal (and therefore the counter of a +literalSERIAL/literal) are immediately visible to all other +transactions and are not rolled back if the transaction that made the +changes aborts. See xref linkend=functions-sequence and +xref linkend=datatype-serial. + /para +/important + sect2 id=xact-read-committed titleRead Committed Isolation Level/title -- 1.7.11.2 Subject: [PATCH 2/3] Collect a list of features with abberant transactional behaviour --- doc/src/sgml/mvcc.sgml | 67 ++ 1 file changed, 67 insertions(+) diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml new file mode 100644 index 9dc65f5..e2930c9 *** a/doc/src/sgml/mvcc.sgml --- b/doc/src/sgml/mvcc.sgml *** SELECT pg_advisory_lock(q.id) FROM *** 1540,1543 --- 1540,1610 indexes should be used instead. /para /sect1 + + sect1 id=mvcc-exceptions +titleExceptions to normal transactional rules/title + +para + Some PostgreSQL features, functions and data types differ from the + usual transactional behaviour described in this chapter. Differences + are generally mentioned in the documentation sections for the + features they affect. Such exceptions are collected here for + easy reference. +/para + +para + The following actions and features don't follow the typical + transactional rules: +/para + +itemizedlist + listitem + para + Serial pseudo-types xref linkend=datatype-serial + /para + /listitem + listitem + para + literalSEQUENCE/literals - xref linkend=functions-sequence + /para + /listitem + listitem + para + Advisory locks - xref linkend=advisory-locks + /para + /listitem + listitem + para + Disk writes to files outside the database, as performed by + literalCOPY ... TO/literal, adminpack functions, and other add-ons. + See xref linkend=sql-copy, xref linkend=adminpack. + /para + /listitem + listitem + para + Any network I/O or inter-process communication not explicitly + described as transactional in its documentation. For example, + sending an email from PL/PerlU would not be transactional; + the email would be sent before the transaction commits and + could not be un-sent if the transaction were to roll back. + /listitem +/itemizedlist + +note + para + When working with external non-transactional resources like files + on disk or network sockets the two-phase commit feature can be + useful. See: xref linkend=sql-prepare-transaction + /para + para + LISTEN/NOTIFY provides a lighter weight but still transaction-friendly method of + triggering changes outside the database in response to changes inside the + database. A LISTENing helper program running outside the database can + perform actions when it gets a NOTIFY after a transaction commits. See: + xref linkend=sql-notify. + /para +/note + + /sect1 + /chapter -- 1.7.11.2 Subject: [PATCH 3/3] Change xref of important/ note re SERIAL to point to mvcc-exceptions --- doc/src/sgml/mvcc.sgml | 3 +-- 1 file changed, 1 insertion(+), 2 deletions(-) diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml new file mode 100644 index e2930c9..0de4b75 *** a/doc/src/sgml/mvcc.sgml --- b/doc/src/sgml/mvcc.sgml *** *** 267,274 made to a literalSEQUENCE/literal (and therefore the counter of a literalSERIAL/literal) are immediately visible to all other transactions and are not rolled back if the transaction that made the !changes aborts. See xref linkend=functions-sequence and !xref linkend=datatype-serial. /para /important --- 267,273 made to a literalSEQUENCE/literal (and therefore the counter of a literalSERIAL/literal)
Re: [HACKERS] [PATCH] Docs: Make notes on sequences and rollback more obvious
On 08/19/2012 03:01 AM, Jeff Janes wrote: Or would you instead say that changes made to a sequence are immediately visible to all other transactions ? Yes, that sounds better. OK, how about the attached series, then? The 2nd probably needs improvement - and I expect I've missed some other areas that aren't strictly transactional. Comments? Working branch: https://github.com/ringerc/postgres/tree/sequence_documentation_fixes -- Craig Ringer From 311ea8a493c939a70a89234246f662514687d8c2 Mon Sep 17 00:00:00 2001 From: Craig Ringer ring...@ringerc.id.au Date: Sat, 18 Aug 2012 12:51:28 +0800 Subject: [PATCH 1/3] Make sure you can't read through mvcc.sgml without realising that not everything is MVCC. --- doc/src/sgml/mvcc.sgml | 12 1 file changed, 12 insertions(+) diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml new file mode 100644 index 8f88582..9dc65f5 *** a/doc/src/sgml/mvcc.sgml --- b/doc/src/sgml/mvcc.sgml *** *** 260,265 --- 260,277 command xref linkend=sql-set-transaction. /para +important + para +Some productnamePostgreSQL/productname data types and functions have +special rules regarding transactional behaviour. In particular, changes +made to a literalSEQUENCE/literal (and therefore the counter of a +literalSERIAL/literal) are immediately visible to all other +transactions and are not rolled back if the transaction that made the +changes aborts. See xref linkend=functions-sequence and +xref linkend=datatype-serial. + /para +/important + sect2 id=xact-read-committed titleRead Committed Isolation Level/title -- 1.7.11.2 From 69c892ed85070de8df41f2b00088f503453b2e97 Mon Sep 17 00:00:00 2001 From: Craig Ringer ring...@ringerc.id.au Date: Mon, 20 Aug 2012 11:55:25 +0800 Subject: [PATCH 2/3] Collect a list of features with abberant transactional behavour --- doc/src/sgml/mvcc.sgml | 67 ++ 1 file changed, 67 insertions(+) diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml new file mode 100644 index 9dc65f5..e2930c9 *** a/doc/src/sgml/mvcc.sgml --- b/doc/src/sgml/mvcc.sgml *** SELECT pg_advisory_lock(q.id) FROM *** 1540,1543 --- 1540,1610 indexes should be used instead. /para /sect1 + + sect1 id=mvcc-exceptions +titleExceptions to normal transactional rules/title + +para + Some PostgreSQL features, functions and data types differ from the + usual transactional behaviour described in this chapter. Differences + are generally mentioned in the documentation sections for the + features they affect. Such exceptions are collected here for + easy reference. +/para + +para + The following actions and features don't follow the typical + transactional rules: +/para + +itemizedlist + listitem + para + Serial pseudo-types xref linkend=datatype-serial + /para + /listitem + listitem + para + literalSEQUENCE/literals - xref linkend=functions-sequence + /para + /listitem + listitem + para + Advisory locks - xref linkend=advisory-locks + /para + /listitem + listitem + para + Disk writes to files outside the database, as performed by + literalCOPY ... TO/literal, adminpack functions, and other add-ons. + See xref linkend=sql-copy, xref linkend=adminpack. + /para + /listitem + listitem + para + Any network I/O or inter-process communication not explicitly + described as transactional in its documentation. For example, + sending an email from PL/PerlU would not be transactional; + the email would be sent before the transaction commits and + could not be un-sent if the transaction were to roll back. + /listitem +/itemizedlist + +note + para + When working with external non-transactional resources like files + on disk or network sockets the two-phase commit feature can be + useful. See: xref linkend=sql-prepare-transaction + /para + para + LISTEN/NOTIFY provides a lighter weight but still transaction-friendly method of + triggering changes outside the database in response to changes inside the + database. A LISTENing helper program running outside the database can + perform actions when it gets a NOTIFY after a transaction commits. See: + xref linkend=sql-notify. + /para +/note + + /sect1 + /chapter -- 1.7.11.2 From 35b2aec7f6816c7ecd7bb1f8e2ecb2439043d982 Mon Sep 17 00:00:00 2001 From: Craig Ringer ring...@ringerc.id.au Date: Mon, 20 Aug 2012 12:02:53 +0800 Subject: [PATCH 3/3] Change xref of important/ note re SERIAL to point to mvcc-exceptions --- doc/src/sgml/mvcc.sgml | 3 +-- 1 file changed, 1 insertion(+), 2 deletions(-) diff --git a/doc/src/sgml/mvcc.sgml
Re: [HACKERS] [PATCH] Docs: Make notes on sequences and rollback more obvious
On 08/07/2012 09:45 PM, Kevin Grittner wrote: I also think it's a problem that one can get through the entire Concurrency Control chapter (mvcc.sgml) without a clue that sequences aren't transactional. I think maybe a mention in the Introduction section of that chapter with a ref would be appropriate. How about this? Is it accurate to suggest that sequences behave as if they were always in dirty read isolation? Or would you instead say that changes made to a sequence are immediately visible to all other transactions ? [PATCH] Make sure you can't read through mvcc.sgml without realising that SEQUENCEs and SERIAL don't follow the rules. --- doc/src/sgml/mvcc.sgml | 11 +++ 1 file changed, 11 insertions(+) diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml new file mode 100644 index 8f88582..eed1f85 *** a/doc/src/sgml/mvcc.sgml --- b/doc/src/sgml/mvcc.sgml *** *** 260,265 --- 260,276 command xref linkend=sql-set-transaction. /para +important + para +Some PostgreSQL data types and functions have special transactional +behaviour. Notably, SEQUENCEs +behave as if the isolation level is Dirty Read, irrespective +of the current isolation level, and are exempt from transaction +rollback. See xref linkend=functions-sequence. +SEQUENCEs are used by the SERIAL data types. See xref linkend=datatype-serial. + /para +/important + sect2 id=xact-read-committed titleRead Committed Isolation Level/title -- 1.7.11.2 -- Craig Ringer -- 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] [PATCH] Docs: Make notes on sequences and rollback more obvious
On 08/07/2012 09:45 PM, Kevin Grittner wrote: I also think it's a problem that one can get through the entire Concurrency Control chapter (mvcc.sgml) without a clue that sequences aren't transactional. I'm also wondering about adding something like the following summary of features with odd transactional behaviour. I'm sure there's more than I've listed, but nothing is jumping out at me. sect1 id=mvcc-exceptions titleExceptions to normal transactional rules/title para Some PostgreSQL features, functions and data types differ from the usual transactional behaviour described in this chapter. Differences are generally mentioned in the documentation sections for the features they affect. Such exceptions are collected here for easy reference. /para itemizedlist listitem para Serial pseudo-types xref linkend=datatype-serial /para /listitem listitem para literalSEQUENCE/literals - xref linkend=functions-sequence /para /listitem listitem para Advisory locks - xref linkend=advisory-locks /para /listitem listitem para Disk I/O to files outside the database, as performed by literalCOPY ... TO/literal, adminpack functions, and other add-ons. See xref linkend=sql-copy, xref linkend=adminpack. /para /listitem /itemizedlist /sect1 -- 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] [PATCH] Docs: Make notes on sequences and rollback more obvious
2012/8/7 Kevin Grittner kevin.gritt...@wicourts.gov: I also think it's a problem that one can get through the entire Concurrency Control chapter (mvcc.sgml) without a clue that sequences aren't transactional. It is possible to say that they *are* transactional when considering the following definition: nextval() doesn’t always give you “the” next value, but “some” next value that is higher than the one gotten by any preceding transactions. I personally like it better to introduce this minor complexity in the definition of sequences, rather than messing with the definition of transactionality. Nicolas -- A. Because it breaks the logical sequence of discussion. Q. Why is top posting bad? -- 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] [PATCH] Docs: Make notes on sequences and rollback more obvious
On 08/18/2012 05:19 PM, Nicolas Barbier wrote: 2012/8/7 Kevin Grittner kevin.gritt...@wicourts.gov: I also think it's a problem that one can get through the entire Concurrency Control chapter (mvcc.sgml) without a clue that sequences aren't transactional. It is possible to say that they *are* transactional when considering the following definition: nextval() doesn’t always give you “the” next value, but “some” next value that is higher than the one gotten by any preceding transactions. I personally like it better to introduce this minor complexity in the definition of sequences, rather than messing with the definition of transactionality. I guess they're semi-transactional. You don't get dirty reads unless you actually `SELECT ... FROM some_transaction` which isn't really official API. OTOH, one transaction affects another, and they aren't subject to rollbacks. -- Craig Ringer -- 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] [PATCH] Docs: Make notes on sequences and rollback more obvious
On Sat, Aug 18, 2012 at 1:34 AM, Craig Ringer ring...@ringerc.id.au wrote: On 08/07/2012 09:45 PM, Kevin Grittner wrote: I also think it's a problem that one can get through the entire Concurrency Control chapter (mvcc.sgml) without a clue that sequences aren't transactional. I think maybe a mention in the Introduction section of that chapter with a ref would be appropriate. How about this? Is it accurate to suggest that sequences behave as if they were always in dirty read isolation? I don't think so. I would think that a dirty read would allow unresolved data to be visible, but upon rollback of the other transaction would stop seeing the dirty data. That doesn't describe sequences. A better explanation is that sequence advancement is autonomously committed. Or would you instead say that changes made to a sequence are immediately visible to all other transactions ? Yes, that sounds better. Cheers, Jeff -- 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] [PATCH] Docs: Make notes on sequences and rollback more obvious
On 08/07/2012 02:27 AM, Robert Haas wrote: I did not commit the advanced.sgml changes. That's arguably the most important point to raise this. The most recent question came from someone who actually bothered to RTFM and believed based on the advanced-transactions page that rollback rolls *everything* back. Some kind of hint that there are execptions is IMO very important. I'm not sure what the best form for it to take is. I am not sure I believe the assertion that any function or type with special transactional behavior will include a documentation mention. It absolutely should, but I guess that doesn't mean it's guaranteed to. It doesn't seem like a terribly future-proof assertion at any rate. With respect to the mention of autocommit, I think it would be good to add something there, but maybe it should cross-reference our existing documentation mentions of autocommit. Also, it's a bit ambiguous the way it's worded whether you get the automatic BEGIN/COMMIT with autocommit=on or with autocommit=off; somehow we should try to clarify what we mean a little more there. Yeah. I should've kept that separate, as it was something I noticed in passing, rather than central to the changes. -- Craig Ringer -- 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] [PATCH] Docs: Make notes on sequences and rollback more obvious
On Tue, Aug 7, 2012 at 3:59 AM, Craig Ringer ring...@ringerc.id.au wrote: On 08/07/2012 02:27 AM, Robert Haas wrote: I did not commit the advanced.sgml changes. That's arguably the most important point to raise this. The most recent question came from someone who actually bothered to RTFM and believed based on the advanced-transactions page that rollback rolls *everything* back. Some kind of hint that there are execptions is IMO very important. I'm not sure what the best form for it to take is. I'm not sure, either. Maybe we should avoid blanket statements and just say something like: Note: Some operations on sequences are non-transactional and will not be rolled back on transaction abort. See xref. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] [PATCH] Docs: Make notes on sequences and rollback more obvious
Robert Haas robertmh...@gmail.com wrote: Craig Ringer ring...@ringerc.id.au wrote: On 08/07/2012 02:27 AM, Robert Haas wrote: I did not commit the advanced.sgml changes. That's arguably the most important point to raise this. The most recent question came from someone who actually bothered to RTFM and believed based on the advanced-transactions page that rollback rolls *everything* back. Some kind of hint that there are execptions is IMO very important. I'm not sure what the best form for it to take is. I'm not sure, either. Maybe we should avoid blanket statements and just say something like: Note: Some operations on sequences are non-transactional and will not be rolled back on transaction abort. See xref. I also think it's a problem that one can get through the entire Concurrency Control chapter (mvcc.sgml) without a clue that sequences aren't transactional. I think maybe a mention in the Introduction section of that chapter with a ref would be appropriate. -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] [PATCH] Docs: Make notes on sequences and rollback more obvious
On Tuesday, August 07, 2012 09:45:35 AM Kevin Grittner wrote: [...snipped...] I also think it's a problem that one can get through the entire Concurrency Control chapter (mvcc.sgml) without a clue that sequences aren't transactional. I think maybe a mention in the Introduction section of that chapter with a ref would be appropriate. +1 -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] [PATCH] Docs: Make notes on sequences and rollback more obvious
On Tue, Aug 07, 2012 at 03:59:42PM +0800, Craig Ringer wrote: On 08/07/2012 02:27 AM, Robert Haas wrote: I did not commit the advanced.sgml changes. That's arguably the most important point to raise this. The most recent question came from someone who actually bothered to RTFM and believed based on the advanced-transactions page that rollback rolls *everything* back. Perhaps we should see about correcting that misapprehension. When PostgreSQL does any irreversible process http://en.wikipedia.org/wiki/Irreversible_process such as incrementing a sequence, writing a file, sending an email, etc., it can't be rolled back. Might it be useful to find those irreversible operations we document and mark same? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] [PATCH] Docs: Make notes on sequences and rollback more obvious
On Sat, Aug 4, 2012 at 12:56 AM, Craig Ringer ring...@ringerc.id.au wrote: On 08/04/2012 04:12 AM, Kevin Grittner wrote: I haven't reviewed it in detail but noticed an apparent editing error: which are used the counters should probably have an as thrown in there. Or something. Thanks. Editing fail. I revised that spot repeatedly to try to keep it short and simple without in any way implying that SEQUENCEs are *only* used for SERIAL columns. Fixed attached. In datatype.sgml, I think that adding that important block in the middle of the existing paragraph is too choppy. I moved it down a bit, changed it to a note, expanded it a little, and fixed some typos and markup. In func.sgml, I chose to keep the important at the end, instead of switching the order of the paragraphs as you did, but I moved it up under nextval instead of having it at the end, as you had it. I kept your note in setval() but cleaned it up a bit. I did not commit the advanced.sgml changes. I am not sure I believe the assertion that any function or type with special transactional behavior will include a documentation mention. It doesn't seem like a terribly future-proof assertion at any rate. With respect to the mention of autocommit, I think it would be good to add something there, but maybe it should cross-reference our existing documentation mentions of autocommit. Also, it's a bit ambiguous the way it's worded whether you get the automatic BEGIN/COMMIT with autocommit=on or with autocommit=off; somehow we should try to clarify what we mean a little more there. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Docs: Make notes on sequences and rollback more obvious
Hi all I'm seeing enough questions on pgsql-general and stack overflow to suggest that the docs for how sequences interact with transaction rollback. Take the most recent post on -general, where the person read at least the tutorial, but had no idea about the exemption. The attached patch: - Moves the note about nextval() from the footer to be inside the nextval description - Adds an xref from the advanced-transactions tutorial where the poster noted their point of confusion, noting the exemption and pointing to the docs on nextval. - A pointer from the docs on SERIAL types to the nextval notes on tx rollback. Comments would be appreciated. -- Craig Ringer diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 218988e..423f09e 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -237,6 +237,16 @@ COMMIT; commandCOMMIT/, and all our updates so far will be canceled. /para + note + para + A few things in the database are exempt from rollback. The most + important are literalSEQUENCE/s - which are used the counters in + literalSERIAL/ columns. See xref linkend=functions-sequence. Any + function or type with special transactional behavior will have an explanatory + note in its documentation. + /para + /note + para productnamePostgreSQL/ actually treats every SQL statement as being executed within a transaction. If you do not issue a commandBEGIN/ @@ -251,8 +261,8 @@ COMMIT; para Some client libraries issue commandBEGIN/ and commandCOMMIT/ commands automatically, so that you might get the effect of transaction - blocks without asking. Check the documentation for the interface - you are using. + blocks without asking. Client libraries often call this autocommit. + Check the documentation for the interface you are using. /para /note diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index afc82a2..cbde801 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -800,7 +800,19 @@ NUMERIC typebigserial/type are not true types, but merely a notational convenience for creating unique identifier columns (similar to the literalAUTO_INCREMENT/literal property - supported by some other databases). In the current + supported by some other databases). +/para + +important + para +Because they use literalSEQUENCE/literals, serial data types are + exempt from transactional rollback. This means they can have holes +or gaps where values are discarded. See literalnexval()/literal in + xref linkend=functions-sequence for details. + /para +/important + +paraIn the current implementation, specifying: programlisting diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 157de09..0296d3a 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -9820,6 +9820,27 @@ nextval('foo'::text) lineannotationliteralfoo/literal is looked up at execute functionnextval/function concurrently, each will safely receive a distinct sequence value. /para + + important +para + To avoid blocking concurrent transactions that obtain numbers from the + same sequence, a functionnextval/function operation is never rolled back; + that is, once a value has been fetched it is considered used, even if the + transaction that did the functionnextval/function later aborts. This means + that aborted transactions might leave unused quoteholes/quote in the + sequence of assigned values. functionsetval/function operations are never + rolled back, either. +/para + /important + + para +If a sequence object has been created with default parameters, +successive functionnextval/function calls will return successive values +beginning with 1. Other behaviors can be obtained by using +special parameters in the xref linkend=sql-createsequence command; +see its command reference page for more information. + /para + /listitem /varlistentry @@ -9883,31 +9904,17 @@ SELECT setval('foo', 42, false);lineannotationNext functionnextval/ wi The result returned by functionsetval/function is just the value of its second argument. /para + important +para + Changes to sequences made by setval() are not undone if the transaction + rolls back. See the note on nextval(). +/para + /important /listitem /varlistentry /variablelist /para - para - If a sequence object has been created with default parameters, - successive functionnextval/function calls will return successive values - beginning with 1. Other behaviors can be obtained by using - special parameters in the xref linkend=sql-createsequence command; -
Re: [HACKERS] [PATCH] Docs: Make notes on sequences and rollback more obvious
Craig Ringer ring...@ringerc.id.au wrote: I'm seeing enough questions on pgsql-general and stack overflow to suggest that the docs for how sequences interact with transaction rollback. Yeah, I've noticed a surprising number of people who are being surprised by the non-transactional nature of sequences (and serial columns) in spite of current caveats in the docs; so I agree that we should punch that up in the docs a bit. The attached patch: - Moves the note about nextval() from the footer to be inside the nextval description - Adds an xref from the advanced-transactions tutorial where the poster noted their point of confusion, noting the exemption and pointing to the docs on nextval. - A pointer from the docs on SERIAL types to the nextval notes on tx rollback. Comments would be appreciated. I haven't reviewed it in detail but noticed an apparent editing error: which are used the counters should probably have an as thrown in there. Or something. -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] [PATCH] Docs: Make notes on sequences and rollback more obvious
On 08/04/2012 04:12 AM, Kevin Grittner wrote: I haven't reviewed it in detail but noticed an apparent editing error: which are used the counters should probably have an as thrown in there. Or something. Thanks. Editing fail. I revised that spot repeatedly to try to keep it short and simple without in any way implying that SEQUENCEs are *only* used for SERIAL columns. Fixed attached. diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 218988e..75b1bd4 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -237,6 +237,16 @@ COMMIT; commandCOMMIT/, and all our updates so far will be canceled. /para + note + para + A few things in the database are exempt from rollback. The most + important are literalSEQUENCE/s - which are used by the counters in + literalSERIAL/ columns. See xref linkend=functions-sequence. Any + function or type with special transactional behavior will have an explanatory + note in its documentation. + /para + /note + para productnamePostgreSQL/ actually treats every SQL statement as being executed within a transaction. If you do not issue a commandBEGIN/ @@ -251,8 +261,8 @@ COMMIT; para Some client libraries issue commandBEGIN/ and commandCOMMIT/ commands automatically, so that you might get the effect of transaction - blocks without asking. Check the documentation for the interface - you are using. + blocks without asking. Client libraries often call this autocommit. + Check the documentation for the interface you are using. /para /note diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index afc82a2..cbde801 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -800,7 +800,19 @@ NUMERIC typebigserial/type are not true types, but merely a notational convenience for creating unique identifier columns (similar to the literalAUTO_INCREMENT/literal property - supported by some other databases). In the current + supported by some other databases). +/para + +important + para +Because they use literalSEQUENCE/literals, serial data types are + exempt from transactional rollback. This means they can have holes +or gaps where values are discarded. See literalnexval()/literal in + xref linkend=functions-sequence for details. + /para +/important + +paraIn the current implementation, specifying: programlisting diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 157de09..0296d3a 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -9820,6 +9820,27 @@ nextval('foo'::text) lineannotationliteralfoo/literal is looked up at execute functionnextval/function concurrently, each will safely receive a distinct sequence value. /para + + important +para + To avoid blocking concurrent transactions that obtain numbers from the + same sequence, a functionnextval/function operation is never rolled back; + that is, once a value has been fetched it is considered used, even if the + transaction that did the functionnextval/function later aborts. This means + that aborted transactions might leave unused quoteholes/quote in the + sequence of assigned values. functionsetval/function operations are never + rolled back, either. +/para + /important + + para +If a sequence object has been created with default parameters, +successive functionnextval/function calls will return successive values +beginning with 1. Other behaviors can be obtained by using +special parameters in the xref linkend=sql-createsequence command; +see its command reference page for more information. + /para + /listitem /varlistentry @@ -9883,31 +9904,17 @@ SELECT setval('foo', 42, false);lineannotationNext functionnextval/ wi The result returned by functionsetval/function is just the value of its second argument. /para + important +para + Changes to sequences made by setval() are not undone if the transaction + rolls back. See the note on nextval(). +/para + /important /listitem /varlistentry /variablelist /para - para - If a sequence object has been created with default parameters, - successive functionnextval/function calls will return successive values - beginning with 1. Other behaviors can be obtained by using - special parameters in the xref linkend=sql-createsequence command; - see its command reference page for more information. - /para - - important - para -To avoid blocking concurrent transactions that obtain numbers from the -same sequence, a functionnextval/function operation is never rolled back; -that is, once a value has been