RI fast path gets cross-type foreign keys wrong

On Sat, Aug 1, 2026 at 8:33 Peter Geoghegan <pg@bowt.ie> wrote:

The attached isolation test patch (written by Claude code) shows that
the new RI fast path can get some things wrong when a foreign key uses
a cross-type equality operator.

The test has two permutations differing only in the primary key's type
-- date against a timestamp FK in the first, timestamp against
timestamp in the second. The interleaving is identical and the
referenced key is present throughout, so both should behave the same
way. The second succeeds (which is correct), while the first fails
with a foreign key violation (which is incorrect).

The fast path was introduced by b7b27eb41. This is an issue on 19 and
master only.

Thanks Peter for the report. I’m on vacation atm, will take a look when I
get back next week.

- Amit

On Sat, Aug 1, 2026 at 8:49 Amit Langote <amitlangote09@gmail.com> wrote:

On Sat, Aug 1, 2026 at 8:33 Peter Geoghegan <pg@bowt.ie> wrote:

The attached isolation test patch (written by Claude code) shows that
the new RI fast path can get some things wrong when a foreign key uses
a cross-type equality operator.

The test has two permutations differing only in the primary key's type
-- date against a timestamp FK in the first, timestamp against
timestamp in the second. The interleaving is identical and the
referenced key is present throughout, so both should behave the same
way. The second succeeds (which is correct), while the first fails
with a foreign key violation (which is incorrect).

The fast path was introduced by b7b27eb41. This is an issue on 19 and
master only.

Thanks Peter for the report. I’m on vacation atm, will take a look when I
get back next week.

Still away, but couldn’t help reading the code on my phone.

The recheck block in ri_FastPathFlushArray() appears to put the key it has
just read out of the locked tuple (found_val) into sk_argument, and then
pass that same slot to recheck_matched_pk_tuple(). If I’m reading that
right, both operands come from the locked tuple, so it compares the key
with itself and since sk_argument is the right-hand operand, which on a
cross-type operator is the FK-typed side, the PK value there gets read as
an FK value. That would make the same-type case pass trivially and the
cross-type case fail always, which seems to fit what Peter is seeing.

If that’s really what’s going on, maybe the recheck can just go away,
because the loop a few lines further down in the same scan iteration already
compares found_val against the buffered FK values. I’ll look at it properly
next week.

- Amit

Hello, everyone!

I've (or AI?) found another fast-path/foreign-key - related problem which the
stress-test framework I am prototyping detected. I think this's okay
to send it to that thread; if not, please tell me, and I'll create
another one.

It was all built by Claude; sorry, I have no cycles to carefully
validate all details (there are many findings like this), but it looks
valid - it reproduces the error and even data corruption with just two
new injection points.
I think Amit will be able to understand it all clearly and easily as
the author. If not for some reason, please tell me, and I'll handle it
myself later.

That's a strange time to live :)

Best regards,
Mikhail.

On Sun, Aug 2, 2026 at 4:28 Mihail Nikalayeu <mihailnikalayeu@gmail.com>
wrote:

Hello, everyone!

I've (or AI?) found another fast-path/foreign-key - related problem which
the
stress-test framework I am prototyping detected. I think this's okay
to send it to that thread; if not, please tell me, and I'll create
another one.

It was all built by Claude; sorry, I have no cycles to carefully
validate all details (there are many findings like this), but it looks
valid - it reproduces the error and even data corruption with just two
new injection points.
I think Amit will be able to understand it all clearly and easily as
the author. If not for some reason, please tell me, and I'll handle it
myself later.

Thanks Mihail. Adding this to my list to get back to when I’m back next week
- Amit

Hello!

Thanks Mihail. Adding this to my list to get back to when I’m back next

week

This is one more in the same area (also caught by the stress suite).

The fix and test crafted by Claude were validated and adjusted by me
(but not too carefully).

Enjoy your vacation!
Mikhail.

Hi Mihail,

On Wed, Aug 5, 2026 at 16:58 Mihail Nikalayeu <mihailnikalayeu@gmail.com>
wrote:

Hello!

Thanks Mihail. Adding this to my list to get back to when I’m back next

week

This is one more in the same area (also caught by the stress suite).

The fix and test crafted by Claude were validated and adjusted by me
(but not too carefully).

Enjoy your vacation!

Just got back.

Could you please start a separate thread after all for these issues? It
would be better to keep the discussions separate.

Thanks again for your investigations.

- Amit

Hi,

On Sun, Aug 2, 2026 at 12:59 AM Amit Langote <amitlangote09@gmail.com> wrote:

On Sat, Aug 1, 2026 at 8:49 Amit Langote <amitlangote09@gmail.com> wrote:

On Sat, Aug 1, 2026 at 8:33 Peter Geoghegan <pg@bowt.ie> wrote:

The attached isolation test patch (written by Claude code) shows that
the new RI fast path can get some things wrong when a foreign key uses
a cross-type equality operator.

The test has two permutations differing only in the primary key's type
-- date against a timestamp FK in the first, timestamp against
timestamp in the second. The interleaving is identical and the
referenced key is present throughout, so both should behave the same
way. The second succeeds (which is correct), while the first fails
with a foreign key violation (which is incorrect).

The fast path was introduced by b7b27eb41. This is an issue on 19 and
master only.

Thanks Peter for the report. I’m on vacation atm, will take a look when I get back next week.

Still away, but couldn’t help reading the code on my phone.

The recheck block in ri_FastPathFlushArray() appears to put the key it has just read out of the locked tuple (found_val) into sk_argument, and then pass that same slot to recheck_matched_pk_tuple(). If I’m reading that right, both operands come from the locked tuple, so it compares the key with itself and since sk_argument is the right-hand operand, which on a cross-type operator is the FK-typed side, the PK value there gets read as an FK value. That would make the same-type case pass trivially and the cross-type case fail always, which seems to fit what Peter is seeing.

If that’s really what’s going on, maybe the recheck can just go away, because the loop a few lines further down in the same scan iteration already compares found_val against the buffered FK values. I’ll look at it properly next week.

The reading on the phone was right, so removing the recheck works and
is fine. The marking loop a few lines below already compares
found_val, read after the chain has been followed, against every
buffered FK value, with the arguments in the order the operator
expects, so a key that has moved away matches nothing and we still
report the violation. Reorienting the scan key isn't an option, since
sk_argument would have to be the particular buffered value that this
tuple matched, and the AM doesn't expose which array element that was.

ri_FastPathProbeOne() passes its original scan key, with the FK value
still in sk_argument, so it was never affected. Single-row statements
go through it, and Peter's test passes on unpatched HEAD if the INSERT
adds one row instead of two.

Attached is a patch. I kept Peter's test and added a permutation that
leaves the key where it was moved, so the check must report a
violation.

Also, added an open item for this.

--
Thanks, Amit Langote

Hi,

On Thu, Aug 6, 2026 at 11:11 PM Amit Langote <amitlangote09@gmail.com> wrote:

On Sun, Aug 2, 2026 at 12:59 AM Amit Langote <amitlangote09@gmail.com> wrote:

On Sat, Aug 1, 2026 at 8:49 Amit Langote <amitlangote09@gmail.com> wrote:

On Sat, Aug 1, 2026 at 8:33 Peter Geoghegan <pg@bowt.ie> wrote:

The attached isolation test patch (written by Claude code) shows that
the new RI fast path can get some things wrong when a foreign key uses
a cross-type equality operator.

The test has two permutations differing only in the primary key's type
-- date against a timestamp FK in the first, timestamp against
timestamp in the second. The interleaving is identical and the
referenced key is present throughout, so both should behave the same
way. The second succeeds (which is correct), while the first fails
with a foreign key violation (which is incorrect).

The fast path was introduced by b7b27eb41. This is an issue on 19 and
master only.

Thanks Peter for the report. I’m on vacation atm, will take a look when I get back next week.

Still away, but couldn’t help reading the code on my phone.

The recheck block in ri_FastPathFlushArray() appears to put the key it has just read out of the locked tuple (found_val) into sk_argument, and then pass that same slot to recheck_matched_pk_tuple(). If I’m reading that right, both operands come from the locked tuple, so it compares the key with itself and since sk_argument is the right-hand operand, which on a cross-type operator is the FK-typed side, the PK value there gets read as an FK value. That would make the same-type case pass trivially and the cross-type case fail always, which seems to fit what Peter is seeing.

If that’s really what’s going on, maybe the recheck can just go away, because the loop a few lines further down in the same scan iteration already compares found_val against the buffered FK values. I’ll look at it properly next week.

The reading on the phone was right, so removing the recheck works and
is fine. The marking loop a few lines below already compares
found_val, read after the chain has been followed, against every
buffered FK value, with the arguments in the order the operator
expects, so a key that has moved away matches nothing and we still
report the violation. Reorienting the scan key isn't an option, since
sk_argument would have to be the particular buffered value that this
tuple matched, and the AM doesn't expose which array element that was.

ri_FastPathProbeOne() passes its original scan key, with the FK value
still in sk_argument, so it was never affected. Single-row statements
go through it, and Peter's test passes on unpatched HEAD if the INSERT
adds one row instead of two.

Attached is a patch. I kept Peter's test and added a permutation that
leaves the key where it was moved, so the check must report a
violation.

Would like to commit tomorrow, barring objections.

--
Thanks, Amit Langote

On Fri, Jul 31, 2026 at 11:03 PM Peter Geoghegan <pg@bowt.ie> wrote:

The attached isolation test patch (written by Claude code) shows that
the new RI fast path can get some things wrong when a foreign key uses
a cross-type equality operator.

Opus 5 flagged an independent problem in the RI fast path work. This
one appears to be a resource management issue.

When I run the foreign_key tests with the attached patch applied, I
see these failures:

# END$$;
# CREATE TRIGGER fp_expand_kit_trg AFTER INSERT ON fp_order
# FOR EACH ROW EXECUTE FUNCTION fp_expand_kit();
# INSERT INTO fp_order VALUES (1, 1, 1);
# +WARNING: resource was not closed: relation "fp_product"
# +WARNING: resource was not closed: relation "fp_product_pkey"
# +WARNING: resource was not closed: TupleDesc 0x7f7e109a3448 (34085,-1)
# +WARNING: resource was not closed: TupleDesc 0x7f7e109a2cd8 (34068,-1)
# NOTICE: order 1 expanded into 3 order items

Right afterwards, "Assert(rel->rd_refcnt > 0)" fails within
RelationDecrementReferenceCount().

--
Peter Geoghegan

On Thu, Aug 13, 2026 at 1:22 Peter Geoghegan <pg@bowt.ie> wrote:

On Fri, Jul 31, 2026 at 11:03 PM Peter Geoghegan <pg@bowt.ie> wrote:

The attached isolation test patch (written by Claude code) shows that
the new RI fast path can get some things wrong when a foreign key uses
a cross-type equality operator.

Opus 5 flagged an independent problem in the RI fast path work. This
one appears to be a resource management issue.

When I run the foreign_key tests with the attached patch applied, I
see these failures:

# END$$;
# CREATE TRIGGER fp_expand_kit_trg AFTER INSERT ON fp_order
# FOR EACH ROW EXECUTE FUNCTION fp_expand_kit();
# INSERT INTO fp_order VALUES (1, 1, 1);
# +WARNING: resource was not closed: relation "fp_product"
# +WARNING: resource was not closed: relation "fp_product_pkey"
# +WARNING: resource was not closed: TupleDesc 0x7f7e109a3448 (34085,-1)
# +WARNING: resource was not closed: TupleDesc 0x7f7e109a2cd8 (34068,-1)
# NOTICE: order 1 expanded into 3 order items

Right afterwards, "Assert(rel->rd_refcnt > 0)" fails within
RelationDecrementReferenceCount().

Thanks, Peter. This is another bug, and a hole in the April fix for nested
C-level SPI [1]/messages/by-id/CAEg7pwcKf01FmDqFAf-Hzu_pYnMYScY_Otid-pe9uw3BJ6gq9g@mail.gmail.com.

That fix made the batch callback lists per query level, but left
ri_fastpath_callback_registered global. If an outer FK check has already
set the flag, the nested level does not register its own
ri_FastPathEndBatch() callback. When the nested portal ends, its
ResourceOwner warns about and releases the still-cached relation references
and TupleDesc pins. The outer callback later calls ri_FastPathTeardown() on
the same entries, accounting for the refcount assertion.

This exact case should be addressed by 0001 from the subtransaction series
[2]: /messages/by-id/CA+HiwqGHa3tc6MZFSLyLrvwySdrmpkb1TqkH2jGd3HtKGGZ6cQ@mail.gmail.com
level’s entries. I am checking whether it also handles nested firing
involving the same constraint before committing that series.

[1]: /messages/by-id/CAEg7pwcKf01FmDqFAf-Hzu_pYnMYScY_Otid-pe9uw3BJ6gq9g@mail.gmail.com
/messages/by-id/CAEg7pwcKf01FmDqFAf-Hzu_pYnMYScY_Otid-pe9uw3BJ6gq9g@mail.gmail.com

[2]: /messages/by-id/CA+HiwqGHa3tc6MZFSLyLrvwySdrmpkb1TqkH2jGd3HtKGGZ6cQ@mail.gmail.com
/messages/by-id/CA+HiwqGHa3tc6MZFSLyLrvwySdrmpkb1TqkH2jGd3HtKGGZ6cQ@mail.gmail.com

- Amit

On Fri, Aug 7, 2026 at 10:16 PM Amit Langote <amitlangote09@gmail.com> wrote:

On Thu, Aug 6, 2026 at 11:11 PM Amit Langote <amitlangote09@gmail.com> wrote:

On Sun, Aug 2, 2026 at 12:59 AM Amit Langote <amitlangote09@gmail.com> wrote:

On Sat, Aug 1, 2026 at 8:49 Amit Langote <amitlangote09@gmail.com> wrote:

On Sat, Aug 1, 2026 at 8:33 Peter Geoghegan <pg@bowt.ie> wrote:

The attached isolation test patch (written by Claude code) shows that
the new RI fast path can get some things wrong when a foreign key uses
a cross-type equality operator.

The test has two permutations differing only in the primary key's type
-- date against a timestamp FK in the first, timestamp against
timestamp in the second. The interleaving is identical and the
referenced key is present throughout, so both should behave the same
way. The second succeeds (which is correct), while the first fails
with a foreign key violation (which is incorrect).

The fast path was introduced by b7b27eb41. This is an issue on 19 and
master only.

Thanks Peter for the report. I’m on vacation atm, will take a look when I get back next week.

Still away, but couldn’t help reading the code on my phone.

The recheck block in ri_FastPathFlushArray() appears to put the key it has just read out of the locked tuple (found_val) into sk_argument, and then pass that same slot to recheck_matched_pk_tuple(). If I’m reading that right, both operands come from the locked tuple, so it compares the key with itself and since sk_argument is the right-hand operand, which on a cross-type operator is the FK-typed side, the PK value there gets read as an FK value. That would make the same-type case pass trivially and the cross-type case fail always, which seems to fit what Peter is seeing.

If that’s really what’s going on, maybe the recheck can just go away, because the loop a few lines further down in the same scan iteration already compares found_val against the buffered FK values. I’ll look at it properly next week.

The reading on the phone was right, so removing the recheck works and
is fine. The marking loop a few lines below already compares
found_val, read after the chain has been followed, against every
buffered FK value, with the arguments in the order the operator
expects, so a key that has moved away matches nothing and we still
report the violation. Reorienting the scan key isn't an option, since
sk_argument would have to be the particular buffered value that this
tuple matched, and the AM doesn't expose which array element that was.

ri_FastPathProbeOne() passes its original scan key, with the FK value
still in sk_argument, so it was never affected. Single-row statements
go through it, and Peter's test passes on unpatched HEAD if the INSERT
adds one row instead of two.

Attached is a patch. I kept Peter's test and added a permutation that
leaves the key where it was moved, so the check must report a
violation.

Would like to commit tomorrow, barring objections.

Pushed (cc449a504bf) and marked open item for this issue as fixed.

--
Thanks, Amit Langote