Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key

Hello,

I've been looking at this bug once again and I think I finally
understood what's going on and how to fix it.

Ref 1: https://postgr.es/m/20230707175859.17c91538@karst
Re: Issue attaching a table to a partitioned table with an
auto-referenced foreign key
(Guillaume Lelarge)
Ref 2: https://postgr.es/m/18156-a44bc7096f0683e6@postgresql.org
BUG #18156: Self-referential foreign key in partitioned table not
enforced on deletes
(Matthew Gabeler-Lee)
Ref 3: https://postgr.es/m/myvsiF-Attja5DcWoUWh21R12R-sfXECY2-3ynt8kaOqjw@mail.gmail.com
Self referential foreign keys in partitioned table not working as
expected
(Luca Vallisa)

First of all -- apparently we broke this in commit 5914a22f6ea5 (which
fixed the other problems that had been reported by G. Lelarge in Ref 1)
even worse than how it was before, by having the new functions just skip
processing the referenced side altogether. Previously we were at least
partially setting up the necessary triggers, at least some of the time.
So what the report by Luca is saying is, plain and simple, that the
referenced-side action triggers just do not exist, which is why no error
is thrown even on the most trivial cases, on the releases that contain
that commit (17.1, 16.5, 15.9).

The solution I came up with, is to no longer skip creating the
referenced-side objects when the FK is self-referencing. But in order
for this to work, when cloning FKs to partitions, we must process the
referencing side first rather than the referenced side first as we did
up to now; if we don't do it that way, the code there gets confused
about multiple constraint entries already existing for the same table.
Which one gets processed first shouldn't really have any other important
effect, unless I have overlooked something.

The patch also adds equivalent test cases to what was reported; if I
remove the code fix, these cases fail because they no longer report the
expected errors. (The changes to the other regression expected fails
reflect the triggers that are missing.)

We already fixed some bits in 614a406b4ff1 and siblings, but apparently
the test cases we added there were insufficient, or we would have
detected that we were making things worse in 5914a22f6ea5 :-(

Anyway, if people have a chance to give this a look, it would be
helpful.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Linux transformó mi computadora, de una `máquina para hacer cosas',
en un aparato realmente entretenido, sobre el cual cada día aprendo
algo nuevo" (Jaime Salinas)