BUG #15556: Duplicate key violations even when using ON CONFLICT DOUPDATE

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