Re: Switching identity column to serial

> On 04/02/2023 05:41 CET Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>
> On 2/3/23 18:54, Erik Wienhold wrote:
>
> > I was wondering if it's possible to drop a column identity (not the column
> > itself) while keeping the attached sequence. This would avoid recreating
> > an identical sequence (especially with a correct start value and owner).
>
> Why doesn't this work?
> BEGIN;
> DROP SEQUENCE t_id;
> CREATE SEQUENCE new_t_id_seq AS INTEGER OWNED BY t.id;
> ALTER SEQUENCE new_t_id_seq OWNER TO new_owner;
> SELECT setval('new_t_id', (SELECT MAX(id) FROM t));
> SELECT nextval('new_t_id');
> COMMIT;

This should work but I want to preserve the existing sequence instead of
re-creating it with the same properties. That's why I was looking for a
shortcut (also code golfing and sheer curiosity).

I haven't thought about using setval but I would need to dynamically
generate the CREATE SEQUENCE anyway to preserve the old sequence definition
with info from pg_sequence. I assume the sequences were created with
default settings, e.g. cache 1, no cycle. But I haven't checked the ~100
affected sequences in detail.

Also setting the current value to max(id) is not the same as preserving the
sequence state which may be past max(id) if rows were deleted in the
meantime. We log DML in audit tables and record the ID of deleted rows.
Therefore I don't want sequences to generate previous values. This can be
handled by getting nextval from the old sequence before dropping it and
using that as start for the new sequence.

> > Changing the sequence owner to NONE before dropping identity is not allowed.
> > Also changing pg_class.relowner to some role did not help. The sequence is
> > still dropped together with the column identity.
>
> Manually diigging around the system catalog is never recommended.

I had the idea from relocating PostGIS a few weeks ago which describes
setting pg_extension.extrelocatable = true. [1]

Now I also checked the implementation of DROP IDENTITY on 12.13 and 15.1:

1. check column attributes
2. clear pg_attribute.attidentity
3. invoke post alter hook (it's a no-op without sepgsql)
4. drop sequence

My approach is identical to steps 1 and 2. Of course future releases may
change that.

[1] https://www.postgis.net/2017/11/07/tip-move-postgis-schema/

--
Erik