Migration to identity columns on PostgreSQL

As of version 4, the DBAL uses identity columns to implement the autoincrement behavior on PostgreSQL instead of SERIAL* column types.

If you have a database with autoincrement columns created using DBAL 3 or earlier, you will need to perform the following schema migration before being able to continue managing the schema with the DBAL:

1. Identify all autoincrement columns and their tables.

2. Create the upgrade_serial_to_identity() function in the database as described in PostgreSQL 10 identity columns explained <https://www.enterprisedb.com/blog/postgresql-10-identity-columns-explained>_:

   .. code-block:: sql

   CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(tbl regclass, col name) RETURNS void LANGUAGE plpgsql AS $$ DECLARE colnum smallint; seqid oid; count int; BEGIN -- find column number SELECT attnum INTO colnum FROM pg_attribute WHERE attrelid = tbl AND attname = col; IF NOT FOUND THEN RAISE EXCEPTION 'column does not exist'; END IF;

   Copy

    -- find sequence
       SELECT INTO seqid objid
      FROM pg_depend
      WHERE (refclassid, refobjid, refobjsubid) = ('pg_class'::regclass, tbl, colnum)
        AND classid = 'pg_class'::regclass AND objsubid = 0
        AND deptype = 'a';
   
    GET DIAGNOSTICS count = ROW_COUNT;
    IF count < 1 THEN
      RAISE EXCEPTION 'no linked sequence found';
    ELSIF count > 1 THEN
      RAISE EXCEPTION 'more than one linked sequence found';
    END IF;
   
    -- drop the default
    EXECUTE 'ALTER TABLE ' || tbl || ' ALTER COLUMN ' || quote_ident(col) || ' DROP DEFAULT';
   
    -- 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';
   
    -- mark the column as identity column
    UPDATE pg_attribute
      SET attidentity = 'd'
      WHERE attrelid = tbl
        AND attname = col;
   

   END; $$;

3. Run the function for each table like this:

   .. code-block:: sql

   SELECT upgrade_serial_to_identity('article', 'id');

Without this migration, next time when DBAL 4 is used to manage the schema, it will perform a similar migration but instead of reusing the existing sequence, it will drop it and create a new one. As a result, all new sequence numbers will be generated from 1, which is most likely undesired.