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;
[...]
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 |