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;

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

Migrating on managed PostgreSQL services (AWS RDS, Cloud SQL, Azure, Supabase, Neon)
------------------------------------------------------------------------------------

The function above writes directly to ``pg_depend`` and ``pg_attribute``. These
catalog updates require PostgreSQL superuser privileges, which managed services
typically do not grant to the database's master / admin user. On those services
the function fails with:

.. code-block:: text

  ERROR:  permission denied for table pg_depend

Use the variant below instead. It produces the same end state - the column is
``GENERATED BY DEFAULT AS IDENTITY``, an attached sequence positioned at the next
value the original sequence would have emitted - but uses only standard DDL
(``DROP SEQUENCE`` + ``ALTER TABLE … ADD GENERATED BY DEFAULT AS IDENTITY``).
The trade-off is that the original sequence object is dropped and PostgreSQL
creates a fresh internal sequence; only ownership of the table and sequence is
required, no superuser.

.. code-block:: sql

  CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(tbl regclass, col name)
  RETURNS void
  LANGUAGE plpgsql
  AS $$
  DECLARE
    seq_name regclass;
    next_val bigint;
  BEGIN
    -- already identity? nothing to do
    IF EXISTS (
      SELECT 1 FROM pg_attribute
      WHERE attrelid = tbl AND attname = col AND attidentity IN ('a', 'd')
    ) THEN
      RETURN;
    END IF;

    -- find the sequence linked via the column's DEFAULT
    SELECT pg_get_serial_sequence(tbl::text, col)::regclass INTO seq_name;
    IF seq_name IS NULL THEN
      RAISE EXCEPTION 'no linked sequence found for %.%', tbl, col;
    END IF;

    -- capture the next value the sequence would emit
    EXECUTE format('SELECT CASE WHEN is_called THEN last_value + 1 ELSE last_value END FROM %s', seq_name)
      INTO next_val;

    -- swap SERIAL for IDENTITY via standard DDL
    EXECUTE format('ALTER TABLE %s ALTER COLUMN %I DROP DEFAULT', tbl, col);
    EXECUTE format('DROP SEQUENCE %s', seq_name);
    EXECUTE format(
      'ALTER TABLE %s ALTER COLUMN %I ADD GENERATED BY DEFAULT AS IDENTITY (START WITH %s)',
      tbl, col, next_val
    );
  END;
  $$;
