WHERE CURRENT OF behaviour is not what's documented
Hi,
I have experimented with cursors a little and found that the part about FOR SHARE/FOR
UPDATE in
http://www.postgresql.org/docs/9.2/interactive/sql-declare.html
i.e. the "sensitive cursor" is not what actually happens. BTW, 9.3 has the same contents
for the same page.
"
If the cursor's query includes FOR UPDATE or FOR SHARE, then returned rows are locked at
the time they are first fetched, in the same way as for a regular SELECT
<http://www.postgresql.org/docs/9.3/interactive/sql-select.html> command with these
options. In addition, the returned rows will be the most up-to-date versions; therefore
these options provide the equivalent of what the SQL standard calls a "sensitive cursor".
(Specifying INSENSITIVE together with FOR UPDATE or FOR SHARE is an error.)
"
The statement that the "most up-to-date versions of the rows are returned"
doesn't reflect the reality anymore:
$ psql
psql (9.2.4)
Type "help" for help.
zozo=> create table xxx (id serial primary key, t text);
NOTICE: CREATE TABLE will create implicit sequence "xxx_id_seq" for serial column "xxx.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "xxx_pkey" for table "xxx"
CREATE TABLE
zozo=> insert into xxx (t) values ('a'), ('b'), ('c');
INSERT 0 3
zozo=> begin;
BEGIN
zozo=> declare mycur cursor for select * from xxx for update;
DECLARE CURSOR
zozo=> fetch all from mycur;
id | t
----+---
1 | a
2 | b
3 | c
(3 rows)
zozo=> move absolute 0 in mycur;
MOVE 0
zozo=> fetch from mycur;
id | t
----+---
1 | a
(1 row)
zozo=> update xxx set t = t || '_x' where current of mycur;
UPDATE 1
zozo=> move absolute 0 in mycur;
MOVE 0
zozo=> fetch all from mycur;
id | t
----+---
2 | b
3 | c
(2 rows)
What happened to the "most up-to-date row" of "id == 1"?
zozo=> select * from xxx where id = 1;
id | t
----+-----
1 | a_x
(1 row)
The same behaviour is experienced under 9.2.4 and 9.3.0.
As a side note, I couldn't test 8.4.17, 9.0.13 and 9.1.9 under Fedora 19,
because initdb fails for all 3 versions. I am bitten by the same as what's
described here: /messages/by-id/14242.1365200084@sss.pgh.pa.us
It the above cursor behaviour is the accepted/expected one?
Since SCROLL (with or without INSENSITIVE) cannot be specified together
with FOR UPDATE/FOR SHARE, I know the MOVE ABSOLUTE 0 is on the
verge of being invalid in this case.
But in any case, either the documentation should tell that the UPDATEd
rows will be missing from a reset executor run or MOVE ABSOLUTE
with a value smaller than portal->portalPos should also be refused
just like MOVE BACKWARD.
As another side note, portal->portalPos mentions it can overflow,
so I suggest using int64 explicitly, so it's ensured that 32-bit systems
get the same overflow behaviour as 64-bit ones. Or (the horror, the horror!) int128_t.
Best regards,
Zolt�n B�sz�rm�nyi
--
----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
Gr�hrm�hlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/
On 2013-09-18 14:23:19 +0200, Boszormenyi Zoltan wrote:
Hi,
I have experimented with cursors a little and found that the part about FOR
SHARE/FOR UPDATE inhttp://www.postgresql.org/docs/9.2/interactive/sql-declare.html
i.e. the "sensitive cursor" is not what actually happens. BTW, 9.3 has the
same contents for the same page."
If the cursor's query includes FOR UPDATE or FOR SHARE, then returned rows
are locked at the time they are first fetched, in the same way as for a
regular SELECT
<http://www.postgresql.org/docs/9.3/interactive/sql-select.html> command
with these options. In addition, the returned rows will be the most
up-to-date versions; therefore these options provide the equivalent of what
the SQL standard calls a "sensitive cursor". (Specifying INSENSITIVE
together with FOR UPDATE or FOR SHARE is an error.)
"The statement that the "most up-to-date versions of the rows are returned"
doesn't reflect the reality anymore:
I think it's not referring to the behaviour inside a single session but
across multiple sessions. I.e. when we follow the ctid chain of a tuple
updated in a concurrent session.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2013-09-18 14:27 keltez�ssel, Andres Freund �rta:
On 2013-09-18 14:23:19 +0200, Boszormenyi Zoltan wrote:
Hi,
I have experimented with cursors a little and found that the part about FOR
SHARE/FOR UPDATE inhttp://www.postgresql.org/docs/9.2/interactive/sql-declare.html
i.e. the "sensitive cursor" is not what actually happens. BTW, 9.3 has the
same contents for the same page."
If the cursor's query includes FOR UPDATE or FOR SHARE, then returned rows
are locked at the time they are first fetched, in the same way as for a
regular SELECT
<http://www.postgresql.org/docs/9.3/interactive/sql-select.html> command
with these options. In addition, the returned rows will be the most
up-to-date versions; therefore these options provide the equivalent of what
the SQL standard calls a "sensitive cursor". (Specifying INSENSITIVE
together with FOR UPDATE or FOR SHARE is an error.)
"The statement that the "most up-to-date versions of the rows are returned"
doesn't reflect the reality anymore:I think it's not referring to the behaviour inside a single session but
across multiple sessions. I.e. when we follow the ctid chain of a tuple
updated in a concurrent session.
But the documentation doesn't spell it out. Perhaps a little too terse.
Quoting the SQL2011 draft, 4.33.2 Operations on and using cursors, page 112:
If a cursor is open, and the SQL-transaction in which the cursor was opened makes a
significant change to
SQL-data, then whether that change is visible through that cursor before it is closed is
determined as follows:
� If the cursor is insensitive, then significant changes are not visible.
� If the cursor is sensitive, then significant changes are visible.
� If the cursor is asensitive, then the visibility of significant changes is
implementation-dependent.
SQL2003 has the same wording in 4.32.2 Operations on and using cursors
on page 96.
So, a SENSITIVE cursor shows "significant changes" (I guess a modified
row counts as one) and they should be shown in the _same_ transaction
where the cursor was opened. If anything, the PostgreSQL cursor
implementation for FOR SHARE/FOR UPDATE is "asensitive".
Also, "14.10 <update statement: positioned>", paragraph 14) in General Rules
in SQL2003 (page 848) or "15.6 Effect of a positioned update", paragraph 16)
in SQL2011 draft (page 996) says the new row replaces the old row
*in the cursor*, not just in the table. Quote:
"
Let R1 be the candidate new row and let R be the current row of CR.
...
The current row R of CR is replaced by R1.
"
Best regards,
Zolt�n B�sz�rm�nyi
--
----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
Gr�hrm�hlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers