UPSERT strange behavior

From: Ivan Frolkov <ifrol2001(at)mail(dot)ru>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: UPSERT strange behavior
Date: 2016-08-25 14:12:38
Message-ID: 1472134358.649524557@f146.i.mail.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


Suppose we have some table

create table cnt(
 usr_id int primary key,
 usr_doc_ref text not null,
 cnt int,
 sum int
);

And going to run some insert on conflict update on it (pgbench script):

\setrandom id 1 50
insert into cnt as c(usr_id,usr_doc_ref, cnt) values(:id, '#'||:id, 1) on conflict(usr_id) do update set cnt=c.cnt+1;

Run it:

 pgbench -c 16 -j 2 -t 50000 -n -h localhost -p 5432 -U postgres -f upsert2-ok.pgb  work
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 2
number of transactions per client: 50000
number of transactions actually processed: 800000/800000
latency average: 0.000 ms
tps = 36475.782816 (including connections establishing)
tps = 36483.759765 (excluding connections establishing)

All ok.
Then add a unique constraint to the table.

alter table cnt add constraint usr_doc_ref_uq unique(usr_doc_ref)

Run pgbench again:

pgbench -c 16 -j 2 -t 50000 -n -h localhost -p 5432 -U postgres -f upsert2-ok.pgb work
client 2 aborted in state 2: ERROR: duplicate key value violates unique constraint "usr_doc_ref_uq"
DETAIL: Key (usr_doc_ref)=(#39) already exists.
client 6 aborted in state 2: ERROR: duplicate key value violates unique constraint "usr_doc_ref_uq"
DETAIL: Key (usr_doc_ref)=(#16) already exists.
client 9 aborted in state 2: ERROR: duplicate key value violates unique constraint "usr_doc_ref_uq"
DETAIL: Key (usr_doc_ref)=(#28) already exists.

So, if we have primary key and unique constraint on a table then upsert will not work as would expected.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2016-08-25 14:18:52 Re: increasing the default WAL segment size
Previous Message Stephen Frost 2016-08-25 13:48:02 Re: increasing the default WAL segment size