Horton

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

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: postgresql@middaysomewhere.com
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>, pgsql-bugs@lists.postgresql.org
Date: 2022-07-25T15:39:40Z
Lists: pgsql-bugs 
PG Bug reporting form <noreply@postgresql.org> writes:
> 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

Yup, still a problem in HEAD.  It correctly reports an error if
you just "INSERT INTO test (val) VALUES (NULL)", but something
in the ON CONFLICT code path seems not to be on board with this.
Peter?

			regards, tom lane

Commits

  1. Fix check_exclusion_or_unique_constraint for UNIQUE NULLS NOT DISTINCT.

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