Does RENAME TABLE rename associated identity sequence?

Hi Jason,

On Wed, Apr 23, 2025 at 6:06 PM Jason Song <pidaoh@g.skku.edu> wrote:

Hi hackers,

I was wondering if there's any built-in functionality in PostgreSQL where
renaming a table with an identity column would also rename the
auto-generated sequence associated with that identity column.

In my case, I renamed a table that used `GENERATED BY DEFAULT AS
IDENTITY`, and later when I ran `pg_dump`, I noticed that the sequence name
was unchanged (e.g., still `old_table_id_seq`). As a result, any `setval()`
or sequence-related operations referenced the old sequence name, even
though the table name had changed.

Is it causing a problem in your application or environment?

As long as the system uses the given name consistently and does not cause
any error due to trying to derive sequence name from new table name and
ending up in non-existent sequence error, it should be fine. Internally we
use OID and not name. Identity sequences are not expected to be manipulated
by users anyway, so the name shouldn't matter.

I agree that finding an identity sequence associated with a table is not
straightforward - that association is stored in pg_depends, which is not
intuitive. Take a look at getIdentitySequence().

I realize this can be worked around — for example, by using
`--exclude-table-data` to skip the `setval()` or manually renaming the
sequence after the table rename. But I'm curious if there are any plans (or
technical reasons against) supporting something like `ALTER TABLE ...
RENAME ... WITH SEQUENCE`, or having the sequence name automatically follow
the table rename when it was originally auto-generated by an identity
column.

If there's any problem, IMO, ALTER TABLE ... RENAME ... should rename the
sequence too since the identity sequences are created implicitly when the
table is created, so they should be renamed implicitly. We should not
require WITH SEQUENCE clause.

--
Best Wishes,
Ashutosh Bapat

On Thu, 24 Apr 2025 at 05:53, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
wrote:

If there's any problem, IMO, ALTER TABLE ... RENAME ... should rename the
sequence too since the identity sequences are created implicitly when the
table is created, so they should be renamed implicitly. We should not
require WITH SEQUENCE clause.

My concern would be what happens if the new sequence name is not available.
I suppose the simplest behaviour might be to skip renaming the sequence in
that case, perhaps raising a warning.

Isaac Morland <isaac.morland@gmail.com> writes:

On Thu, 24 Apr 2025 at 05:53, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
wrote:

If there's any problem, IMO, ALTER TABLE ... RENAME ... should rename the
sequence too since the identity sequences are created implicitly when the
table is created, so they should be renamed implicitly. We should not
require WITH SEQUENCE clause.

My concern would be what happens if the new sequence name is not available.
I suppose the simplest behaviour might be to skip renaming the sequence in
that case, perhaps raising a warning.

We do not rename any other subsidiary objects such as indexes.
Why would we rename a sequence (which has a lot more reason
to be considered an independent object than an index does)?

regression=# create table foo (i int primary key);
CREATE TABLE
regression=# \d+ foo
Table "public.foo"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
i | integer | | not null | | plain | | |
Indexes:
"foo_pkey" PRIMARY KEY, btree (i)
Not-null constraints:
"foo_i_not_null" NOT NULL "i"
Access method: heap

regression=# alter table foo rename to bar;
ALTER TABLE
regression=# \d+ bar
Table "public.bar"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
i | integer | | not null | | plain | | |
Indexes:
"foo_pkey" PRIMARY KEY, btree (i)
Not-null constraints:
"foo_i_not_null" NOT NULL "i"
Access method: heap

I think it's up to the user to rename subsidiary objects if
they wish to do so.

regards, tom lane

On Thu, Apr 24, 2025 at 11:10 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

I think it's up to the user to rename subsidiary objects if
they wish to do so.

I absolutely agree.

--
Best Wishes,
Ashutosh Bapat

Hi all,

Thank you for the detailed discussion and clarifications.

I sincerely appreciate everyone's time and valuable insights shared in this
discussion.