BUG #19407: pg_dump : DROP RULE creates forward references

The following bug has been logged on the website:

Bug reference: 19407
Logged by: Dominik Hirt
Email address: dominik(dot)hirt(at)hub28(dot)de
PostgreSQL version: 18.1
Operating system: Linux / MacOS
Description:

During dump / restore with pg_dump / psql, I encounter a dependency ordering
issue that breaks the import 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.

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=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;
```