Horton

Thread

Commits

  1. Fix ALTER COLUMN TYPE failure with a partial exclusion constraint.

  2. Fix ALTER/TYPE on columns referenced by FKs in partitioned tables

  1. BUG #15835: Errors altering data type of the column used in partial exclusion constraint

    The Post Office <noreply@postgresql.org> — 2019-06-05T18:18:42Z

    The following bug has been logged on the website:

Bug reference:      15835
Logged by:          Yaroslav Schekin
Email address:      ladayaroslav@yandex.ru
PostgreSQL version: 11.3
Operating system:   Any
Description:        

Executing the below code:

CREATE TABLE t(some_id int);
ALTER TABLE t ADD EXCLUDE USING btree(some_id WITH =) WHERE (some_id IS NOT
NULL);
ALTER TABLE t ALTER COLUMN some_id TYPE bigint;

Produces the following errors (per PostgreSQL version):
9.4.19, 9.5.15, 9.6.13: ERROR:  could not open relation with OID 195837
10.8: ERROR: cache lookup failed for relation 630589
11.3, 12beta1: ERROR: relation "t_some_id_excl" already exists

  2. Re: BUG #15835: Errors altering data type of the column used in partial exclusion constraint

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-06-11T23:08:33Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> Executing the below code:

> CREATE TABLE t(some_id int);
> ALTER TABLE t ADD EXCLUDE USING btree(some_id WITH =) WHERE (some_id IS NOT
> NULL);
> ALTER TABLE t ALTER COLUMN some_id TYPE bigint;

> Produces the following errors (per PostgreSQL version):
> 9.4.19, 9.5.15, 9.6.13: ERROR:  could not open relation with OID 195837
> 10.8: ERROR: cache lookup failed for relation 630589
> 11.3, 12beta1: ERROR: relation "t_some_id_excl" already exists

Thanks for the report!  The problem seems to be that ATExecAlterColumnType
doesn't consider the possibility that an index that needs to be rebuilt
might be a child of a constraint that needs to be rebuilt.  We haven't
noticed this before because usually a constraint index doesn't have a
direct dependency on the table.  But if there's a WHERE clause, then
dependency analysis of the WHERE clause results in direct dependencies
on the column(s) mentioned in WHERE.

In HEAD, we successfully drop both the index and the constraint, and
then try to rebuild both, and of course the second rebuild hits a
duplicate-index-name problem.  Before v11, it fails even earlier than
that, because we first drop all the constraints and then drop all the
indexes.  (Commit 20bef2c31 explains the change in behavior.)

The attached patch seems to fix it in HEAD.  I'm pretty sure it will
fix the older branches too, but haven't tried to back-patch yet.

In passing, I cleaned up some obsolete comments in pg_depend.c,
and changed a "paranoia" test so that it won't break things so
badly if it fires.

			regards, tom lane