Horton

BUG #17558: 15beta2: Endless loop with UNIQUE NULLS NOT DISTINCT and INSERT ... ON CONFLICT

The Post Office <noreply@postgresql.org>

View thread View on postgr.es
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: postgresql@middaysomewhere.com
Date: 2022-07-25T01:04:06Z
Lists: pgsql-bugs 
The following bug has been logged on the website:

Bug reference:      17558
Logged by:          Andrew Kesper
Email address:      postgresql@middaysomewhere.com
PostgreSQL version: Unsupported/Unknown
Operating system:   Debian 11 (buster)
Description:        

In PostgreSQL 15 beta, when I try to insert a NULL value which violates a
"UNIQUE NULLS NOT DISTINCT" constraint, PostgreSQL will get stuck in an
endless loop if the command includes an "ON CONFLICT" clause. 

CREATE TABLE test (val TEXT UNIQUE NULLS NOT DISTINCT);
INSERT INTO test (val) VALUES ('a') ON CONFLICT DO NOTHING; -- inserts 'a'
INSERT INTO test (val) VALUES ('a') ON CONFLICT DO NOTHING; -- does
nothing
INSERT INTO test (val) VALUES (NULL) ON CONFLICT DO NOTHING; -- inserts
NULL
INSERT INTO test (val) VALUES (NULL) ON CONFLICT DO NOTHING; --
unresponsive

PostgreSQL will max out the CPU, complain that the WAL is growing too fast,
consume all available disk space, then crash.

Commits

  1. Fix check_exclusion_or_unique_constraint for UNIQUE NULLS NOT DISTINCT.

  2. Add CHECK_FOR_INTERRUPTS in ExecInsert's speculative insertion loop.