[BUG?] check_exclusion_or_unique_constraint false negative

Michail Nikolaev <michail.nikolaev@gmail.com>

From: Michail Nikolaev <michail.nikolaev@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2024-07-14T18:01:50Z
Lists: pgsql-hackers

Attachments

Hello, everyone!

While reviewing [1], I noticed that check_exclusion_or_unique_constraint
occasionally returns false negatives for btree unique indexes during UPSERT
operations.
Although this doesn't cause any real issues with INSERT ON CONFLICT, I
wanted to bring it to your attention, as it might indicate an underlying
problem.

Attached is a patch to reproduce the issue.

make -C src/test/modules/test_misc/ check PROVE_TESTS='t/006_*'
....
   Failed test 'concurrent INSERTs status (got 2 vs expected 0)'
#   at t/006_concurrently_unique_fail.pl line 26.

#   Failed test 'concurrent INSERTs stderr /(?^:^$)/'
#   at t/006_concurrently_unique_fail.pl line 26.
#                   'pgbench: error: client 34 script 0 aborted in command
0 query 0: ERROR:  we know 31337 in the index!

Best regards,
Mikhail,

[1]:
https://www.postgresql.org/message-id/flat/CANtu0ogs10w%3DDgbYzZ8MswXE3PUC3J4SGDc0YEuZZeWbL0b6HA%40mail.gmail.com#8c01dcf6051e28c47d25e9471736947e

Commits

  1. Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.