BUG #14652: pg_dump: VIEW dumped as TABLE

From: martellilaurent(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14652: pg_dump: VIEW dumped as TABLE
Date: 2017-05-12 22:19:48
Message-ID: 20170512221948.1796.40707@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14652
Logged by: Laurent Martelli
Email address: martellilaurent(at)gmail(dot)com
PostgreSQL version: 9.4.12
Operating system: Dedian gnu-linux Jessie
Description:

Some views of are dumped as TABLE. And this is really annoying because it
has "REPLICA IDENTITY NOTHING" which is not understood if I try to restore
in 9.1.

Example:
coopener-v2_2017-03-16=# \d ael_invoice
View "public.ael_invoice"
Column | Type | Modifiers
-----------------+------------------------+-----------
id | bigint |
old_invoice_id | character varying(255) |
invoice_id | character varying(255) |
amount_wt | numeric(20,2) |
vat | numeric(20,2) |
is_paid | boolean |
due_date | date |
rum | bigint |
invoice_date | date |
invoice_name | character varying(255) |
status_name | character varying(255) |
payment_mode | character varying(255) |
reject_date | date |
amount_rejected | numeric(19,2) |
contract_line | bigint |
consumption_qty | numeric |
product | bigint |

Using pg_dump -t, all is fine:
$ pg_dump --schema-only -O -x -t ael_invoice coopener-v2_2017-03-16
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

--
-- Name: ael_invoice; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW ael_invoice AS
SELECT i.id,
i.old_invoice_id,
i.invoice_id,
i.ex_all_tax_total AS amount_wt,
i.vat_total AS vat,
i.balanced_memory_ok AS is_paid,
i.due_date,
i.rum,
i.invoice_date,
i.invoice_name,
status.name AS status_name,
pay_mode.name AS payment_mode,
i.reject_date,
i.amount_rejected,
i.contract_line,
sum(iline.qty) AS consumption_qty,
iline.product
FROM (((invoice_invoice i
LEFT JOIN administration_status status ON ((i.status = status.id)))
LEFT JOIN payment_payment_mode pay_mode ON ((i.payment_mode =
pay_mode.id)))
LEFT JOIN invoice_invoice_line iline ON ((i.id = iline.invoice)))
GROUP BY i.id, status.name, pay_mode.name, iline.product;

--
-- Name: VIEW ael_invoice; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON VIEW ael_invoice IS 'Factures';

--
-- PostgreSQL database dump complete
--

But if I dump the whole schema, I get a TABLE with RULE:

$ pg_dump --schema-only -O -x --schema=public coopener-v2_2017-03-16

[...]

--
-- Name: ael_invoice; Type: TABLE; Schema: public; Owner: -; Tablespace:
--

CREATE TABLE ael_invoice (
id bigint,
old_invoice_id character varying(255),
invoice_id character varying(255),
amount_wt numeric(20,2),
vat numeric(20,2),
is_paid boolean,
due_date date,
rum bigint,
invoice_date date,
invoice_name character varying(255),
status_name character varying(255),
payment_mode character varying(255),
reject_date date,
amount_rejected numeric(19,2),
contract_line bigint,
consumption_qty numeric,
product bigint
);

ALTER TABLE ONLY ael_invoice REPLICA IDENTITY NOTHING;

--
-- Name: TABLE ael_invoice; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE ael_invoice IS 'Factures';

[...]

--
-- Name: _RETURN; Type: RULE; Schema: public; Owner: -
--

CREATE RULE "_RETURN" AS
ON SELECT TO ael_invoice DO INSTEAD SELECT i.id,
i.old_invoice_id,
i.invoice_id,
i.ex_all_tax_total AS amount_wt,
i.vat_total AS vat,
i.balanced_memory_ok AS is_paid,
i.due_date,
i.rum,
i.invoice_date,
i.invoice_name,
status.name AS status_name,
pay_mode.name AS payment_mode,
i.reject_date,
i.amount_rejected,
i.contract_line,
sum(iline.qty) AS consumption_qty,
iline.product
FROM (((invoice_invoice i
LEFT JOIN administration_status status ON ((i.status = status.id)))
LEFT JOIN payment_payment_mode pay_mode ON ((i.payment_mode =
pay_mode.id)))
LEFT JOIN invoice_invoice_line iline ON ((i.id = iline.invoice)))
GROUP BY i.id, status.name, pay_mode.name, iline.product;

[...]

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2017-05-12 23:27:55 Re: BUG #14652: pg_dump: VIEW dumped as TABLE
Previous Message nilesoien 2017-05-12 21:27:23 BUG #14651: Uninitialized page fix corrupted TOAST table