agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedMistake in statement example
5+ messages / 4 participants
[nested] [flat]
* Mistake in statement example
@ 2023-03-01 07:21 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: PG Doc comments form @ 2023-03-01 07:21 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: marlene.brandstaetter@cargonet.software
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/transaction-iso.html
Description:
I believe there is a mistake in an example on
https://www.postgresql.org/docs/current/transaction-iso.html section
13.2.1:
BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;
The acctnum is expected to be 12345 in both cases.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Mistake in statement example
@ 2023-03-01 16:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-03-01 16:34 UTC (permalink / raw)
To: marlene.brandstaetter@cargonet.software; +Cc: pgsql-docs@lists.postgresql.org
PG Doc comments form <noreply@postgresql.org> writes:
> I believe there is a mistake in an example on
> https://www.postgresql.org/docs/current/transaction-iso.html section
> 13.2.1:
> BEGIN;
> UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
> UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
> COMMIT;
> The acctnum is expected to be 12345 in both cases.
No, I think that's intentional: the example depicts transferring
$100 from account 7534 to account 12345.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Mistake in statement example
@ 2023-03-01 16:45 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2023-03-01 16:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: marlene.brandstaetter@cargonet.software; pgsql-docs@lists.postgresql.org
On Wed, Mar 1, 2023 at 9:34 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> PG Doc comments form <noreply@postgresql.org> writes:
> > I believe there is a mistake in an example on
> > https://www.postgresql.org/docs/current/transaction-iso.html section
> > 13.2.1:
> > BEGIN;
> > UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
> > UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
> > COMMIT;
>
> > The acctnum is expected to be 12345 in both cases.
>
> No, I think that's intentional: the example depicts transferring
> $100 from account 7534 to account 12345.
>
>
That may be, but the descriptive text and point of the example (which isn't
atomicity, but concurrency) doesn't even require the second update command
to be present. What the example could use is a more traditional
two-session depiction of the commands instead of having a single
transaction and letting the user envision the correct concurrency.
Something like:
S1: SELECT balance FROM accounts WHERE acctnum = 12345; //100
S1: BEGIN;
S2: BEGIN;
S1: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
//200
S2: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
//WAITING ON S1
S1: COMMIT;
S2: UPDATED; balance = 300
S2: COMMIT;
Though maybe "balance" isn't a good example domain, the incrementing
example used just after this one seems more appropriate along with the
added benefit of consistency.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Mistake in statement example
@ 2023-09-27 23:23 Bruce Momjian <bruce@momjian.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bruce Momjian @ 2023-09-27 23:23 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; marlene.brandstaetter@cargonet.software; pgsql-docs@lists.postgresql.org
On Wed, Mar 1, 2023 at 09:45:00AM -0700, David G. Johnston wrote:
> On Wed, Mar 1, 2023 at 9:34 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Doc comments form <noreply@postgresql.org> writes:
> > I believe there is a mistake in an example on
> > https://www.postgresql.org/docs/current/transaction-iso.html section
> > 13.2.1:
> > BEGIN;
> > UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
> > UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
> > COMMIT;
>
> > The acctnum is expected to be 12345 in both cases.
>
> No, I think that's intentional: the example depicts transferring
> $100 from account 7534 to account 12345.
>
>
>
> That may be, but the descriptive text and point of the example (which isn't
> atomicity, but concurrency) doesn't even require the second update command to
> be present. What the example could use is a more traditional two-session
> depiction of the commands instead of having a single transaction and letting
> the user envision the correct concurrency.
>
> Something like:
>
> S1: SELECT balance FROM accounts WHERE acctnum = 12345; //100
> S1: BEGIN;
> S2: BEGIN;
> S1: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; //200
> S2: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; //
> WAITING ON S1
> S1: COMMIT;
> S2: UPDATED; balance = 300
> S2: COMMIT;
>
> Though maybe "balance" isn't a good example domain, the incrementing example
> used just after this one seems more appropriate along with the added benefit of
> consistency.
I developed the attached patch. I explained the example, I mentioned a
"second" transaciton, I changed the account number so I can talk about
the second statement, because read committed changes the row visibility
of the non-first statements, and I changed "transaction" to "statement".
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Attachments:
[text/x-diff] mvcc.diff (1.7K, ../../ZRS5XbHrqhqJQt99@momjian.us/2-mvcc.diff)
download | inline diff:
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
new file mode 100644
index f8f83d4..189cab0
*** a/doc/src/sgml/mvcc.sgml
--- b/doc/src/sgml/mvcc.sgml
***************
*** 413,420 ****
does not see effects of those commands on other rows in the database.
This behavior makes Read Committed mode unsuitable for commands that
involve complex search conditions; however, it is just right for simpler
! cases. For example, consider updating bank balances with transactions
! like:
<screen>
BEGIN;
--- 413,420 ----
does not see effects of those commands on other rows in the database.
This behavior makes Read Committed mode unsuitable for commands that
involve complex search conditions; however, it is just right for simpler
! cases. For example, consider transferring $100 from one account
! to another:
<screen>
BEGIN;
*************** UPDATE accounts SET balance = balance -
*** 423,430 ****
COMMIT;
</screen>
! If two such transactions concurrently try to change the balance of account
! 12345, we clearly want the second transaction to start with the updated
version of the account's row. Because each command is affecting only a
predetermined row, letting it see the updated version of the row does
not create any troublesome inconsistency.
--- 423,430 ----
COMMIT;
</screen>
! If another transactions concurrently tries to change the balance of account
! 7534, we clearly want the second statement to start with the updated
version of the account's row. Because each command is affecting only a
predetermined row, letting it see the updated version of the row does
not create any troublesome inconsistency.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Mistake in statement example
@ 2024-11-01 20:38 Bruce Momjian <bruce@momjian.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2024-11-01 20:38 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; marlene.brandstaetter@cargonet.software; pgsql-docs@lists.postgresql.org
On Wed, Sep 27, 2023 at 07:23:09PM -0400, Bruce Momjian wrote:
> On Wed, Mar 1, 2023 at 09:45:00AM -0700, David G. Johnston wrote:
> > That may be, but the descriptive text and point of the example (which isn't
> > atomicity, but concurrency) doesn't even require the second update command to
> > be present. What the example could use is a more traditional two-session
> > depiction of the commands instead of having a single transaction and letting
> > the user envision the correct concurrency.
> >
> > Something like:
> >
> > S1: SELECT balance FROM accounts WHERE acctnum = 12345; //100
> > S1: BEGIN;
> > S2: BEGIN;
> > S1: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; //200
> > S2: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; //
> > WAITING ON S1
> > S1: COMMIT;
> > S2: UPDATED; balance = 300
> > S2: COMMIT;
> >
> > Though maybe "balance" isn't a good example domain, the incrementing example
> > used just after this one seems more appropriate along with the added benefit of
> > consistency.
>
> I developed the attached patch. I explained the example, I mentioned a
> "second" transaciton, I changed the account number so I can talk about
> the second statement, because read committed changes the row visibility
> of the non-first statements, and I changed "transaction" to "statement".
Patch from September 2023 applied.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-11-01 20:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-01 07:21 Mistake in statement example PG Doc comments form <noreply@postgresql.org>
2023-03-01 16:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-01 16:45 ` David G. Johnston <david.g.johnston@gmail.com>
2023-09-27 23:23 ` Bruce Momjian <bruce@momjian.us>
2024-11-01 20:38 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox