From: Tom Lane <tgl@sss.pgh.pa.us>
To: bulgakovalexey1980@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17050: cursor with for update + commit in loop
Date: Tue, 08 Jun 2021 14:54:15 -0400
Message-ID: <1125319.1623178455@sss.pgh.pa.us> (raw)
In-Reply-To: <17050-f77aa827dc85247c@postgresql.org>
References: <17050-f77aa827dc85247c@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> If run procedure test_tuple_stream then in result updated 2 rows of 3.
> Why?
So what's happening here is that at the point of the first COMMIT,
we need to save the output of the cursor query, because we have to
release locks and so forth. The expectation is that subsequent
fetches will read from a tuplestore rather than the live query.
To do that, PersistHoldablePortal supposes that it can rewind and
re-read the query's executor run. But that only works if the
query is guaranteed stable, which anything involving FOR UPDATE
is not. In the example at hand, when we re-read the row with ID 1,
nodeLockRows sees that its state is now TM_SelfModified thanks to the
UPDATE we just did. This causes it to not return that row, so that
what ends up in the tuplestore is only the rows with IDs 2 and 3.
But since the cursor "knows" that one row has already been read out,
the next fetch will return the row with ID 3, and the procedure's
loop never visits the row with ID 2.
I imagine that similar misbehavior could be constructed around
queries containing volatile functions (e.g. random()), rather than
FOR UPDATE.
The only obvious way to fix this is to always save aside the output
of a cursor query in case we need to persist it later, so that
PersistHoldablePortal doesn't have to assume that rewinding is safe.
That would be pretty catastrophic for performance, though, so I doubt
anybody will be happy with that answer.
For cursors that aren't marked scrollable, we might be able to say
that we only save the *rest* of the query output, and then adjust
the cursor state appropriately for that choice. Seems possibly
nontrivial though, and there's still the question of what to do
for scrollable ones.
Anyway, many thanks for the report! But don't hold your breath
for a fix; this is going to take some thought.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, bulgakovalexey1980@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17050: cursor with for update + commit in loop
In-Reply-To: <1125319.1623178455@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox