Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19407: pg_dump : DROP RULE creates forward references
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: dominik.hirt@hub28.de
Reply-To: dominik.hirt@hub28.de, pgsql-bugs@lists.postgresql.org
Date: Fri, 13 Feb 2026 09:02:37 +0000
Message-ID: <19407-0c327e0fc912b451@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: <https://www.postgresql.org/message-id/19407-0c327e0fc912b451%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19407
Logged by:          Dominik Hirt
Email address:      dominik.hirt@hub28.de
PostgreSQL version: 18.1
Operating system:   Linux / MacOS
Description:       =20

During dump / restore with pg_dump / psql, I encounter a dependency ordering
issue that breaks the import when using ON_ERROR_STOP=3Don.

Problem: pg_dump executes DROP RULE for specific views, forcing them to be
recreated before their dependencies (custom types, collations) exist in the
dump file, causing import failures.

pg_dump (PostgreSQL) 18.1 (Debian 18.1-1.pgdg11+2)
psql (PostgreSQL) 18.1 (Homebrew)
(but seen since v16)

Here is a minimal reproducible example consisting of

    shell script 'reproduce.sh' for createDB -> import schema -> export
schema -> import again -> ERROR
    minimal SQL schema definition 'minimal_schema_en.sql'

reproduce.sh
```
createdb repro
psql repro < minimal_schema_en.sql
pg_dump --clean --if-exists --no-owner --schema-only -f minimal_dump.sql
repro
createdb repro_import
psql --set ON_ERROR_STOP=3Don repro_import < minimal_dump.sql
```

minimal_schema_en.sql
```
--
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D
-- Minimal schema to reproduce the pg_dump --clean bug (PostgreSQL 18)
--
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D
--
-- BUG:
--   pg_dump --clean generates a view placeholder (CREATE OR REPLACE VIEW
--   ... AS SELECT NULL::public.procurement_status) BEFORE the DROP/CREATE
TYPE.
--   When importing into a fresh DB this fails with:
--     ERROR:  type "public.procurement_status" does not exist
--
-- REPRODUCTION:
--   createdb repro && psql repro < minimal_schema.sql
--   pg_dump --clean --if-exists --no-owner --schema-only -f dump.sql repro
--   createdb repro_import && psql --set ON_ERROR_STOP=3Don repro_import <
dump.sql
--   --> ERROR:  type "public.procurement_status" does not exist
--
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D

CREATE TYPE public.procurement_status AS ENUM (
    'ORDERED',
    'DELIVERED'
);

CREATE TABLE public.procurement_order_confirmation (
    id integer NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    order_id integer,
    delivery_date date,
    delivery_week character varying,
    freight_cost numeric,
    packaging_cost numeric,
    certificate_cost numeric,
    other_cost numeric,
    delivery_location_id integer,
    date date,
    status public.procurement_status,
    created_at timestamp without time zone DEFAULT now() NOT NULL
);

CREATE TABLE public.procurement_order_confirmation_item (
    id integer NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    order_confirmation_id integer NOT NULL,
    order_item_id integer NOT NULL,
    quantity numeric,
    unit_price numeric,
    discount_percent numeric
);

CREATE TABLE public.procurement_order (
    id serial PRIMARY KEY,
    project_id integer NOT NULL,
    number character varying(32),
    delivery_date date,
    delivery_week character varying(5),
    freight_cost numeric,
    packaging_cost numeric,
    certificate_cost numeric,
    other_cost numeric,
    remark character varying(1024),
    supplier_contact_id integer,
    external_id character varying(40),
    remark_supplier character varying(1024),
    status public.procurement_status,
    quote_id integer,
    delivery_location_id integer,
    email character varying,
    ancillary_services character varying,
    warranty character varying,
    reorder_until character varying,
    incoterm character varying,
    incoterm_addon character varying,
    contact_person_id integer,
    payment_terms text,
    created_at timestamp without time zone DEFAULT now() NOT NULL
);

CREATE TABLE public.procurement_order_item (
    id serial PRIMARY KEY,
    order_id integer NOT NULL REFERENCES public.procurement_order(id) ON
DELETE CASCADE,
    quantity numeric,
    unit_price numeric,
    unit_price_corrected numeric,
    discount_percent numeric,
    order_quantity numeric,
    planning_component_id integer
);

CREATE TABLE public.supplier (
    id serial PRIMARY KEY,
    company character varying(200)
);

CREATE TABLE public.supplier_contact (
    id serial PRIMARY KEY,
    company_id integer REFERENCES public.supplier(id) ON DELETE CASCADE,
    name character varying(200),
    email character varying(200)
);

ALTER TABLE ONLY public.procurement_order
    ADD CONSTRAINT fk_order_supplier_contact
    FOREIGN KEY (supplier_contact_id) REFERENCES
public.supplier_contact(id);

CREATE TABLE public.goods_receipt_item (
    id integer NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    originator_order_item_id integer REFERENCES
public.procurement_order_item(id) ON DELETE CASCADE,
    delivered_quantity numeric,
    status public.procurement_status,
    parent_id integer REFERENCES public.goods_receipt_item(id) ON DELETE
CASCADE
);

ALTER TABLE ONLY public.procurement_order_confirmation
    ADD CONSTRAINT fk_oc_order
    FOREIGN KEY (order_id) REFERENCES public.procurement_order(id) ON DELETE
CASCADE;

ALTER TABLE ONLY public.procurement_order_confirmation_item
    ADD CONSTRAINT fk_oci_oc
    FOREIGN KEY (order_confirmation_id) REFERENCES
public.procurement_order_confirmation(id) ON DELETE CASCADE;

ALTER TABLE ONLY public.procurement_order_confirmation_item
    ADD CONSTRAINT fk_oci_oi
    FOREIGN KEY (order_item_id) REFERENCES public.procurement_order_item(id)
ON DELETE CASCADE;

-- View with correlated subquery on procurement_order (o.id)
-- This structure forces view-splitting in pg_dump --clean
CREATE OR REPLACE VIEW public.view_order AS
SELECT
    o.id,
    o.project_id,
    o.number,
    o.supplier_contact_id,
    o.external_id,
    o.remark_supplier,
    o.quote_id,
    o.ancillary_services,
    o.warranty,
    o.reorder_until,
    o.incoterm,
    o.incoterm_addon,
    o.contact_person_id,
    o.payment_terms,
    o.created_at,
    o.remark,
    COALESCE(oc.delivery_date, o.delivery_date) AS delivery_date,
    COALESCE(oc.delivery_week, o.delivery_week) AS delivery_week,
    COALESCE(oc.freight_cost, o.freight_cost) AS freight_cost,
    COALESCE(oc.packaging_cost, o.packaging_cost) AS packaging_cost,
    COALESCE(oc.certificate_cost, o.certificate_cost) AS certificate_cost,
    COALESCE(oc.other_cost, o.other_cost) AS other_cost,
    COALESCE(oc.delivery_location_id, o.delivery_location_id) AS
delivery_location_id,
    oc.date AS order_confirmation_date,
    oc.status AS status_order_confirmation,
    sc.name AS supplier_contact_name,
    sc.email,
    s.company,
    -- Correlated subquery: computes order status based on goods receipt
    (SELECT
        CASE
            WHEN sub.total_items =3D 0 THEN
'ORDERED'::public.procurement_status
            WHEN sub.delivered =3D sub.total_items THEN
'DELIVERED'::public.procurement_status
            ELSE 'ORDERED'::public.procurement_status
        END
     FROM (
        SELECT count(*) AS total_items,
               sum(CASE WHEN gr.status =3D
'DELIVERED'::public.procurement_status THEN 1 ELSE 0 END) AS delivered
        FROM public.procurement_order_item oi2
        LEFT JOIN public.goods_receipt_item gr
            ON gr.originator_order_item_id =3D oi2.id
        WHERE oi2.order_id =3D o.id  -- correlated reference to outer o.id
     ) sub
    ) AS status_order,
    sum(COALESCE(oci.quantity, oi.order_quantity, oi.quantity)) AS
total_quantity,
    sum(COALESCE(oci.quantity, oi.order_quantity, oi.quantity) *
COALESCE(oci.unit_price, oi.unit_price_corrected, oi.unit_price)
        * (1 - COALESCE(oci.discount_percent, oi.discount_percent, 0) /
100))
        + COALESCE(o.freight_cost, 0) + COALESCE(o.packaging_cost, 0)
        + COALESCE(o.certificate_cost, 0) + COALESCE(o.other_cost, 0) AS
order_total_amount
FROM public.procurement_order o
JOIN public.procurement_order_item oi ON o.id =3D oi.order_id
LEFT JOIN public.procurement_order_confirmation oc ON o.id =3D oc.order_id
LEFT JOIN public.procurement_order_confirmation_item oci ON oi.id =3D
oci.order_item_id
LEFT JOIN public.supplier_contact sc ON sc.id =3D o.supplier_contact_id
LEFT JOIN public.supplier s ON s.id =3D sc.company_id
GROUP BY o.id, oc.id, sc.id, s.id;
```