BUG #17945: Different order of definition of a constraint causes constraint violation

The following bug has been logged on the website:

Bug reference: 17945
Logged by: Yann Salaün
Email address: yannsalaun1(at)gmail(dot)com
PostgreSQL version: 15.3
Operating system: Linux
Description:

This is a bug report with a reproducible case where different orders of
definition of a SQL constraint causes different behavior.

To be more specific, if the constraint is defined inside the table
definition as in
```sql
CREATE TABLE node (
id integer PRIMARY KEY,
parent_id integer REFERENCES node(id) ON DELETE CASCADE
);
```
the behavior is not the same as when the constraint is defined after all
tables are defined (like in the output of pg_dump) as in
```sql
CREATE TABLE node (
id integer PRIMARY KEY,
parent_id integer
);
-- define all other tables...
ALTER TABLE node
ADD CONSTRAINT nodes_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES
node(id) ON DELETE CASCADE;
```

The reproductible case comes from an application with tables modelling
filesystem trees. When a parent node is deleted, children are deleted in
cascade via foreign key constraints.

Here is the full psql script to reproduce the problem:
```sql
-- Schema definition
CREATE TABLE node (
id integer PRIMARY KEY,
parent_id integer
);
CREATE TABLE dir (
id integer PRIMARY KEY REFERENCES node(id) ON DELETE CASCADE
);
CREATE TABLE file (
id integer PRIMARY KEY REFERENCES node(id) ON DELETE CASCADE
);
CREATE TABLE t (
main_dir_id integer REFERENCES dir(id) ON DELETE SET NULL,
main_file_id integer REFERENCES file(id) ON DELETE SET NULL,
other_file_id integer REFERENCES file(id) ON DELETE SET NULL
);

-- Constraint definition. If we inline this constraint in the table
definition, the SQL error below disappears.
ALTER TABLE node
ADD CONSTRAINT nodes_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES
node(id) ON DELETE CASCADE;

-- Data insertion
-- main_dir
INSERT INTO node (id) VALUES (1);
INSERT INTO dir (id) VALUES (1);

-- main_file in main_dir
INSERT INTO node (id, parent_id) VALUES (2, 1);
INSERT INTO file (id) VALUES (2);

-- other_file
INSERT INTO node (id) VALUES (3);
INSERT INTO file (id) VALUES (3);

INSERT INTO t (main_dir_id, main_file_id, other_file_id) VALUES (1, 2, 3);

-- Data deletion in a transaction.
BEGIN;

-- First, delete other_file (this sets t.other_file_id to NULL via ON DELETE
SET NULL)
DELETE FROM node WHERE id = 3;

-- Then delete main_dir (this sets t.main_dir_id and t.main_file_id to NULL
via ON DELETE SET NULL)
DELETE FROM node WHERE id = 1;

-- The second DELETE statement returns the following error.

-- ERROR: 23503: insert or update on table "t" violates foreign key
constraint "t_main_file_id_fkey"
-- DETAIL: Key (main_file_id)=(2) is not present in table "file".
-- SCHEMA NAME: public
-- TABLE NAME: t
-- CONSTRAINT NAME: t_main_file_id_fkey
-- LOCATION: ri_ReportViolation, ri_triggers.c:2528

ROLLBACK;
```

I believe this psql script is sufficient to reproduce the bug. Let me know
if that's not the case, I would be happy to provide more details.

Thanks for your answer.

Yann