| From: | Raja Sai pranav <rajasaipranav0(at)gmail(dot)com> |
|---|---|
| To: | imchifan8(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | Re: BUG #19875: Foreign-key self-update rejects an accepted implicit I/O cast |
| Date: | 2026-11-21 13:55:45 |
| Message-ID: | CAPHduZA30+k7asR0B7WqbzofBtQ-T69vbZ1vef-6hF7J_mkxuQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
Hi,
I reproduced this on master (4a26e36f87).
When an UPDATE assigns to the FK column, ri_KeysEqual() compares the old
and new values with the FK-FK equality operator, casting both to the
operator's input type first. ri_HashCompareOp() only handles a cast
function or a binary-compatible cast; a comment there says a coercion via
I/O will never be generated for implicit coercions. A cast created WITH
INOUT AS IMPLICIT does give one, so the lookup fails with that error.
INSERT and the constraint check itself work, because they go through the
parser, which handles CoerceViaIO.
The attached patch handles the I/O path in ri_HashCompareOp() and
ri_CompareWithCast(), by calling the output function of the FK type and the
input function of the operator's type.
The test goes in foreign_key.sql. It has to commit the FK row before the
UPDATE: for a row inserted in the same transaction the comparison is
skipped, so the existing ri_fk_io test doesn't reach this code. The new
test fails without the fix and passes with it. regress, isolation and
pg_upgrade pass.
v1 is for master. The REL_18 version has the same code change, with the
test hunk adjusted for that branch, and passes the same way. I haven't
checked older branches.
With ON UPDATE CASCADE, an update of the referenced key in your example
fails with "column a is of type integer_io but expression is of type
integer", with or without the patch. That is because there is no cast back
from int4 to integer_io, so I left it alone.
Regards,
Sai Pranav
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Support-I-O-coercion-when-comparing-foreign-key-v.patch | application/octet-stream | 7.4 KB |
| REL_18-v1-0001-Support-I-O-coercion-when-comparing-foreign-key-v.patch | application/octet-stream | 7.4 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Fujii Masao | 2026-11-21 14:00:02 | Re: BUG #19881: CLUSTER uses a disabled sort path when enable_sort is off |
| Previous Message | Andrey Borodin | 2026-11-21 13:37:50 | Re: BUG #19779: Backend SIGSEGV in UPDATE with FOR UPDATE CTE joined to UNION ALL subquery |