Migration to identity columns on PostgreSQL, but without privileges to modify pg_depend

Question

On preparation to the release of Doctrine DBAL 4, I want to migrate the identity column of my autoincrement based tables to "serial" type columns, as described here:

Migration to identity columns on PostgreSQL

There is a nice script included and everything.

The problem is that this script runs this:

-- change the dependency between column and sequence to internal
  UPDATE pg_depend
    SET deptype = 'i'
    WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
      AND deptype = 'a';

And since my db is hosted as managed database on DigitalOcean, my doadmin user doesn't have enough privileges to modify this table directly:

ERROR:  permission denied for table pg_depend
CONTEXT:  SQL statement "UPDATE pg_depend
   SET deptype = 'i'
   WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
     AND deptype = 'a'"
PL/pgSQL function upgrade_serial_to_identity(regclass,name) line 31 at SQL statement

How can I perform this migration, without having access to change this user's privileges or this table permissions?

Answer 1

Modifying catalog tables like that is dangerous and likely to lead to data corruption. Why so complicated, if there is a fast and simple way to do it?

Given a table like this:

\d s
                            Table "laurenz.s"
 Column │  Type   │ Collation │ Nullable │            Default            
════════╪═════════╪═══════════╪══════════╪═══════════════════════════════
 id     │ integer │           │ not null │ nextval('s_id_seq'::regclass)
Indexes:
    "s_pkey" PRIMARY KEY, btree (id)

The quick and clean way to switch to using an identity column is the following:

-- make the change atomic
BEGIN;

-- prevent concurrent access to the table
LOCK s;

-- get the current sequence value
SELECT last_value FROM s_id_seq;

 last_value 
════════════
       1000
(1 row)

-- get rid of the sequence
DROP SEQUENCE s_id_seq CASCADE;
NOTICE:  drop cascades to default value for column id of table s

-- turn "id" into an identity column, start the sequence a little higher
ALTER TABLE s ALTER id ADD GENERATED ALWAYS AS IDENTITY (START 1100);

-- done
COMMIT;

Now the table is fine:

\d s
                           Table "laurenz.s"
 Column │  Type   │ Collation │ Nullable │           Default            
════════╪═════════╪═══════════╪══════════╪══════════════════════════════
 id     │ integer │           │ not null │ generated always as identity
Indexes:
    "s_pkey" PRIMARY KEY, btree (id)

Answer 2

Using Laurenz's excellent advice in the other answer, I wrapped the whole thing into a function, so it's easier to update the couple dozens of tables in my applications.

Luckily the auto-increment ids were the minority!

CREATE OR REPLACE FUNCTION serial_to_identity(table_name text, column_name text)
    RETURNS void AS
$$
DECLARE
    -- will hold the max value found in the sequence
    max_id integer;

    -- sequence name
    seqence_name text := table_name || '_' || column_name || '_seq';
BEGIN

   
    EXECUTE format('LOCK %I;', table_name);

    -- get current maximum id
    EXECUTE format (
            'SELECT MAX(%I) FROM %I;',
            column_name,
            table_name
            ) INTO max_id;

    -- drop the sequence 
    EXECUTE format('DROP SEQUENCE IF EXISTS %I CASCADE;', seqence_name);

    -- alter table so it now uses an identity, instead of the previously seqence
    EXECUTE format (
            'ALTER TABLE %I ALTER COLUMN %I ADD GENERATED ALWAYS AS IDENTITY (START WITH %s)',
            table_name,
            column_name,
            max_id + 100
            );

END;
$$
    LANGUAGE plpgsql;

Now to update table xxx, with column id I just execute:

select serial_to_identity('xxx', 'id').

Seems to be working.