Re: ALTER TABLE results in "could not find cast from 3904 to 3831" - Mailing list pgsql-bugs

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.