Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR77m-00000000h32-3VjZ for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 12:08:23 +0000 Received: from localhost ([127.0.0.1]:33782 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xR77l-0000000AalK-0wjj for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 12:08:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:35888) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR6xv-0000000AaaC-1JCN for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 11:58:11 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR6xq-00000000SFW-3eZ4 for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 11:58:11 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=o5TM//3kUFiW2egn3AGi65dOX672Co6PqppG2nnae6k=; b=iwBfYN+zXam3njkasPIslOQwLl Iy2PDm1omvdGf0eNLc43Q5Marac9vt9CHy9gHdEzwLrHh4dFdeX2xmX8QUStzZJr1QvglH8k7dH65 NHoyisH95ZQItt/a8M4PdbmETpC64kEhFUKruY5hRL+XXpoA1CodR6dT/gjZOewgX+PwyaCGYOD5h KFNsBp2TVRur6lk0mP7AgH9A1W4Q2gOnob8X1DddR1VeYLtwL823fpmFBnnIsJkPdgKM0iEMrSheQ aoFvjGU0NvzO6f/QY4fvp/JPJByf3hEXpTYsMnhOqMudMxYTKHNaw+po+GcQPBznJSGPlAtCuruQh MXXXALjA==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR6xo-000000010oE-2G4q for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 11:58:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xR6xn-00000002fHK-28mc for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 11:58:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19875: Foreign-key self-update rejects an accepted implicit I/O cast To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: imchifan8@gmail.com Reply-To: imchifan8@gmail.com, pgsql-bugs@lists.postgresql.org Date: Wed, 11 Nov 2026 11:57:42 +0000 Message-ID: <19875-73e3c3c7534b9855@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19875 Logged by: Noah Email address: imchifan8@gmail.com PostgreSQL version: 18.6 Operating system: Linux/amd64 Description: =20 An UPDATE that explicitly assigns a valid foreign-key column to itself fails when the column's custom type uses an implicit WITH INOUT cast to the referenced type. PostgreSQL accepts the cast when creating the constraint and inserting the row, but the unchanged-key UPDATE reports `no conversion function from integer_io to integer`. The key value is unchanged and still maps to an existing referenced row, so the UPDATE should succeed and set payload to 11. How to repeat: Run the following as a superuser in a disposable database with `psql -X -f case.sql`: ```sql \set ON_ERROR_STOP off -- Recover cleanly if an earlier interrupted run left the shell type behind. DROP TYPE IF EXISTS integer_io CASCADE; CREATE TYPE integer_io; CREATE FUNCTION integer_io_in(cstring) RETURNS integer_io AS 'int4in' LANGUAGE internal IMMUTABLE STRICT; CREATE FUNCTION integer_io_out(integer_io) RETURNS cstring AS 'int4out' LANGUAGE internal IMMUTABLE STRICT; CREATE TYPE integer_io (INPUT =3D integer_io_in, OUTPUT =3D integer_io_out, LIKE =3D int4); CREATE CAST (integer_io AS int4) WITH INOUT AS IMPLICIT; CREATE TABLE parent (id int4 PRIMARY KEY); CREATE TABLE child (a integer_io REFERENCES parent(id), payload int); INSERT INTO parent VALUES (1); INSERT INTO child VALUES ('1', 10); SELECT conpfeqop[1]::regoperator AS pk_fk_operator, conffeqop[1]::regoperator AS fk_fk_operator FROM pg_constraint WHERE conrelid =3D 'child'::regclass; -- Assigning the FK column to itself should succeed because its value is -- unchanged and still maps to the existing key. Naming the FK column in SET -- makes the UPDATE trigger compare the old and new key values. BEGIN; SAVEPOINT before_update; UPDATE child SET a =3D a, payload =3D 11; ROLLBACK TO SAVEPOINT before_update; SELECT a::text, payload FROM child; ROLLBACK; DROP TABLE child; DROP TABLE parent; DROP CAST (integer_io AS int4); DROP TYPE integer_io CASCADE; ``` The constraint uses integer equality operators: ``` pk_fk_operator | fk_fk_operator --------------------+-------------------- =3D(integer,integer) | =3D(integer,integer) (1 row) ``` The UPDATE then fails with: ``` ERROR: no conversion function from integer_io to integer ``` After rolling back to the savepoint, the row remains unchanged: ``` a | payload ---+--------- 1 | 10 (1 row) ``` The corresponding server log records: ``` ERROR: no conversion function from integer_io to integer STATEMENT: UPDATE child SET a =3D a, payload =3D 11; ``` The evidence indicates that the foreign-key unchanged-key comparison path expects the accepted coercion to be representable as a directly callable function or relabel operation and does not handle this WITH INOUT coercion. The issue was reproduced on PostgreSQL 18.6 on Linux/amd64. This occurs when a foreign key uses a custom type with an implicit WITH INOUT cast and an UPDATE explicitly assigns the key column to itself.