The following bug has been logged on the website:
Bug reference: 15556
Logged by: Feike Steenbergen
Email address: feikesteenbergen@gmail.com
PostgreSQL version: 11.1
Operating system: CentOS Linux release 7.5.1804 (Core)
Description:
On a table that has a primary key and multiple unique indexes, we have
recently
seen a duplicate key violation, even though we use ON CONFLICT DO UPDATE.
The values for the separate columns are dependent on each other, therefore
a
unique violation on one of the columns should also violate the other
indexes.
Below a self reproducing testcase for this behaviour.
I would have expected the statement to either insert or update, but
receiving
the duplicate key violation is not something that I expected.
Should I expect a duplicate key violation for the given table and
statements?
regards,
Feike
Reproducing testcase
#!/bin/bash
# within 1 second, I have multiple of these errors:
# ERROR: duplicate key value violates unique constraint "bugtest_pkey"
# DETAIL: Key (i)=(76) already exists.
PARALLEL=10
TIMEOUT=1
TMPFILE=$(mktemp)
trap 'rm -f ${TMPFILE}' EXIT QUIT INT TERM
psql -AtXq <<__EOT__
CREATE OR REPLACE FUNCTION slow_integer(i integer)
RETURNS integer LANGUAGE sql
AS 'SELECT i FROM pg_sleep(random()/1000000);'
IMMUTABLE;
DROP TABLE IF EXISTS bugtest;
CREATE TABLE bugtest(
i int primary key,
j int not null,
k int not null,
counter bigint not null default 1
);
CREATE UNIQUE INDEX ON bugtest (slow_integer(j));
CREATE UNIQUE INDEX ON bugtest (slow_integer(k));
__EOT__
cat > "${TMPFILE}" <<__EOT__
INSERT INTO bugtest(i, j, k)
SELECT i, i, i
FROM CAST(random()*100 AS int) AS sub(i)
ON CONFLICT (slow_integer(k))
DO UPDATE
SET counter=bugtest.counter+1;
\watch 0.0000001
__EOT__
seq ${PARALLEL} | xargs --max-args=1 --max-procs=${PARALLEL} \
timeout ${TIMEOUT} psql -AtXq -f "${TMPFILE}" --set
