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
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 |