Re: ALTER TABLE results in "could not find cast from 3904 to 3831"

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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.