Hi,
I found a pg_dump/restore failure involving inherited columns and identity columns.
Consider the following schema:
CREATE SCHEMA orchestrator;
CREATE SCHEMA tasks;
CREATE SEQUENCE orchestrator.task_id_seq AS integer;
CREATE TABLE orchestrator.task (
id integer NOT NULL DEFAULT nextval('orchestrator.task_id_seq'::regclass)
);
CREATE TABLE tasks.kis05_formation_act_by_contract (
id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
child_id integer NOT NULL
) INHERITS (orchestrator.task);
The child table definition merges the local id column with the inherited one, producing:
NOTICE: merging column "id" with inherited definition
A schema-only dump currently emits the child table roughly as:
CREATE TABLE tasks.kis05_formation_act_by_contract (
id integer DEFAULT nextval('orchestrator.task_id_seq'::regclass) NOT NULL,
child_id integer NOT NULL
)
INHERITS (orchestrator.task);
ALTER TABLE tasks.kis05_formation_act_by_contract
ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (...);
Restoring this dump fails with:
ERROR: column "id" of relation "kis05_formation_act_by_contract"
already has a default value
The failure happens because the inherited/default expression is emitted as part of the CREATE TABLE, and then pg_dump emits ALTER COLUMN ... ADD GENERATED ... AS IDENTITY for the identity sequence. However, ADD GENERATED cannot be applied while the column has a default.
The attached patch makes pg_dump emit ALTER COLUMN ... DROP DEFAULT before
ALTER COLUMN ... ADD GENERATED ... AS IDENTITY, but only when pg_dump knows
that the owning column has a default expression.
With the patch, the dump becomes restorable:
ALTER TABLE tasks.kis05_formation_act_by_contract
ALTER COLUMN id DROP DEFAULT;
ALTER TABLE tasks.kis05_formation_act_by_contract
ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (...);
This is a minimal fix for the invalid dump. I considered whether pg_dump
should instead emit the identity clause inline in the CREATE TABLE, but that
would require a larger change in how identity sequences are dumped. The
attached patch keeps the current structure and only removes the conflicting
default before adding the identity property.
A regression test is included for the pg_dump output and restore path.
Comments are welcome.
--
Antuan Violin, PostgresPro
