Re: Prevent foreign key references to unlogged partitions

> Even with the v2 patch, the following steps seem to allow a permanent table
> to reference an unlogged partition. Is this an issue?

Eh, I think it is, but it is not specific to partitioned tables. This
also works, and it shouldn't:

CREATE UNLOGGED TABLE upk (i int PRIMARY KEY);
CREATE UNLOGGED TABLE fk (x int);
ALTER TABLE fk SET LOGGED, ADD FOREIGN KEY (x) REFERENCES upk;

ATPrepChangePersistence checks the existing foreign keys in the prep
phase, the new constraint is added later while the table is still
unlogged, and the rewrite makes it permanent afterwards.

0003 moves the foreign key check right before the end of phase 3.

Similarly to the above example, we also had a (mostly theoretical)
issue with USING expressions and table_rewrite event triggers.

USING expressions are fixed by 0003, but table_rewrite triggers
aren't: if a table_rewrite trigger changes the persistence the rewrite
later reverts that change, and we can again end up with a mismatch in
a foreign key.

0004 fixes this by moving when we fire those triggers. (this also
affected access methods and tablespace changes)

> AFAIR, earlier versions allowed unlogged partitioned tables to have permanent
> partitions. In those versions, would the check added by this patch still work
> as expected in that case?

No, it wouldn't, 17 and earlier need additional checks on both the
referencing and referenced side, and 0002 is significantly more
complex there. I also attached a version for that, with more detailed
test coverage, that should prevent any combination where users try
create a foreign key between logged and unlogged tables.

I kept 0001 for now part of the patchset: if the patch in the other
thread gets committed, I'll drop it, the later patches do not depend
on it.

And one more question: these changes could cause pg_upgrade failures
for affected databases - but it's also a corner case, so I am not sure
how much handling it needs. Should we add detection this for
pg_upgrade --check, or at least mention it in the release notes?

e2bab2d7920 already broke upgrading without an added check, as this is
a valid 17 schema:

CREATE UNLOGGED TABLE upk (a int PRIMARY KEY);
CREATE UNLOGGED TABLE ufk (x int REFERENCES upk) PARTITION BY RANGE (x);
CREATE UNLOGGED TABLE ufk1 PARTITION OF ufk FOR VALUES FROM (0) TO (100);

and it fails the upgrade to 18, but pg_upgrade --check doesn't report it.