Prevent foreign key references to unlogged partitions

Started by Zsolt Parragi3 months ago7 messageshackers
Jump to latest
#1Zsolt Parragi
zsolt.parragi@percona.com

Hello!

This is another find with my claude feature-crosscheck analysis[1]/messages/by-id/CAN4CZFPBcRObk2sHJKidnuN7hJ_fG7QCdim=YnrN1sjSLFN68A@mail.gmail.com,
with the combination of unlogged tables, partitioned tables and
foreign keys.

Currently foreign key persistence checks only target the specific
relation referenced in the constraint. Partitioned tables are always
permanent, but the partitions can be unlogged, allowing a persistent
table's foreign key to reference an unlogged table.

Simple repro:

CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);
CREATE TABLE fk (x int REFERENCES pk);

(ALTER TABLE SET UNLOGGED on an already attached partition fails, all
other variations I tried currently succeed on master)

0002 aims to fix this with an additional check, and adds some test cases.

And there's actually a "user" of this bug in the code: pgbench with
both --partitions and --unlogged-tables silently ignores the unlogged
flag for top level tables, but creates the partitions of
pgbench_accounts unlogged, which are referenced by other permanent
tables. initCreateTables handles the combination differently,
createPartitions only checks the unlogged flag.

We could possibly make pgbench error out for this combination, ignore
unlogged-tables properly even for the partitions, or fix it to make as
many relations unlogged as possible.

0001 does the latter, only leaving pgbench_accounts (the parent, not
the partitions) and pgbench_branches permanent, everything else is
properly unlogged. We have to keep pgbench_branches permanent, because
pgbench_accounts is a partitioned table, and it can't be unlogged,
even if all of its partitions are unlogged.

[1]: /messages/by-id/CAN4CZFPBcRObk2sHJKidnuN7hJ_fG7QCdim=YnrN1sjSLFN68A@mail.gmail.com

Attachments:

t253620_1
0001-pgbench-don-t-disable-unlogged-tables-when-partition.patchapplication/octet-stream; name=0001-pgbench-don-t-disable-unlogged-tables-when-partition.patchDownload+21-3
0002-Disallow-foreign-keys-referencing-unlogged-partition.patchapplication/octet-stream; name=0002-Disallow-foreign-keys-referencing-unlogged-partition.patchDownload+97-1
#2Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Zsolt Parragi (#1)
Re: Prevent foreign key references to unlogged partitions

v2 is a trivial rebase as v1 no longer applies on master

Attachments:

t253620_2
v2-0002-Disallow-foreign-keys-referencing-unlogged-partit.patchapplication/octet-stream; name=v2-0002-Disallow-foreign-keys-referencing-unlogged-partit.patchDownload+97-1
v2-0001-pgbench-don-t-disable-unlogged-tables-when-partit.patchapplication/octet-stream; name=v2-0001-pgbench-don-t-disable-unlogged-tables-when-partit.patchDownload+21-3
#3Aleksander Alekseev
aleksander@tigerdata.com
In reply to: Zsolt Parragi (#2)
Re: Prevent foreign key references to unlogged partitions

Hi Zsolt,

v2 is a trivial rebase as v1 no longer applies on master

Thanks for the patch. Looks good to me. Changing the CF entry status to RfC.

--
Best regards,
Aleksander Alekseev

#4Fujii Masao
masao.fujii@gmail.com
In reply to: Aleksander Alekseev (#3)
Re: Prevent foreign key references to unlogged partitions

On Wed, Nov 11, 2026 at 8:24 PM Aleksander Alekseev
<aleksander@tigerdata.com> wrote:

Thanks for the patch. Looks good to me. Changing the CF entry status to RfC.

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

----------------------
CREATE TABLE pk (i int PRIMARY KEY) PARTITION BY RANGE (i);
CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);
CREATE UNLOGGED TABLE fk (x int);
ALTER TABLE fk SET LOGGED, ADD CONSTRAINT fk_pk FOREIGN KEY (x) REFERENCES pk;
----------------------

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?

Regards,

--
Fujii Masao

#5Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Fujii Masao (#4)
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.

Attachments:

t253620_5
nocfbot-v3-REL_17-0001-Check-partition-persistence-on-both-sides-of-fore.patchtext/x-patchDownload+274-3
nocfbot-v3-REL_17-0002-Check-foreign-keys-after-adding-them-in-ALTER-TAB.patchtext/x-patchDownload+146-17
nocfbot-v3-REL_17-0003-Don-t-undo-table_rewrite-event-trigger-changes-in.patchtext/x-patchDownload+59-16
v3-0001-pgbench-don-t-disable-unlogged-tables-when-partit.patchtext/x-patchDownload+21-3
v3-0002-Disallow-foreign-keys-referencing-unlogged-partit.patchtext/x-patchDownload+97-1
v3-0003-Check-foreign-keys-after-adding-them-in-ALTER-TAB.patchtext/x-patchDownload+152-23
v3-0004-Don-t-undo-table_rewrite-event-trigger-changes-in.patchtext/x-patchDownload+59-16
#6shihao zhong
zhong950419@gmail.com
In reply to: Zsolt Parragi (#5)
Re: Prevent foreign key references to unlogged partitions

Hi Zsolt,

Thanks for your patches. I have some thoughts.

1. For 003, a SET LOGGED that fails the foreign key check rewrites
the table first. I think it will create too much overhead.

I did a experiment on a 270MB table and your patch writes 310MB wal.
Master only writes 88bytes.

2. For 004, this seems already captured in a different bug[1]/messages/by-id/29523cc1.253e7.1a080818743.Coremail.njuptlgq@163.com, I think the
current patch is to reject access to the table from the trigger.

If that goes in, would 0004 still be needed?

[1]: /messages/by-id/29523cc1.253e7.1a080818743.Coremail.njuptlgq@163.com

Thanks,

#7Zsolt Parragi
zsolt.parragi@percona.com
In reply to: shihao zhong (#6)
Re: Prevent foreign key references to unlogged partitions

Thanks for the review!

1. For 003, a SET LOGGED that fails the foreign key check rewrites
the table first. I think it will create too much overhead.

Good catch, I only thought about the cases that should fail and I didn't notice the WAL overhead.

v4 checks the foreign keys at the start of phase 3 too, before any rewrite. Phase 2 already added the keys of the same command at that point, so this also covers SET LOGGED, ADD FOREIGN KEY. The check at the end remains for event
triggers and USING expressions, we need both.

There's also a new regression test which verifies that no table_rewrite event fires in this case. I am not sure if we want to keep that, but for the review now I think it's better to have more tests than less.

2. For 004, this seems already captured in a different bug[1], I think the
current patch is to reject access to the table from the trigger.

If that goes in, would 0004 still be needed?

No, if that gets committed, we don't need 0004. For now I kept it here, but if that gets committed, I'll drop it, and also the previous patches can be committed without it.

Attachments:

t253620_7
nocfbot-v4-REL_17-0001-Check-partition-persistence-on-both-sides-of-fore.patchtext/x-patchDownload+274-3
nocfbot-v4-REL_17-0002-Check-foreign-keys-after-adding-them-in-ALTER-TAB.patchtext/x-patchDownload+191-17
nocfbot-v4-REL_17-0003-Don-t-undo-table_rewrite-event-trigger-changes-in.patchtext/x-patchDownload+59-16
v4-0001-pgbench-don-t-disable-unlogged-tables-when-partit.patchtext/x-patchDownload+21-3
v4-0002-Disallow-foreign-keys-referencing-unlogged-partit.patchtext/x-patchDownload+97-1
v4-0003-Check-foreign-keys-after-adding-them-in-ALTER-TAB.patchtext/x-patchDownload+197-23
v4-0004-Don-t-undo-table_rewrite-event-trigger-changes-in.patchtext/x-patchDownload+59-16