From a8b7408686a5576cf3b102dcbec61d37dde04b02 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 26 Mar 2024 15:28:16 -0400 Subject: [PATCH] Fix failure of ALTER FOREIGN TABLE SET SCHEMA to move sequences. Ordinary ALTER TABLE SET SCHEMA will also move any owned sequences into the new schema. We failed to do likewise for foreign tables, because AlterTableNamespaceInternal believed that only certain relkinds could have indexes, owned sequences, or constraints. We could simply add foreign tables to that relkind list, but it seems likely that the same oversight could be made again in future. Instead let's remove the relkind filter altogether. These functions shouldn't cost much when there are no objects that they need to process, and surely this isn't an especially performance-critical case anyway. Per bug #18407 from Vidushi Gupta. Back-patch to all supported branches. Discussion: https://postgr.es/m/18407-4fd07373d252c6a0@postgresql.org --- src/backend/commands/tablecmds.c | 15 +++++---------- src/test/regress/expected/foreign_data.out | 29 +++++++++++++++++------------ src/test/regress/sql/foreign_data.sql | 2 ++ 3 files changed, 24 insertions(+), 22 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 18d9d2f3f4..c209061a63 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -15051,16 +15051,11 @@ AlterTableNamespaceInternal(Relation rel, Oid oldNspOid, Oid nspOid, nspOid, false, false, objsMoved); /* Fix other dependent stuff */ - if (rel->rd_rel->relkind == RELKIND_RELATION || - rel->rd_rel->relkind == RELKIND_MATVIEW || - rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) - { - AlterIndexNamespaces(classRel, rel, oldNspOid, nspOid, objsMoved); - AlterSeqNamespaces(classRel, rel, oldNspOid, nspOid, - objsMoved, AccessExclusiveLock); - AlterConstraintNamespaces(RelationGetRelid(rel), oldNspOid, nspOid, - false, objsMoved); - } + AlterIndexNamespaces(classRel, rel, oldNspOid, nspOid, objsMoved); + AlterSeqNamespaces(classRel, rel, oldNspOid, nspOid, + objsMoved, AccessExclusiveLock); + AlterConstraintNamespaces(RelationGetRelid(rel), oldNspOid, nspOid, + false, objsMoved); table_close(classRel, RowExclusiveLock); } diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 1376bfefa2..d568050c52 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -877,26 +877,31 @@ ERROR: column "no_column" of relation "ft1" does not exist ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column; NOTICE: column "no_column" of relation "ft1" does not exist, skipping ALTER FOREIGN TABLE ft1 DROP COLUMN c9; +ALTER FOREIGN TABLE ft1 ADD COLUMN c11 serial; ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema; ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR ERROR: relation "ft1" does not exist ALTER FOREIGN TABLE foreign_schema.ft1 SET TABLESPACE ts; -- ERROR ERROR: "ft1" is not a table, materialized view, index, or partitioned index +ALTER SEQUENCE foreign_schema.ft1_c11_seq SET SCHEMA public; -- ERROR +ERROR: cannot move an owned sequence into another schema +DETAIL: Sequence "ft1_c11_seq" is linked to table "ft1". ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1; ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; \d foreign_schema.foreign_table_1 - Foreign table "foreign_schema.foreign_table_1" - Column | Type | Collation | Nullable | Default | FDW options -------------------+---------+-----------+----------+---------+-------------------------------- - foreign_column_1 | integer | | not null | | ("param 1" 'val1') - c2 | text | | | | (param2 'val2', param3 'val3') - c3 | date | | | | - c4 | integer | | | 0 | - c5 | integer | | | | - c6 | integer | | not null | | - c7 | integer | | | | (p1 'v1', p2 'v2') - c8 | text | | | | (p2 'V2') - c10 | integer | | | | (p1 'v1') + Foreign table "foreign_schema.foreign_table_1" + Column | Type | Collation | Nullable | Default | FDW options +------------------+---------+-----------+----------+-------------------------------------------------+-------------------------------- + foreign_column_1 | integer | | not null | | ("param 1" 'val1') + c2 | text | | | | (param2 'val2', param3 'val3') + c3 | date | | | | + c4 | integer | | | 0 | + c5 | integer | | | | + c6 | integer | | not null | | + c7 | integer | | | | (p1 'v1', p2 'v2') + c8 | text | | | | (p2 'V2') + c10 | integer | | | | (p1 'v1') + c11 | integer | | not null | nextval('foreign_schema.ft1_c11_seq'::regclass) | Check constraints: "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql index 279786f4bf..ae0e6fb4b8 100644 --- a/src/test/regress/sql/foreign_data.sql +++ b/src/test/regress/sql/foreign_data.sql @@ -406,9 +406,11 @@ ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column; ALTER FOREIGN TABLE ft1 DROP COLUMN c9; +ALTER FOREIGN TABLE ft1 ADD COLUMN c11 serial; ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema; ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR ALTER FOREIGN TABLE foreign_schema.ft1 SET TABLESPACE ts; -- ERROR +ALTER SEQUENCE foreign_schema.ft1_c11_seq SET SCHEMA public; -- ERROR ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1; ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; \d foreign_schema.foreign_table_1 -- 2.11.4.GIT