Re: BUG #16272: Index expression can refer to wrong attributes if index is created via CREATE TABLE LIKE
От | Tom Lane |
---|---|
Тема | Re: BUG #16272: Index expression can refer to wrong attributes if index is created via CREATE TABLE LIKE |
Дата | |
Msg-id | 6861.1582328011@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | BUG #16272: Index expression can refer to wrong attributes if index is created via CREATE TABLE LIKE (PG Bug reporting form <noreply@postgresql.org>) |
Ответы |
Re: BUG #16272: Index expression can refer to wrong attributes if index is created via CREATE TABLE LIKE
|
Список | pgsql-bugs |
PG Bug reporting form <noreply@postgresql.org> writes: > The index expression in the index created via LIKE ... INCLUDING INDEXES > still refers to the first two attributes of the table, although an attribute > has been put in place before the columns the expression referred to in the > original index. Ugh. So the problem here is that transformTableLikeClause carefully renumbers the Vars in the index expression to match the new column numbers ... as they stand when it runs, which is before any account has been taken of inheritance. It looks like Vars in check constraints are likewise misprocessed, and probably GENERATED expressions as well. I think this is basically another instance of the ALTER TABLE issues I recently fixed: doing this sort of transformation at parse time is fundamentally broken. We should refrain from trying to import the LIKE table's indexes etc. until after MergeAttributes has done its work, and most likely ought to just punt LIKE transformation into DefineRelation altogether. That's probably too big a change to consider back-patching, unfortunately. For future reference, there are some test cases in create_table_like.sql that come oh so close to exposing these issues. But not close enough. See attached test-case patch (with wrong results). regards, tom lane diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index 94d4858..deafc81 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -268,9 +268,10 @@ ALTER TABLE ctlt1 ALTER COLUMN a SET STORAGE MAIN; CREATE TABLE ctlt2 (c text); ALTER TABLE ctlt2 ALTER COLUMN c SET STORAGE EXTERNAL; COMMENT ON COLUMN ctlt2.c IS 'C'; -CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text); +CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text CHECK (length(c) < 7)); ALTER TABLE ctlt3 ALTER COLUMN c SET STORAGE EXTERNAL; ALTER TABLE ctlt3 ALTER COLUMN a SET STORAGE MAIN; +CREATE INDEX ctlt3_fnidx ON ctlt3 ((a || c)); COMMENT ON COLUMN ctlt3.a IS 'A3'; COMMENT ON COLUMN ctlt3.c IS 'C'; COMMENT ON CONSTRAINT ctlt3_a_check ON ctlt3 IS 't3_a_check'; @@ -326,10 +327,11 @@ NOTICE: merging multiple inherited definitions of column "a" Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5) + "ctlt3_c_check" CHECK (length(c) < 7) Inherits: ctlt1, ctlt3 -CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1); +CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS INCLUDING STORAGE) INHERITS(ctlt1); NOTICE: merging column "a" with inherited definition \d+ ctlt13_like Table "public.ctlt13_like" @@ -338,9 +340,12 @@ NOTICE: merging column "a" with inherited definition a | text | | not null | | main | | A3 b | text | | | | extended | | c | text | | | | external | | C +Indexes: + "ctlt13_like_expr_idx" btree ((a || b)) Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5) + "ctlt3_c_check" CHECK (length(b) < 7) Inherits: ctlt1 SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid ANDc.conrelid = 'ctlt13_like'::regclass; diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index 589ee12..185ab84 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -118,9 +118,10 @@ CREATE TABLE ctlt2 (c text); ALTER TABLE ctlt2 ALTER COLUMN c SET STORAGE EXTERNAL; COMMENT ON COLUMN ctlt2.c IS 'C'; -CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text); +CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text CHECK (length(c) < 7)); ALTER TABLE ctlt3 ALTER COLUMN c SET STORAGE EXTERNAL; ALTER TABLE ctlt3 ALTER COLUMN a SET STORAGE MAIN; +CREATE INDEX ctlt3_fnidx ON ctlt3 ((a || c)); COMMENT ON COLUMN ctlt3.a IS 'A3'; COMMENT ON COLUMN ctlt3.c IS 'C'; COMMENT ON CONSTRAINT ctlt3_a_check ON ctlt3 IS 't3_a_check'; @@ -137,7 +138,7 @@ CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INH SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid ANDc.conrelid = 'ctlt1_inh'::regclass; CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3); \d+ ctlt13_inh -CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1); +CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS INCLUDING STORAGE) INHERITS(ctlt1); \d+ ctlt13_like SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid ANDc.conrelid = 'ctlt13_like'::regclass;
В списке pgsql-bugs по дате отправления:
Предыдущее
От: duvall@comfychair.orgДата:
Сообщение: Re: CREATE TABLE IF NOT EXISTS fails with privilege exception whentable exists