Обсуждение: Attribute has wrong type in ALTER TABLE

Hi everyone,

the statements below result in an error "ERROR:  attribute 1 of type
t0 has wrong type":

CREATE TABLE t0(c0 VARCHAR(10));
INSERT INTO t0(c0) VALUES('');
ALTER TABLE t0 ALTER c0 SET DATA TYPE TEXT, ADD EXCLUDE (('a' LIKE
t0.c0) WITH =); -- unexpected: ERROR:  attribute 1 of type t0 has
wrong type

This is unexpected, since breaking up the ALTER TABLE actions does not
result in this error:

CREATE TABLE t0(c0 VARCHAR(10));
INSERT INTO t0(c0) VALUES('');
ALTER TABLE t0 ALTER c0 SET DATA TYPE TEXT;
ALTER TABLE t0 ADD EXCLUDE (('a' LIKE t0.c0) WITH =); -- no error

Best,
Manuel

Manuel Rigger <rigger.manuel@gmail.com> writes:
> the statements below result in an error "ERROR:  attribute 1 of type
> t0 has wrong type":

> CREATE TABLE t0(c0 VARCHAR(10));
> INSERT INTO t0(c0) VALUES('');
> ALTER TABLE t0 ALTER c0 SET DATA TYPE TEXT, ADD EXCLUDE (('a' LIKE
> t0.c0) WITH =); -- unexpected: ERROR:  attribute 1 of type t0 has
> wrong type

Yeah, this is another variant of the problems with doing
transformIndexStmt too early, like your previous report
https://www.postgresql.org/message-id/CA%2Bu7OA4hkFSV_Y%3DsW_vNcYgKFEoq0WL5GtrBWEHUZnCqSqjhAA%40mail.gmail.com
The index expression is parse-analyzed while c0 is still varchar,
and then it's wrong by the time we go to create the index.

We need to fix things so that ALTER TABLE doesn't do any of that
work until after it's finished with ALTER COLUMN TYPE subcommands.

            regards, tom lane

Sorry for the duplicate bug report!

In the following test case, the ALTER TABLE seems to result in some
inconsistent database state, which is observed by the subsequent
INSERT:

CREATE TABLE t0(c0 boolean , c1 integer);
ALTER TABLE t0 ADD EXCLUDE (c1 WITH =) WHERE (t0.c0), DROP c0;
INSERT INTO t0(c1) VALUES (0); -- unexpected: ERROR:  attribute 1 of
type record has wrong type

I assume it's the same underlying bug, but maybe this could be useful
as an additional test case.

Best,
Manuel

On Wed, Jul 24, 2019 at 8:02 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Manuel Rigger <rigger.manuel@gmail.com> writes:
> > the statements below result in an error "ERROR:  attribute 1 of type
> > t0 has wrong type":
>
> > CREATE TABLE t0(c0 VARCHAR(10));
> > INSERT INTO t0(c0) VALUES('');
> > ALTER TABLE t0 ALTER c0 SET DATA TYPE TEXT, ADD EXCLUDE (('a' LIKE
> > t0.c0) WITH =); -- unexpected: ERROR:  attribute 1 of type t0 has
> > wrong type
>
> Yeah, this is another variant of the problems with doing
> transformIndexStmt too early, like your previous report
> https://www.postgresql.org/message-id/CA%2Bu7OA4hkFSV_Y%3DsW_vNcYgKFEoq0WL5GtrBWEHUZnCqSqjhAA%40mail.gmail.com
> The index expression is parse-analyzed while c0 is still varchar,
> and then it's wrong by the time we go to create the index.
>
> We need to fix things so that ALTER TABLE doesn't do any of that
> work until after it's finished with ALTER COLUMN TYPE subcommands.
>
>                         regards, tom lane