sql: Foreign Key Cannot Reference Its Own Row

[xudongzheng]

• What did you do?

create table example (a text references example (b), b text unique);
insert into example values ('a', 'a');

• What did you expect to see?

The query works in postgresql

• What did you see instead?

pq: foreign key violation: value ['a'] not found in example@example_b_key [b]

This type of structure can be helpful for storing a rooted tree where the root is defined as pointing to itself. Using NULL for the parent of the root prevents me from making (a, b) a composite primary key.

[knz]

cc @BramGruneir @emsal1863

[jordanlewis]

This should be fixed by the upcoming refresh of FKs by the optimizer in 19.2.

[rafiss]

This also relates to #20041

[knz]

Solution is described (and will be implemented by fixing) #33475.

[RaduBerinde]

Opt-driven FK checks are enabled on master.