On 2026-09-11 15:23:16 -0400, Andres Freund wrote: > Hi, > > On 2026-09-11 14:51:52 -0400, Andres Freund wrote: > > While looking at FOR PORTION OF (see [1]), some AI tool noted that FPO can > > lead to issues with temporal foreign keys. I don't think the issues were > > really related to FPO, hence this new thread. > > Grmpf, it also found some stuff in temporal keys: > > - RelationFindReplTupleByIndex() skips equality checks for > primary-key/replica-identity indexes without xs_recheck > > Temporal keys can use GiST, where a returned candidate need not be an exact > match. That can lead to modifying the wrong row, which is ... not good. > > - The outer constraint scan honors NULLS NOT DISTINCT, but > index_recheck_constraint() unconditionally treats an existing NULL as > disproving a conflict. That is a problem when needing a recheck. > > See constraint.sql.
And I just saw that GPT-6 found something additional. I'm too tired to edit
these into non-AI-ese:
1. Deleted-row conflict detection also trusts lossy identity-index matches
Location: src/backend/executor/execReplication.c:657–694,
RelationFindDeletedTupleInfoByIndex().
This repeats the live-row lookup’s shortcut: skip equality checking when
using the primary-key/replica-identity index, without honoring xs_recheck.
Verified: with retain_dead_tuples and track_commit_timestamp enabled:
- The target row is missing, and its actual previous deletion has been
vacuumed.
- A different multirange key with the same bounding range is subsequently
inserted and deleted.
- A replicated UPDATE for the missing target reports update_deleted,
naming the transaction that deleted the unrelated row.
Changing only that unrelated key to have a different bounding range
produces update_missing.
The observed consequence is incorrect conflict classification and deletion
metadata. This particular path does not itself modify the wrong row.
2. Parser functional-dependency inference assumes compatible PK equality
Location: src/backend/catalog/pg_constraint.c:1755–1777,
check_functional_grouping().
This is an equality-semantics problem rather than missing xs_recheck. The
function accepts ungrouped columns whenever the grouping columns contain the PK
attributes, without checking the equality semantics.
Using the shipped citext and btree_gist extensions:
CREATE TABLE temporal_grouping (
k citext COLLATE "C",
valid_at int4range,
payload text,
PRIMARY KEY (k, valid_at WITHOUT OVERLAPS)
);
The default GiST opclass uses text equality for k, permitting both:
a | [1,5) | first
A | [1,5) | second
But GROUP BY uses citext equality. This query is nevertheless accepted:
SELECT k, valid_at, payload, count(*)
FROM temporal_grouping
GROUP BY k, valid_at;
Observed result:
a | [1,5) | first | 2
An explicit array_agg(payload) shows {first,second} in that group. The
ungrouped payload is not functionally determined by the actual grouping
key. Dropping the PK makes the same query correctly fail with the
ungrouped-column error.
Greetings,
Andres Freund
grouping.sql
Description: application/sql
