Thread: BUG #18407: ALTER TABLE SET SCHEMA on foreign table with SERIAL column does not move sequence to new schema

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

PG Bug reporting form <noreply@postgresql.org> writes:
> 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.

Hmm ... that does seem fairly inconsistent.  It looks like the cause
is in AlterTableNamespaceInternal:

    /* 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);
    }

It's probably reasonable to have this relkind gating for 
AlterIndexNamespaces, although I'm not sure it saves much to
skip that, either.  But we can have sequences attached to
foreign tables, and I think maybe constraints as well.
Maybe it'd be best to just drop this relkind check altogether?

            regards, tom lane

On 25.03.24 17:17, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
>> 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.
> 
> Hmm ... that does seem fairly inconsistent.  It looks like the cause
> is in AlterTableNamespaceInternal:
> 
>      /* 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);
>      }
> 
> It's probably reasonable to have this relkind gating for
> AlterIndexNamespaces, although I'm not sure it saves much to
> skip that, either.  But we can have sequences attached to
> foreign tables, and I think maybe constraints as well.
> Maybe it'd be best to just drop this relkind check altogether?

Yes, seems better to remove the relkind check.

Peter Eisentraut <peter@eisentraut.org> writes:
> On 25.03.24 17:17, Tom Lane wrote:
>> Maybe it'd be best to just drop this relkind check altogether?

> Yes, seems better to remove the relkind check.

Done that way.

            regards, tom lane