From: Erik Wienhold <ewie@ewie.name>
To: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Switching identity column to serial
Date: Sat, 4 Feb 2023 21:55:52 +0100 (CET)
Message-ID: <2023065411.42133.1675544152872@office.mailbox.org> (raw)
In-Reply-To: <0400e92f-0d77-4276-be50-6e16321f4ae0@gmail.com>
References: <1828022907.15226.1675472079824@office.mailbox.org>
<0400e92f-0d77-4276-be50-6e16321f4ae0@gmail.com>
> On 04/02/2023 05:41 CET Ron <ronljohnsonjr@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ewie@ewie.name, ronljohnsonjr@gmail.com
Subject: Re: Switching identity column to serial
In-Reply-To: <2023065411.42133.1675544152872@office.mailbox.org>
References: <1828022907.15226.1675472079824@office.mailbox.org> <0400e92f-0d77-4276-be50-6e16321f4ae0@gmail.com> <2023065411.42133.1675544152872@office.mailbox.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox