Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit fad3b5b

Browse filesBrowse files
tglsfdctglsfdc
committed
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
1 parent 7644a73 commit fad3b5b
Copy full SHA for fad3b5b

File tree

Expand file treeCollapse file tree

3 files changed

+24
-22
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+24
-22
lines changed

‎src/backend/commands/tablecmds.c

Copy file name to clipboardExpand all lines: src/backend/commands/tablecmds.c
+5-10Lines changed: 5 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -17860,16 +17860,11 @@ AlterTableNamespaceInternal(Relation rel, Oid oldNspOid, Oid nspOid,
1786017860
nspOid, false, false, objsMoved);
1786117861

1786217862
/* Fix other dependent stuff */
17863-
if (rel->rd_rel->relkind == RELKIND_RELATION ||
17864-
rel->rd_rel->relkind == RELKIND_MATVIEW ||
17865-
rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
17866-
{
17867-
AlterIndexNamespaces(classRel, rel, oldNspOid, nspOid, objsMoved);
17868-
AlterSeqNamespaces(classRel, rel, oldNspOid, nspOid,
17869-
objsMoved, AccessExclusiveLock);
17870-
AlterConstraintNamespaces(RelationGetRelid(rel), oldNspOid, nspOid,
17871-
false, objsMoved);
17872-
}
17863+
AlterIndexNamespaces(classRel, rel, oldNspOid, nspOid, objsMoved);
17864+
AlterSeqNamespaces(classRel, rel, oldNspOid, nspOid,
17865+
objsMoved, AccessExclusiveLock);
17866+
AlterConstraintNamespaces(RelationGetRelid(rel), oldNspOid, nspOid,
17867+
false, objsMoved);
1787317868

1787417869
table_close(classRel, RowExclusiveLock);
1787517870
}

‎src/test/regress/expected/foreign_data.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/foreign_data.out
+17-12Lines changed: 17 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -897,24 +897,29 @@ ERROR: column "no_column" of relation "ft1" does not exist
897897
ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column;
898898
NOTICE: column "no_column" of relation "ft1" does not exist, skipping
899899
ALTER FOREIGN TABLE ft1 DROP COLUMN c9;
900+
ALTER FOREIGN TABLE ft1 ADD COLUMN c11 serial;
900901
ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema;
901902
ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR
902903
ERROR: relation "ft1" does not exist
904+
ALTER SEQUENCE foreign_schema.ft1_c11_seq SET SCHEMA public; -- ERROR
905+
ERROR: cannot move an owned sequence into another schema
906+
DETAIL: Sequence "ft1_c11_seq" is linked to table "ft1".
903907
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
904908
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
905909
\d foreign_schema.foreign_table_1
906-
Foreign table "foreign_schema.foreign_table_1"
907-
Column | Type | Collation | Nullable | Default | FDW options
908-
------------------+---------+-----------+----------+---------+--------------------------------
909-
foreign_column_1 | integer | | not null | | ("param 1" 'val1')
910-
c2 | text | | | | (param2 'val2', param3 'val3')
911-
c3 | date | | | |
912-
c4 | integer | | | 0 |
913-
c5 | integer | | | |
914-
c6 | integer | | not null | |
915-
c7 | integer | | | | (p1 'v1', p2 'v2')
916-
c8 | text | | | | (p2 'V2')
917-
c10 | integer | | | | (p1 'v1')
910+
Foreign table "foreign_schema.foreign_table_1"
911+
Column | Type | Collation | Nullable | Default | FDW options
912+
------------------+---------+-----------+----------+-------------------------------------------------+--------------------------------
913+
foreign_column_1 | integer | | not null | | ("param 1" 'val1')
914+
c2 | text | | | | (param2 'val2', param3 'val3')
915+
c3 | date | | | |
916+
c4 | integer | | | 0 |
917+
c5 | integer | | | |
918+
c6 | integer | | not null | |
919+
c7 | integer | | | | (p1 'v1', p2 'v2')
920+
c8 | text | | | | (p2 'V2')
921+
c10 | integer | | | | (p1 'v1')
922+
c11 | integer | | not null | nextval('foreign_schema.ft1_c11_seq'::regclass) |
918923
Check constraints:
919924
"ft1_c2_check" CHECK (c2 <> ''::text)
920925
"ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date)

‎src/test/regress/sql/foreign_data.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/foreign_data.sql
+2Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -419,8 +419,10 @@ ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@');
419419
ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR
420420
ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column;
421421
ALTER FOREIGN TABLE ft1 DROP COLUMN c9;
422+
ALTER FOREIGN TABLE ft1 ADD COLUMN c11 serial;
422423
ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema;
423424
ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR
425+
ALTER SEQUENCE foreign_schema.ft1_c11_seq SET SCHEMA public; -- ERROR
424426
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1;
425427
ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
426428
\d foreign_schema.foreign_table_1

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.