Thread: [BUGS] BUG #14652: pg_dump: VIEW dumped as TABLE

The following bug has been logged on the website:

Bug reference:      14652
Logged by:          Laurent Martelli
Email address:      martellilaurent@gmail.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  |
charactervarying(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 ASSELECT i.id,   i.old_invoice_id,   i.invoice_id,   i.ex_all_tax_total AS amount_wt,
i.vat_totalAS vat,   i.balanced_memory_ok AS is_paid,   i.due_date,   i.rum,   i.invoice_date,   i.invoice_name,
status.nameAS 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_wtnumeric(20,2),   vat numeric(20,2),   is_paid boolean,   due_date date,   rum bigint,   invoice_date date,
invoice_namecharacter varying(255),   status_name character varying(255),   payment_mode character varying(255),
reject_datedate,   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_totalAS 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
LEFTJOIN 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;
 

[...]


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

martellilaurent@gmail.com writes:
> 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. 

There has never been any promise that you could load output from
pg_dump version X into server versions before X without manual
adjustments.  Furthermore, this case doesn't even pose any particular
need for manual adjustments: you need only ignore the error.
I see no bug here.

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

FWIW, that typically happens when needed to break a circular dependency.
In this case, since the GROUP BY is obviously inadequate to make the
SELECT legal on its own, I speculate that the query is legal only because
invoice_invoice.id is a primary key, so that the rule has to be emitted
after that pkey constraint is created.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs