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