public inbox for [email protected]
help / color / mirror / Atom feedBUG #19407: pg_dump : DROP RULE creates forward references
2+ messages / 2 participants
[nested] [flat]
* BUG #19407: pg_dump : DROP RULE creates forward references
@ 2026-02-13 09:02 PG Bug reporting form <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-02-13 09:02 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
The following bug has been logged on the website:
Bug reference: 19407
Logged by: Dominik Hirt
Email address: [email protected]
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;
```
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19407: pg_dump : DROP RULE creates forward references
@ 2026-02-13 21:00 Nathan Bossart <[email protected]>
parent: PG Bug reporting form <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Nathan Bossart @ 2026-02-13 21:00 UTC (permalink / raw)
To: [email protected]; [email protected]
On Fri, Feb 13, 2026 at 09:02:37AM +0000, PG Bug reporting form wrote:
> 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.
I think this is an even simpler reproducer:
create type t as enum ('a', 'b');
create table tt (a t primary key, b text);
create view v as select * from tt group by a;
Commit d8c05aff56 [0] did some work in this area, but this issue may even
predate that. In any case, it seems like pg_dump needs to create the type
before it creates the dummy view to deal with circular dependencies.
[0] https://postgr.es/m/flat/19092.1479325184%40sss.pgh.pa.us
--
nathan
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-02-13 21:00 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-13 09:02 BUG #19407: pg_dump : DROP RULE creates forward references PG Bug reporting form <[email protected]>
2026-02-13 21:00 ` Nathan Bossart <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox