New docs chapter on Transaction Management and related changes

Op 06-09-2022 om 17:16 schreef Simon Riggs:

New chapter on transaction management, plus a few related changes.

Markup and links are not polished yet, so please comment initially on
the topics, descriptions and wording.

[xact_docs.v2.patch]

Very clear explanations, thank you.

Two typos:

'not yet yet part' should be
'not yet part'

'extenal' should be
'external'

Erik

On Tue, 6 Sept 2022 at 17:19, Erik Rijkers <er@xs4all.nl> wrote:

Op 06-09-2022 om 17:16 schreef Simon Riggs:

New chapter on transaction management, plus a few related changes.

Markup and links are not polished yet, so please comment initially on
the topics, descriptions and wording.

[xact_docs.v2.patch]

Very clear explanations, thank you.

Two typos:

'not yet yet part' should be
'not yet part'

'extenal' should be
'external'

Thanks, new version attached.

--
Simon Riggs http://www.EnterpriseDB.com/

On Tue, Sep 06, 2022 at 04:16:02PM +0100, Simon Riggs wrote:

New chapter on transaction management, plus a few related changes.

Markup and links are not polished yet, so please comment initially on
the topics, descriptions and wording.

This is useful. Nitpicks follow.

+ If executed in a subtransaction this will return the top-level xid.

I'd prefer it with a comma after "subtransaction"

+SQL Standard. PostgreSQL supports SAVEPOINTs through the implementation
+defined mechanism of subtransactions, which offer a superset of the required

implementation-defined

+features. Prepared transactions allow PostgreSQL to implement what SQL

what *the

+<para>
+All transactions are identified by a unique VirtualTransactionId (virtualxid or
+vxid), e.g. 4/12532, which is comprised of the BackendId (in this example, 4)
+and a sequentially assigned number unique to that backend, known as LocalXid

sequentially-assigned

+<para>
+If a transaction attempts to write to the database it will be assigned a

database comma

+property is used by the transaction system to say that if one xid is earlier
+than another xid then the earlier transaction attempted to write before the

xid comma then

+<para>
+Currently executing transactions are shown in the pg_locks view in columns

currently-executing

+"virtualxid" (text) and "transactionid" (xid), if an xid has been assigned.

maybe remove the "if assigned" part, since it's described next?

+Read transactions will have a virtualxid but a NULL xid, while write
+transactions will have both a virtualxid and an xid assigned.
+</para>

+<para>
+Row-level read locks may require the assignment of a multixact ID (mxid), which
+are recorded in the pg_multixact directory.

which *is ?

+top-level transaction. Subtransactions can also be started from other
+subtransactions. As a result, the arrangement of transaction and subtransactions

transactions (plural) ?

+form a hierarchy or tree. Thus, each subtransaction has one parent transaction.

+At present in PostgreSQL, only one transaction or subtransaction can be active at
+one time.

one time per command/query/request.

+Subtransactions may end via a commit or abort without affecting their parent

may end either by committing or aborting, without ..

+transaction, allowing the parent transaction to continue.

+also be started in other ways, such as PL/pgSQL's EXCEPTION clause. PL/Python and
+PL/TCL also support explicit subtransactions. Working with C API, users may also
+call BeginInternalSubTransaction().

*the C API ?

+If a subtransaction is assigned an xid, we refer to this as a subxid. Read-only
+subtransactions are not assigned a subxid, but when a subtransaction attempts to
+write it will be assigned a subxid. We ensure that all of a subxid's parents, up

write comma.
Or say: "subxid is not assigned until the subtransaction attempts to
write" ?

+<para>
+The more subtransactions each transaction uses, the greater the overhead for
+transaction management. Up to 64 subxids are cached in shmem for each backend,

backend semicolon

+Those commands are extensions to the SQL Standard, since the SQL syntax is not yet
+yet part of the standard, though the Standard does refer to encompassing

yet yet

+Information relating to these is stored in pg_twophase. Currently prepared

s/these/two-phase commits/

+transactions can be inspected using pg_prepared_xacts view.
+</para>

currently-prepared ?

On Tue, 6 Sept 2022 at 21:33, Justin Pryzby <pryzby@telsasoft.com> wrote:

On Tue, Sep 06, 2022 at 04:16:02PM +0100, Simon Riggs wrote:

New chapter on transaction management, plus a few related changes.

Markup and links are not polished yet, so please comment initially on
the topics, descriptions and wording.

This is useful. Nitpicks follow.

Cool, thanks.

+At present in PostgreSQL, only one transaction or subtransaction can be active at
+one time.

one time per command/query/request.

Apart from that comment, all points accepted, thank you.

I've also added further notes about prepared transactions.

I attach a diff against the original patch, plus a new clean copy.

--
Simon Riggs http://www.EnterpriseDB.com/

On 2022-Sep-06, Simon Riggs wrote:

On Tue, 6 Sept 2022 at 17:19, Erik Rijkers <er@xs4all.nl> wrote:

Op 06-09-2022 om 17:16 schreef Simon Riggs:

New chapter on transaction management, plus a few related changes.

Markup and links are not polished yet, so please comment initially on
the topics, descriptions and wording.

I think the concept of XID epoch should be described also, in or near
the paragraph that talks about wrapping around and switching between
int32 and int64. Right now it's a bit unclear why/how that works.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Uno puede defenderse de los ataques; contra los elogios se esta indefenso"

On Thu, 8 Sept 2022 at 08:42, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

On 2022-Sep-06, Simon Riggs wrote:

On Tue, 6 Sept 2022 at 17:19, Erik Rijkers <er@xs4all.nl> wrote:

Op 06-09-2022 om 17:16 schreef Simon Riggs:

New chapter on transaction management, plus a few related changes.

Markup and links are not polished yet, so please comment initially on
the topics, descriptions and wording.

I think the concept of XID epoch should be described also, in or near
the paragraph that talks about wrapping around and switching between
int32 and int64. Right now it's a bit unclear why/how that works.

Will do

--
Simon Riggs http://www.EnterpriseDB.com/

On Wed, Sep 7, 2022 at 8:05 AM Simon Riggs <simon.riggs@enterprisedb.com> wrote:

On Tue, 6 Sept 2022 at 21:33, Justin Pryzby <pryzby@telsasoft.com> wrote:

On Tue, Sep 06, 2022 at 04:16:02PM +0100, Simon Riggs wrote:

New chapter on transaction management, plus a few related changes.

Markup and links are not polished yet, so please comment initially on
the topics, descriptions and wording.

I've also added further notes about prepared transactions.

I attach a diff against the original patch, plus a new clean copy.

Some feedback on the v4 patch, hopefully useful.

+<para>
+Transactions may be started explicitly using BEGIN and COMMIT
commands, known as
+a transaction block. A transaction will also be started and ended
implicitly for
+each request when outside of a transaction block.
+</para>

Transactions may be managed explicitly using BEGIN and COMMIT commands, known as
a transaction block.

+Committed subtransactions are recorded as committed if the main transaction
+commits.  The word subtransaction is often abbreviated to "subxact".
+</para>

Committed subtransactions are only recorded as committed if the main
transaction commits,
otherwise any work done in a subtransaction will be rolled back or
aborted. The word subtransaction is
often abbreviated as "subxact".

+<para>
+Subtransactions may be started explicitly by using the SAVEPOINT
command, but may
+also be started in other ways, such as PL/pgSQL's EXCEPTION clause.
PL/Python and
+PL/TCL also support explicit subtransactions. Working with the C API, users may
+also call BeginInternalSubTransaction().
+</para>

I think this paragraph (or something similar) should be the opening
paragraph for this section, so that readers are immediately given
context for what PostgreSQL considers to be a subtransaction.

+prepared transactions that were prepared before the last checkpoint.
In the typical
+case, shorter-lived prepared transactions are stored only in shared
memory and WAL.
+Currently-prepared transactions can be inspected using the
pg_prepared_xacts view.
+</para>

Transactions that are currently prepared can be inspected using the
pg_prepated_xacts view.

* I thought the hyphenated wording looked odd, though I understand why
you used it. We don't use it elsewhere though (just `currently
prepared` san hyphen) so re-worded to match the other wording.

Robert Treat
https://xzilla.net

On Sun, 11 Sept 2022 at 21:35, Robert Treat <rob@xzilla.net> wrote:

On Wed, Sep 7, 2022 at 8:05 AM Simon Riggs <simon.riggs@enterprisedb.com> wrote:

On Tue, 6 Sept 2022 at 21:33, Justin Pryzby <pryzby@telsasoft.com> wrote:

On Tue, Sep 06, 2022 at 04:16:02PM +0100, Simon Riggs wrote:

New chapter on transaction management, plus a few related changes.

Markup and links are not polished yet, so please comment initially on
the topics, descriptions and wording.

I've also added further notes about prepared transactions.

I attach a diff against the original patch, plus a new clean copy.

Some feedback on the v4 patch, hopefully useful.

+<para>
+Transactions may be started explicitly using BEGIN and COMMIT
commands, known as
+a transaction block. A transaction will also be started and ended
implicitly for
+each request when outside of a transaction block.
+</para>

Transactions may be managed explicitly using BEGIN and COMMIT commands, known as
a transaction block.

+Committed subtransactions are recorded as committed if the main transaction
+commits.  The word subtransaction is often abbreviated to "subxact".
+</para>

Committed subtransactions are only recorded as committed if the main
transaction commits,
otherwise any work done in a subtransaction will be rolled back or
aborted. The word subtransaction is
often abbreviated as "subxact".

+<para>
+Subtransactions may be started explicitly by using the SAVEPOINT
command, but may
+also be started in other ways, such as PL/pgSQL's EXCEPTION clause.
PL/Python and
+PL/TCL also support explicit subtransactions. Working with the C API, users may
+also call BeginInternalSubTransaction().
+</para>

I think this paragraph (or something similar) should be the opening
paragraph for this section, so that readers are immediately given
context for what PostgreSQL considers to be a subtransaction.

+prepared transactions that were prepared before the last checkpoint.
In the typical
+case, shorter-lived prepared transactions are stored only in shared
memory and WAL.
+Currently-prepared transactions can be inspected using the
pg_prepared_xacts view.
+</para>

Transactions that are currently prepared can be inspected using the
pg_prepated_xacts view.

* I thought the hyphenated wording looked odd, though I understand why
you used it. We don't use it elsewhere though (just `currently
prepared` san hyphen) so re-worded to match the other wording.

Thanks Robert. I've tried to accommodate all of your thoughts, plus Alvaro's.

New v5 attached.

Happy to receive further comments.

--
Simon Riggs http://www.EnterpriseDB.com/

On Tue, Sep 13, 2022 at 03:02:34PM +0100, Simon Riggs wrote:

Thanks Robert. I've tried to accommodate all of your thoughts, plus Alvaro's.

New v5 attached.

Happy to receive further comments.

I liked this patch very much. It gives details on a lot of the
internals we expose to users. Some of my changes were:

* tightening the wording
* restructuring the flow
* splitting out user-visible details (prepared transactions) from
internals, e.g., xid, vxid, subtransactions
* adding references from places in our docs to these new sections

I plan to apply this and backpatch it to all supported versions since
these details apply to all versions. These docs should enable our users
to much better understand and monitor Postgres.

Updated patch attached.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson

Op 13-10-2022 om 23:28 schreef Bruce Momjian:

On Tue, Sep 13, 2022 at 03:02:34PM +0100, Simon Riggs wrote:

Thanks Robert. I've tried to accommodate all of your thoughts, plus Alvaro's.

New v5 attached.

Happy to receive further comments.

I liked this patch very much. It gives details on a lot of the
internals we expose to users. Some of my changes were:

* tightening the wording
* restructuring the flow
* splitting out user-visible details (prepared transactions) from
internals, e.g., xid, vxid, subtransactions
* adding references from places in our docs to these new sections

[xact.diff]

I think that
'This chapter explains how the control the reliability of'

should be:
'This chapter explains how to control the reliability of'

And in these lines:
+   together in a transactional manner.  The commands <command>PREPARE
+   TRANSACTION</command>, <command>COMMIT PREPARED</command> and
+   <command>ROLLBACK PREPARED</command>.  Two-phase transactions

'The commands'

should be
'The commands are'

thanks,

Erik Rijkers

On Thu, Oct 13, 2022 at 11:54:51PM +0200, Erik Rijkers wrote:

[xact.diff]

I think that
'This chapter explains how the control the reliability of'

should be:
'This chapter explains how to control the reliability of'

And in these lines:
+   together in a transactional manner.  The commands <command>PREPARE
+   TRANSACTION</command>, <command>COMMIT PREPARED</command> and
+   <command>ROLLBACK PREPARED</command>.  Two-phase transactions

'The commands'

should be
'The commands are'

Thanks, updated patch attached. You can see the output at:

https://momjian.us/tmp/pgsql/

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson

On Thu, 13 Oct 2022 at 23:06, Bruce Momjian <bruce@momjian.us> wrote:

Thanks, updated patch attached. You can see the output at:

Thank you for your work to tighten and cleanup this patch, much appreciated.

I had two minor typos, plus a slight rewording to avoid using the word
"global" in the last section, since that is associated with
distributed or 2PC transactions. For those changes, I provide a
patch-on-patch so you can see clearly.

In related changes, I've also done some major rewording of the RELEASE
SAVEPOINT command, since it was incorrectly described as having "no
other user visible behavior". A complex example is given to explain,
using the terminology established in the main patch.

--
Simon Riggs http://www.EnterpriseDB.com/

+1 for this new chapter. This latest patch looks pretty good. I think
that introducing the concept of "sub-commit" as in Simon's follow-up
patch clarifies things, though the word itself looks very odd. Maybe
it's okay. The addition of the savepoint example looks good also.

On 2022-Oct-13, Bruce Momjian wrote:

+  <para>
+   <productname>PostgreSQL</productname> supports a two-phase commit (2PC)
+   protocol that allows multiple distributed systems to work together
+   in a transactional manner.  The commands are <command>PREPARE
+   TRANSACTION</command>, <command>COMMIT PREPARED</command> and

I suggest/request that we try to avoid breaking tagged constants in
DocBook; doing so makes it much easier to miss them later when grepping
for them (don't laugh, it has happened to me). Also, it breaks
formatting in some weird cases. I know this makes editing a bit harder
because you can't just reflow with your editor like you would normal
text. So this'd be:

+   in a transactional manner.  The commands are <command>PREPARE TRANSACTION</command>,
+   <command>COMMIT PREPARED</command> and

with whatever word wrapping you like, except breaking between PREPARE
and TRANSACTION.

+   <para>
+    In addition to <literal>vxid</literal> and <type>xid</type>,
+    when a transaction is prepared it is also identified by a Global
+    Transaction Identifier (<acronym>GID</acronym>). GIDs
+    are string literals up to 200 bytes long, which must be
+    unique amongst other currently prepared transactions.
+    The mapping of GID to xid is shown in <link
+    linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link>.
+   </para>

Maybe say "is prepared for two-phase commit", to make the topic of this
paragraph more obvious?

+   <para>
+    Lock waits on table-level locks are shown waiting for
+    <structfield>virtualxid</structfield>, while lock waits on row-level
+    locks are shown waiting for <structfield>transactionid</structfield>.
+    Row-level read and write locks are recorded directly in locked
+    rows and can be inspected using the <xref linkend="pgrowlocks"/>
+    extension.  Row-level read locks might also require the assignment
+    of multixact IDs (<literal>mxid</literal>). Mxids are recorded in
+    the <filename>pg_multixact</filename> directory.
+   </para>

Hmm, ok.

+   <para>
+    The parent xid of each subxid is recorded in the
+    <filename>pg_subtrans</filename> directory. No entry is made for
+    top-level xids since they do not have a parent, nor is an entry made
+    for read-only subtransactions.
+   </para>

Maybe say "the immediate parent xid of each ...", or is it too obvious?

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Las cosas son buenas o malas segun las hace nuestra opinión" (Lisias)

On Fri, 14 Oct 2022 at 09:46, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

+1 for this new chapter. This latest patch looks pretty good. I think
that introducing the concept of "sub-commit" as in Simon's follow-up
patch clarifies things, though the word itself looks very odd. Maybe
it's okay. The addition of the savepoint example looks good also.

On 2022-Oct-13, Bruce Momjian wrote:

+  <para>
+   <productname>PostgreSQL</productname> supports a two-phase commit (2PC)
+   protocol that allows multiple distributed systems to work together
+   in a transactional manner.  The commands are <command>PREPARE
+   TRANSACTION</command>, <command>COMMIT PREPARED</command> and

I suggest/request that we try to avoid breaking tagged constants in
DocBook; doing so makes it much easier to miss them later when grepping
for them (don't laugh, it has happened to me). Also, it breaks
formatting in some weird cases. I know this makes editing a bit harder
because you can't just reflow with your editor like you would normal
text. So this'd be:

+   in a transactional manner.  The commands are <command>PREPARE TRANSACTION</command>,
+   <command>COMMIT PREPARED</command> and

with whatever word wrapping you like, except breaking between PREPARE
and TRANSACTION.

+   <para>
+    In addition to <literal>vxid</literal> and <type>xid</type>,
+    when a transaction is prepared it is also identified by a Global
+    Transaction Identifier (<acronym>GID</acronym>). GIDs
+    are string literals up to 200 bytes long, which must be
+    unique amongst other currently prepared transactions.
+    The mapping of GID to xid is shown in <link
+    linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link>.
+   </para>

Maybe say "is prepared for two-phase commit", to make the topic of this
paragraph more obvious?

+   <para>
+    Lock waits on table-level locks are shown waiting for
+    <structfield>virtualxid</structfield>, while lock waits on row-level
+    locks are shown waiting for <structfield>transactionid</structfield>.
+    Row-level read and write locks are recorded directly in locked
+    rows and can be inspected using the <xref linkend="pgrowlocks"/>
+    extension.  Row-level read locks might also require the assignment
+    of multixact IDs (<literal>mxid</literal>). Mxids are recorded in
+    the <filename>pg_multixact</filename> directory.
+   </para>

Hmm, ok.

+   <para>
+    The parent xid of each subxid is recorded in the
+    <filename>pg_subtrans</filename> directory. No entry is made for
+    top-level xids since they do not have a parent, nor is an entry made
+    for read-only subtransactions.
+   </para>

Maybe say "the immediate parent xid of each ...", or is it too obvious?

+1 to all of those suggestions

--
Simon Riggs http://www.EnterpriseDB.com/

On Fri, 14 Oct 2022 at 08:55, Simon Riggs <simon.riggs@enterprisedb.com> wrote:

In related changes, I've also done some major rewording of the RELEASE
SAVEPOINT command, since it was incorrectly described as having "no
other user visible behavior". A complex example is given to explain,
using the terminology established in the main patch.

ROLLBACK TO SAVEPOINT also needs some clarification, patch attached.

(Commentary: It's confusing to me that ROLLBACK TO SAVEPOINT starts a
new subtransaction, whereas RELEASE SAVEPOINT does not. You might
imagine they would both start a new subtransaction, but that is not
the case.)

--
Simon Riggs http://www.EnterpriseDB.com/

On Fri, Oct 14, 2022 at 08:55:05AM +0100, Simon Riggs wrote:

On Thu, 13 Oct 2022 at 23:06, Bruce Momjian <bruce@momjian.us> wrote:

Thanks, updated patch attached. You can see the output at:

Thank you for your work to tighten and cleanup this patch, much appreciated.

I had two minor typos, plus a slight rewording to avoid using the word
"global" in the last section, since that is associated with
distributed or 2PC transactions. For those changes, I provide a
patch-on-patch so you can see clearly.

Yes, I didn't like global either --- I like your wording. I added your
other changes too, with slight rewording. Merged patch to be posted in
a later email.

In related changes, I've also done some major rewording of the RELEASE
SAVEPOINT command, since it was incorrectly described as having "no
other user visible behavior". A complex example is given to explain,
using the terminology established in the main patch.

Okay, added.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson

On Fri, Oct 14, 2022 at 01:05:14PM +0100, Simon Riggs wrote:

On Fri, 14 Oct 2022 at 08:55, Simon Riggs <simon.riggs@enterprisedb.com> wrote:

In related changes, I've also done some major rewording of the RELEASE
SAVEPOINT command, since it was incorrectly described as having "no
other user visible behavior". A complex example is given to explain,
using the terminology established in the main patch.

ROLLBACK TO SAVEPOINT also needs some clarification, patch attached.

(Commentary: It's confusing to me that ROLLBACK TO SAVEPOINT starts a
new subtransaction, whereas RELEASE SAVEPOINT does not. You might
imagine they would both start a new subtransaction, but that is not
the case.)

Agreed, added.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson

On Fri, Oct 14, 2022 at 10:46:15AM +0200, Álvaro Herrera wrote:

+1 for this new chapter. This latest patch looks pretty good. I think
that introducing the concept of "sub-commit" as in Simon's follow-up
patch clarifies things, though the word itself looks very odd. Maybe
it's okay. The addition of the savepoint example looks good also.

Yes, I like that term since it isn't a permament commit.

On 2022-Oct-13, Bruce Momjian wrote:

+  <para>
+   <productname>PostgreSQL</productname> supports a two-phase commit (2PC)
+   protocol that allows multiple distributed systems to work together
+   in a transactional manner.  The commands are <command>PREPARE
+   TRANSACTION</command>, <command>COMMIT PREPARED</command> and

I suggest/request that we try to avoid breaking tagged constants in
DocBook; doing so makes it much easier to miss them later when grepping
for them (don't laugh, it has happened to me). Also, it breaks
formatting in some weird cases. I know this makes editing a bit harder
because you can't just reflow with your editor like you would normal
text. So this'd be:

+   in a transactional manner.  The commands are <command>PREPARE TRANSACTION</command>,
+   <command>COMMIT PREPARED</command> and

with whatever word wrapping you like, except breaking between PREPARE
and TRANSACTION.

Uh, I do a lot of word wraps and I don't think I can reaonably avoid
these splits.

+   <para>
+    In addition to <literal>vxid</literal> and <type>xid</type>,
+    when a transaction is prepared it is also identified by a Global
+    Transaction Identifier (<acronym>GID</acronym>). GIDs
+    are string literals up to 200 bytes long, which must be
+    unique amongst other currently prepared transactions.
+    The mapping of GID to xid is shown in <link
+    linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link>.
+   </para>

Maybe say "is prepared for two-phase commit", to make the topic of this
paragraph more obvious?

Agreed.

+   <para>
+    The parent xid of each subxid is recorded in the
+    <filename>pg_subtrans</filename> directory. No entry is made for
+    top-level xids since they do not have a parent, nor is an entry made
+    for read-only subtransactions.
+   </para>

Maybe say "the immediate parent xid of each ...", or is it too obvious?

Agreed with your wording.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson

On Fri, Oct 14, 2022 at 12:22:35PM +0100, Simon Riggs wrote:

+   <para>
+    The parent xid of each subxid is recorded in the
+    <filename>pg_subtrans</filename> directory. No entry is made for
+    top-level xids since they do not have a parent, nor is an entry made
+    for read-only subtransactions.
+   </para>

Maybe say "the immediate parent xid of each ...", or is it too obvious?

+1 to all of those suggestions

Attached is the merged patch from all the great comments I received. I
have also rebuilt the docs with the updated patch:

https://momjian.us/tmp/pgsql/

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson