The following bug has been logged on the website:
Bug reference: 18407
Logged by: Vidushi Gupta
Email address: vidushi2504@gmail.com
PostgreSQL version: 16.0
Operating system: Linux
Description:
Hi,
I created a foreign table with a serial column and then moved the table to a
different schema. The sequence bound to the table column wasn't moved to the
new schema. I expected the sequence to be moved as well just like it works
for regular tables.
The documentation for ALTER FOREIGN TABLE SET SCHEMA doesn't mention
anything about objects related to foreign tables.
What is the expected behaviour here?
Steps to reproduce:
```
postgres=# create database d2;
CREATE DATABASE
postgres=# \c d2
You are now connected to database "d2" as user "postgres".
d2=# create table t(a serial);
CREATE TABLE
d2=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# create extension postgres_fdw;
CREATE EXTENSION
postgres=# create server postgres_2 foreign data wrapper postgres_fdw
options (dbname 'd2', host '127.0.0.1', port '5432');
create user mapping for current_user
server postgres_2 options (user 'postgres');
CREATE SERVER
CREATE USER MAPPING
postgres=# create foreign table t_ref (a serial) server postgres_2 options
(schema_name 'public', table_name 't');
CREATE FOREIGN TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+---------------+----------
public | t_ref | foreign table | postgres
public | t_ref_a_seq | sequence | postgres
(2 rows)
postgres=# create schema s;
CREATE SCHEMA
postgres=# alter table t_ref set schema s;
ALTER TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+----------+----------
public | t_ref_a_seq | sequence | postgres
(1 row)
postgres=# alter sequence t_ref_a_seq set schema s;
ERROR: cannot move an owned sequence into another schema
DETAIL: Sequence "t_ref_a_seq" is linked to table "t_ref".
postgres=#
```
And the same behaviour with ALTER FOREIGN TABLE
```
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+---------------+----------
public | t_ref | foreign table | postgres
public | t_ref_a_seq | sequence | postgres
(2 rows)
postgres=# alter foreign table t_ref set schema s;
ALTER FOREIGN TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+----------+----------
public | t_ref_a_seq | sequence | postgres
```
Thanks
