Returning same row twice in certain conditions

From: Yan Maystrenko <yan(at)maystrenko(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Returning same row twice in certain conditions
Date: 2017-11-07 08:19:08
Message-ID: CAEKRE+1zs4nP8tuVhQA9YHOJpGm8cg19SDbCuMA2gTC87m4JKQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Browse pgsql-bugs by date

  From Date Subject
Next Message Jan Przemysław Wójcik 2017-11-07 12:51:35 Re: Fwd: pg_trgm word_similarity inconsistencies or bug
Previous Message Thomas Munro 2017-11-06 21:20:39 Re: BUG #14889: explain analyze is taking much more time than actual execution