Re: Temporal fkey bugs - Mailing list pgsql-hackers

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
unrelatedrow.
 

     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
itselfmodify 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
wheneverthe 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