Thread: Switching identity column to serial

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).

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.

But I managed it by clearing pg_attribute.attidentity.  See the following
psql session:

    test=# create table t (id int generated always as identity, x char);
    CREATE TABLE

    test=# insert into t (x) values ('a'), ('b') returning *;
     id | x
    ----+---
      1 | a
      2 | b
    (2 rows)

    INSERT 0 2

    test=# select pg_get_serial_sequence('t', 'id');
     pg_get_serial_sequence
    ------------------------
     public.t_id_seq
    (1 row)

    test=# update pg_attribute set attidentity = '' where (attrelid, attname) = ('t'::regclass, 'id');
    UPDATE 1

    test=# alter table t alter id drop identity;
    psql:1.sql:6: ERROR:  column "id" of relation "t" is not an identity column

    test=# select pg_get_serial_sequence('t', 'id');
     pg_get_serial_sequence
    ------------------------
     public.t_id_seq
    (1 row)

    test=# alter table t alter id set default nextval('t_id_seq');
    ALTER TABLE

    test=# insert into t (x) values ('c'), ('d') returning *;
     id | x
    ----+---
      3 | c
      4 | d
    (2 rows)

    INSERT 0 2

    test=# insert into t (id, x) values (-1, 'e') returning *;
     id | x
    ----+---
     -1 | e
    (1 row)

    INSERT 0 1

    test=# select * from t;
     id | x
    ----+---
      1 | a
      2 | b
      3 | c
      4 | d
     -1 | e
    (5 rows)

Is this sufficient or am I missing some detail and messing around with
pg_catalog is not enough (in addition to being risky)?

Some context:

I have to change identity columns to a form that resembles a definition as
serial.  Creating a new column and migrating the primary key constraint is
not an option.

Why is this change necessary?

My team is importing data with QGIS which fails to properly handle identity
columns.  QGIS uses INSERT with OVERRIDING SYSTEM VALUE but tries to insert
NULL although identity columns imply NOT NULL (also it's the primary key).
QGIS tries to generate an ID with nextval but does not use the qualified
sequence name although search_path does not contain the namespace.  It's
weird that QGIS thinks that it should generate the ID instead of delegating
this to the database, yet it uses RETURNING id.  Maybe it needs the ID in
advance for reference.  I don't know.

The "serial" style with nextval as column default works as expected.
Probably because QGIS just uses the column default expression which should
reference the correct sequence.  Oh, did I mention yet that QGIS generates
the ID before issuing an INSERT with RETURNING id?

I'll still open a bug ticket with QGIS but right now there's no other way
than ditching identity columns.

--
Erik

> On 04/02/2023 01:54 CET Erik Wienhold <ewie@ewie.name> 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).
>
> 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.
>
> But I managed it by clearing pg_attribute.attidentity.  See the following
> psql session:

Forgot to mention: tested on 12.13 and 15.1.

>
>     test=# create table t (id int generated always as identity, x char);
>     CREATE TABLE
>
>     test=# insert into t (x) values ('a'), ('b') returning *;
>      id | x
>     ----+---
>       1 | a
>       2 | b
>     (2 rows)
>
>     INSERT 0 2
>
>     test=# select pg_get_serial_sequence('t', 'id');
>      pg_get_serial_sequence
>     ------------------------
>      public.t_id_seq
>     (1 row)
>
>     test=# update pg_attribute set attidentity = '' where (attrelid, attname) = ('t'::regclass, 'id');
>     UPDATE 1
>
>     test=# alter table t alter id drop identity;
>     psql:1.sql:6: ERROR:  column "id" of relation "t" is not an identity column
>
>     test=# select pg_get_serial_sequence('t', 'id');
>      pg_get_serial_sequence
>     ------------------------
>      public.t_id_seq
>     (1 row)
>
>     test=# alter table t alter id set default nextval('t_id_seq');
>     ALTER TABLE
>
>     test=# insert into t (x) values ('c'), ('d') returning *;
>      id | x
>     ----+---
>       3 | c
>       4 | d
>     (2 rows)
>
>     INSERT 0 2
>
>     test=# insert into t (id, x) values (-1, 'e') returning *;
>      id | x
>     ----+---
>      -1 | e
>     (1 row)
>
>     INSERT 0 1
>
>     test=# select * from t;
>      id | x
>     ----+---
>       1 | a
>       2 | b
>       3 | c
>       4 | d
>      -1 | e
>     (5 rows)
>
> Is this sufficient or am I missing some detail and messing around with
> pg_catalog is not enough (in addition to being risky)?
>
> Some context:
>
> I have to change identity columns to a form that resembles a definition as
> serial.  Creating a new column and migrating the primary key constraint is
> not an option.
>
> Why is this change necessary?
>
> My team is importing data with QGIS which fails to properly handle identity
> columns.  QGIS uses INSERT with OVERRIDING SYSTEM VALUE but tries to insert
> NULL although identity columns imply NOT NULL (also it's the primary key).
> QGIS tries to generate an ID with nextval but does not use the qualified
> sequence name although search_path does not contain the namespace.  It's
> weird that QGIS thinks that it should generate the ID instead of delegating
> this to the database, yet it uses RETURNING id.  Maybe it needs the ID in
> advance for reference.  I don't know.
>
> The "serial" style with nextval as column default works as expected.
> Probably because QGIS just uses the column default expression which should
> reference the correct sequence.  Oh, did I mention yet that QGIS generates
> the ID before issuing an INSERT with RETURNING id?
>
> I'll still open a bug ticket with QGIS but right now there's no other way
> than ditching identity columns.
>
> --
> Erik

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;

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.

On 2/3/23 22:41, Ron 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;

Note: this was not tested...

-- 
Born in Arizona, moved to Babylonia.

> 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

On 04.02.23 21:55, Erik Wienhold wrote:
>>   Why doesn't this work?
>>   BEGIN;
>>   DROP SEQUENCE t_id;

[This won't work, you need to use ALTER TABLE / DROP IDENTITY.]

>>   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).

This is possible in principle, by implementing the inverse of the 
"Upgrading" recipe from 
<https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/>. 
But if you don't want to figure that out, I think dropping and 
recreating the sequences as suggested here seems the best solution.