Horton

Foreign key isolation tests

Paul A Jungwirth <pj@illuminatedcomputing.com>

View thread View on postgr.es
From: Paul A Jungwirth <pj@illuminatedcomputing.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-05-16T21:57:07Z
Lists: pgsql-hackers

Attachments

Here are a couple new isolation tests for foreign keys, based on
feedback from the Advanced Patch Review session at PGConf.dev.

The goal is to show that temporal foreign keys do not violate
referential integrity under concurrency. Non-temporal foreign keys use
a crosscheck snapshot to avoid this. Temporal foreign keys do the
same, but this test gives us more assurance.

I noticed that traditional foreign keys didn't have a test either, at
least for the case we discussed: one transaction INSERTS a referencing
row, while the other DELETEs the referenced row. So I have two patches
here: one adding tests for the traditional case; another, the temporal
case.

There is a final patch improving the comment for some snapshot macros.
While fixing a typo I thought I could improve their clarity a bit as
well.

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com

Commits

  1. Improve comment about snapshot macros

  2. Add test for temporal referential integrity

  3. Fill testing gap for possible referential integrity violation

  4. Simplify COALESCE() with one surviving argument.