You are browsing a version that has not yet been released. |
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:
- Identify all `autoincrement` columns and their tables.
-
1 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; $$; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 - For each column and their table, run `upgrade_serial_to_identity(<table>, <column>)`.
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.