Thread: ALTER TABLE results in "could not find cast from 3904 to 3831"
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. Best, Manuel
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
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.