From: Peter Eisentraut <peter@eisentraut.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix NO ACTION temporal foreign keys when the referenced endpoint
Date: Tue, 21 Jan 2025 13:45:04 +0000
Message-ID: <E1taEYy-002kBb-AS@gemulon.postgresql.org> (raw)
Fix NO ACTION temporal foreign keys when the referenced endpoints change
If a referenced UPDATE changes the temporal start/end times, shrinking
the span the row is valid, we get a false return from
ri_Check_Pk_Match(), but overlapping references may still be valid, if
their reference didn't overlap with the removed span.
We need to consider what span(s) are still provided in the referenced
table. Instead of returning that from ri_Check_Pk_Match(), we can
just look it up in the main SQL query.
Reported-by: Sam Gabrielsson <sam@movsom.se>
Author: Paul Jungwirth <pj@illuminatedcomputing.com>
Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail....
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/1772d554b089c4779eaa13ae1487721e3080d119
Modified Files
--------------
src/backend/catalog/pg_constraint.c | 17 ++++-
src/backend/commands/tablecmds.c | 4 +-
src/backend/utils/adt/ri_triggers.c | 94 +++++++++++++++++++++++++-
src/include/catalog/pg_constraint.h | 3 +-
src/include/catalog/pg_operator.dat | 6 +-
src/test/regress/expected/without_overlaps.out | 62 +++++++++++++++++
src/test/regress/sql/without_overlaps.sql | 54 +++++++++++++++
7 files changed, 233 insertions(+), 7 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: peter@eisentraut.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Fix NO ACTION temporal foreign keys when the referenced endpoint
In-Reply-To: <E1taEYy-002kBb-AS@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox