During automated database deployment with pg_dump/pg_restore, I encounter a dependency ordering issue that breaks CI/CD pipelines when using ON_ERROR_STOP=on.

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.

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=on repro_import < minimal_dump.sql

minimal_schema_en.sql

-- ============================================================================
-- Minimal schema to reproduce the pg_dump --clean bug (PostgreSQL 18)
-- ============================================================================
--
-- 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=on repro_import < dump.sql
--   --> ERROR:  type "public.procurement_status" does not exist
-- ============================================================================

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 = 0 THEN 'ORDERED'::public.procurement_status
            WHEN sub.delivered = 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 = '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 = oi2.id
        WHERE oi2.order_id = 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 = oi.order_id
LEFT JOIN public.procurement_order_confirmation oc ON o.id = oc.order_id
LEFT JOIN public.procurement_order_confirmation_item oci ON oi.id = oci.order_item_id
LEFT JOIN public.supplier_contact sc ON sc.id = o.supplier_contact_id
LEFT JOIN public.supplier s ON s.id = sc.company_id
GROUP BY o.id, oc.id, sc.id, s.id;

The original question was CLOSED by Stackoverflow due to not about programming or software development ?!

see DROP RULE references non-existing objects