From: Bruce Momjian <bruce@momjian.us>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Manuel Rigger <rigger.manuel@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: ALTER TABLE results in "could not find cast from 3904 to 3831"
Date: Wed, 8 Nov 2023 13:58:43 -0500
Message-ID: <ZUvaY3Uw14lD078F@momjian.us> (raw)
In-Reply-To: <20191116235657.4ratlshjd7dt3svw@development>
References: <CA+u7OA4n2Q8G1DD5MEp+mu08ayB+ULRFrG4s_v3LhOj8f4bX-g@mail.gmail.com>
<20191116235657.4ratlshjd7dt3svw@development>
FYI, I can confirm this command still generates the same odd error in
master.
---------------------------------------------------------------------------
On Sun, Nov 17, 2019 at 12:56:57AM +0100, Tomas Vondra wrote:
> On Sat, Nov 16, 2019 at 11:46:02PM +0100, Manuel Rigger wrote:
> > Hi everyone,
> >
> > Consider the following test case:
> >
> > CREATE TABLE t0(c0 int4range UNIQUE, FOREIGN KEY (c0) REFERENCES t0(c0));
> > ALTER TABLE t0 ALTER c0 SET DATA TYPE int4range; -- ERROR: could not
> > find cast from 3904 to 3831
> >
> > Unexpectedly, the ALTER TABLE results in an error. If, for example,
> > the references to the type "int4range" are replaced by "int", the two
> > statements execute without error.
> >
>
> Thanks for the report. You haven't mentioned which PostgreSQL version
> you've tried this on, but I can reproduce it on 12 and I support it
> affects older versions too (haven't checked though).
>
> This seems to be essentially an omission in how we handle polymorphic
> types in ATAddForeignKeyConstraint(). That is, the same issue aplies to
> int4[] for example:
>
> CREATE TABLE t1(c0 int4[] UNIQUE, FOREIGN KEY (c0) REFERENCES t1(c0));
> ALTER TABLE t1 ALTER c0 SET DATA TYPE int4[];
> ERROR: could not find cast from 1007 to 2277
>
> There's actually this comment
>
> /*
> * Otherwise, look for an implicit cast from the FK type to the
> * opcintype, and if found, use the primary equality operator.
> * This is a bit tricky because opcintype might be a polymorphic
> * type such as ANYARRAY or ANYENUM; so what we have to test is
> * whether the two actual column types can be concurrently cast to
> * that type. (Otherwise, we'd fail to reject combinations such
> * as int[] and point[].)
> */
> Oid input_typeids[2];
> Oid target_typeids[2];
> input_typeids[0] = pktype;
> input_typeids[1] = fktype;
> target_typeids[0] = opcintype;
> target_typeids[1] = opcintype;
> if (can_coerce_type(2, input_typeids, target_typeids,
> COERCION_IMPLICIT))
> {
> pfeqop = ffeqop = ppeqop;
> pfeqop_right = opcintype;
> }
>
> And opcintype = 3831 (which is anyrange). We do get it here because the
> opclass is defined like this:
>
> { oid => '3882', descr => 'equal',
> oprname => '=', oprcanmerge => 't', oprcanhash => 't', oprleft => 'anyrange',
> oprright => 'anyrange', oprresult => 'bool', oprcom => '=(anyrange,anyrange)',
> oprnegate => '<>(anyrange,anyrange)', oprcode => 'range_eq',
> oprrest => 'eqsel', oprjoin => 'eqjoinsel' },
>
> i.e. it's a generic opclass using 'anyrange'. And can_coerce_type() does
> return true, which seems fine according to the comment.
>
> But then a couple lines down we do this:
>
> old_pathtype = findFkeyCast(pfeqop_right, old_fktype,
> &old_castfunc);
>
> which internally calls find_coercion_pathway() and that has a comment
> saying this:
>
> /*
> * find_coercion_pathway
> * Look for a coercion pathway between two types.
> *
> * Currently, this deals only with scalar-type cases; it does not consider
> * polymorphic types nor casts between composite types. (Perhaps fold
> * those in someday?)
> ...
> */
>
> So this code does not really work for polymorphic types, I'm afraid. I'm
> not sure how much new code would be needed.
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: bruce@momjian.us, tomas.vondra@2ndquadrant.com, rigger.manuel@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: ALTER TABLE results in "could not find cast from 3904 to 3831"
In-Reply-To: <ZUvaY3Uw14lD078F@momjian.us>
* 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