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 1xMgsi-000000029Tt-1PId for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:32 +0000 Received: from localhost ([127.0.0.1]:51196 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMgsf-00000008qJy-2Wpa for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:57486) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMgX1-00000008Jhd-495j for pgsql-bugs@lists.postgresql.org; Fri, 30 Oct 2026 06:56:07 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMgWz-00000001ejE-3sr9 for pgsql-bugs@lists.postgresql.org; Fri, 30 Oct 2026 06:56:07 +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=RX1m29LSFUmqpEY5kVSgBq74m+SR++VdT5c4SXRtjKs=; b=msFQnfg/p4fvZ/CHacn5Momlo9 cfF/Rk6gyB6uaYc29MKBzQv10FfYMzGSEr0tmQjLXWQp/GlE5t7/eiUl0KK3bEzG50v9Ibyalbp/P +spQU+CHW2luIXyhqZfQ+IZJOn1+TGF4Mp0ZmLGL/bGWI9H9VJNsKYyfAZA4CCl9DsxFWyHZ4gzAZ bbN125QSw41aT6LmnXhz8vRNJfh391bpL1sxiazeE0S9SMZpIDrbV1BPMla19Cz8vXNJtFn+Sc+bk GwzTFX0925m8LkdJQxFmyzjtGRMrVafUqN0D1dct8gH8nqz2gpoZfD0vJhE0uhP44w7laSVSHaaxI WyQGbUmw==; 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 1xMgWz-00000004m1y-0LR8 for pgsql-bugs@lists.postgresql.org; Fri, 30 Oct 2026 06:56:05 +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 1xMgWw-000000076qn-3Lzd for pgsql-bugs@lists.postgresql.org; Fri, 30 Oct 2026 06:56:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19819: Foreign key DDL can fail with internal errors (XX000) from elog(): "key columns are not both collata To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 303677365@qq.com Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Date: Fri, 30 Oct 2026 06:55:38 +0000 Message-ID: <19819-8aec9277898c3c06@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: 19819 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux (TencentOS 4.4, x86_64) Description: =20 Hello, ATAddForeignKeyConstraint() (src/backend/commands/tablecmds.c) contains two elog(ERROR, ...) calls that are reachable from ordinary user DDL. elog() produces SQLSTATE XX000 (internal error), which per convention should mean "this should never happen" -- but both sites can be reached by plain CREATE TABLE ... REFERENCES / ALTER TABLE ... ADD CONSTRAINT, and one of them rejects a combination that ought to work. 1) "key columns are not both collatable" The referencing and referenced columns have an assignable implicit cast (pg_cast: castcontext=3D'i'), but the two types differ in collatability. Reproducer: CREATE TABLE q1p (a text PRIMARY KEY); CREATE TABLE q1f (a "char" REFERENCES q1p(a)); -- ERROR: XX000: key columns are not both collatable The same happens for "char" -> varchar (implicit function cast), and for binary casts of the stats types: CREATE TABLE q2p (a bytea PRIMARY KEY); CREATE TABLE q2f (a pg_mcv_list REFERENCES q2p(a)); -- ERROR: XX000: key columns are not both collatable For comparison, the reverse combinations (no implicit cast exists) are rejected with a proper user-level error, so the normal rejection path does exist: CREATE TABLE q4p (a "char" PRIMARY KEY); CREATE TABLE q4f (a text REFERENCES q4p(a)); -- ERROR: 42804: ... incompatible types: text and "char" Expected for case 1: since "char" -> text is an implicit function cast (pg_cast: castmethod=3D'f', castcontext=3D'i'), this foreign key combination should either be accepted, or be rejected with a normal user-level SQLSTATE (42804 "datatype mismatch" / "foreign key constraint cannot be implemented"), not an XX000 internal error. 2) "missing operator %d(%u,%u) in opfamily %u" CREATE UNIQUE INDEX accepts an operator class that lacks an equality operator (strategy 3), and the failure only surfaces later, as an internal error, when the foreign key is added: CREATE OPERATOR CLASS cbh_noeq_ops FOR TYPE int4 USING btree AS OPERATOR 1 pg_catalog.<(int4,int4), OPERATOR 2 pg_catalog.<=3D(int4,int4), OPERATOR 4 pg_catalog.>=3D(int4,int4), OPERATOR 5 pg_catalog.>(int4,int4), FUNCTION 1 pg_catalog.btint4cmp(int4,int4); -- deliberately no strategy 3 CREATE TABLE n1p (a int4); CREATE UNIQUE INDEX n1i ON n1p (a cbh_noeq_ops); -- accepted CREATE TABLE n1f (a int4 REFERENCES n1p(a)); -- ERROR: XX000: missing operator 3(23,23) in opfamily 61711 Expected for case 2: a user-level error such as "operator class cbh_noeq_ops has no equality operator", or rejecting the index at CREATE UNIQUE INDEX time. Both elog() sites are in ATAddForeignKeyConstraint() (src/backend/commands/tablecmds.c): the collatability check and the equality-operator lookup. Turning them into ereport() with proper SQLSTATEs (and ideally allowing case 1, since the implicit cast exists) would match how the surrounding code already reports user-reachable conditions. Reproduced on: - PostgreSQL 20devel (snapshot of 2026-10-27, x86_64-pc-linux-gnu) - PostgreSQL 18.6 (REL_18_STABLE, self-built) Both DDL paths (CREATE TABLE ... REFERENCES and ALTER TABLE ... ADD CONSTRAINT) were checked; re-running after DROP and from fresh sessions gives identical results. Regards, Chunling Qin