pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19819: Foreign key DDL can fail with internal errors (XX000) from elog(): "key columns are not both collata
Date: Fri, 30 Oct 2026 06:55:38 +0000
Message-ID: <19819-8aec9277898c3c06@postgresql.org> (raw)

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:        

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='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='f', castcontext='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.<=(int4,int4),
        OPERATOR 4 pg_catalog.>=(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








Message-ID: <19819-8aec9277898c3c06@postgresql.org>
Permalink:  ../19819-8aec9277898c3c06@postgresql.org/
Also on:    postgresql.org/message-id/19819-8aec9277898c3c06@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, 303677365@qq.com
  Subject: Re: BUG #19819: Foreign key DDL can fail with internal errors (XX000) from elog(): "key columns are not both collata
  In-Reply-To: <19819-8aec9277898c3c06@postgresql.org>
  References: <19819-8aec9277898c3c06@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox