I have found a misbehavior with updatable cursors in certain circumstances.
The problem is that in this case a series of FETCH and DELETE WHERE
CURRENT OF ends up deleting rows that were not the ones fetched.
A test script is attached. Here is the output:
drop table if exists t1;
DROP TABLE
create table t1 (a text, b text, c text);
CREATE TABLE
insert into t1 values ('a1', 'b1', 'c1'), ('a2', 'b2', 'c2'), ('a3', 'b3', 'c3'), ('a4', 'b4', 'c4'), ('a5', 'b5',
'c5');
INSERT 0 5
select * from t1;
a | b | c
----+----+----
a1 | b1 | c1
a2 | b2 | c2
a3 | b3 | c3
a4 | b4 | c4
a5 | b5 | c5
(5 rows)
begin;
BEGIN
declare c1 cursor for select * from (select a, b, c from t1 order by a asc) _ order by a asc for update;
DECLARE CURSOR
fetch first from c1;
a | b | c
----+----+----
a1 | b1 | c1
(1 row)
delete from t1 where current of c1;
DELETE 1
select * from t1;
a | b | c
----+----+----
a1 | b1 | c1
a2 | b2 | c2
a3 | b3 | c3
a4 | b4 | c4
(4 rows)
-- XXX deleted a5 row instead of a1
fetch first from c1;
a | b | c
----+----+----
a1 | b1 | c1
(1 row)
delete from t1 where current of c1;
DELETE 1
select * from t1;
a | b | c
----+----+----
a1 | b1 | c1
a2 | b2 | c2
a3 | b3 | c3
(3 rows)
-- XXX deleted a4 row instead of a1
fetch first from c1;
a | b | c
----+----+----
a1 | b1 | c1
(1 row)
delete from t1 where current of c1;
DELETE 1
select * from t1;
a | b | c
----+----+----
a1 | b1 | c1
a2 | b2 | c2
(2 rows)
-- XXX again
commit;
COMMIT
The plan is
QUERY PLAN
-------------------------------------------------------------------------------------
LockRows (cost=91.86..99.99 rows=650 width=216)
-> Sort (cost=91.86..93.49 rows=650 width=216)
Sort Key: _.a
-> Subquery Scan on _ (cost=46.87..61.49 rows=650 width=216)
-> LockRows (cost=46.87..54.99 rows=650 width=102)
-> Sort (cost=46.87..48.49 rows=650 width=102)
Sort Key: t1.a
-> Seq Scan on t1 (cost=0.00..16.50 rows=650 width=102)
Note that there are two LowRows nodes. Looking at what is happening
above, I guess that the execution of the inner LockRows leaves the "pointer"
at the last row, which is the one that ends up being deleted, instead of the
one previously fetched through the top-level LowRows.
This behavior goes back to PostgreSQL 9.0, presumably related to commit
0adaf4cb312fe3eff83e786d6a0b53ae2cdc9302.
In this particular case, it's easy to work around the issue by removing the
inner ORDER BY, which results in a different plan. But I think it's
problematic to get this misbehavior silently, and it could affect other more
realistic queries.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services