Hi!
I started to get 2 rows instead of 1 and ended up with this minimal steps to reproduce
1) setup:
CREATE TABLE test
(
id integer NOT NULL PRIMARY KEY,
value integer
);
insert into test(id)
(select generate_series(1, 100000));
2) run this transaction in parallel many times.
I have ran around 50 threads using php script and starting new connection every time.
begin transaction;
with
sub1 as (
select
ctid,
xmin,
xmax,
id
from
test
limit 1
for update skip locked
),
upd as (
update
test
set
value = random()
where
id = (select id from sub1)
)
select
*,
(
select count(*)
from sub1
) as cnt
from
sub1;
-- sleep for 1-2 seconds in backend;
commit;
3) after some time query returns 2 rows instead of 1. result looks like this:
ctid xmin xmax id cnt
(443,219) 1051 1093 408 1
(443,219) 1051 1093 408 1
I expect to see only one row here.
All values are always same.
Not reproduces without 'upd' subquery.
Reproduces on:
PostgreSQL 9.5.9 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
