Thread: BUG #13870: couldn't restore dump with mat view
The following bug has been logged on the website:
Bug reference: 13870
Logged by: Arthur Kardash
Email address: kardash.a.v@yandex.ru
PostgreSQL version: 9.5.0
Operating system: windows 7 x32
Description:
Try to feed sql-commands below, then do dump and you will see errors while
restore!
d:/pgsql/bin/pg_dump.exe -h 127.0.0.1 -U postgres -F c -f d:/backUp/test4
test
d:/pgsql/bin/pg_restore.exe -h 127.0.0.1 -U postgres -d t -F c
d:/backUp/test4
-----------------
SQL - commands
-----------------
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.5.0
-- Dumped by pg_dump version 9.5.0
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 row_security = off;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET search_path = public, pg_catalog;
--
-- Name: distance(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION distance(line integer) RETURNS numeric
LANGUAGE sql
AS $$
SELECT line * 4.5;
$$;
ALTER FUNCTION public.distance(line integer) OWNER TO postgres;
--
-- Name: point(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION point(line integer) RETURNS numeric
LANGUAGE sql
AS $$
WITH t(line) AS (
SELECT line
FROM stend
)
SELECT distance (line) FROM t
$$;
ALTER FUNCTION public.point(line integer) OWNER TO postgres;
SET default_tablespace = '';
--
-- Name: conjunct_dynamic1; Type: MATERIALIZED VIEW; Schema: public; Owner:
postgres
--
CREATE MATERIALIZED VIEW conjunct_dynamic1 AS
SELECT point.point
FROM point(1) point(point)
WITH NO DATA;
ALTER TABLE conjunct_dynamic1 OWNER TO postgres;
SET default_with_oids = false;
--
-- Name: stend; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE stend (
line integer
);
ALTER TABLE stend OWNER TO postgres;
--
-- Data for Name: stend; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY stend (line) FROM stdin;
\.
--
-- Name: conjunct_dynamic1; Type: MATERIALIZED VIEW DATA; Schema: public;
Owner: postgres
--
REFRESH MATERIALIZED VIEW conjunct_dynamic1;
--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
--
-- PostgreSQL database dump complete
--
kardash.a.v@yandex.ru writes:
> Try to feed sql-commands below, then do dump and you will see errors while
> restore!
Well, this example doesn't work as-is because the command order is already
wrong. But the issue is that CREATE MATERIALIZED VIEW ... WITH NO DATA
tries to plan and execute the query, not just store it, which entirely
defeats pg_dump's attempt to avoid hidden dependencies. We need a less
chintzy solution to handling this variant of CREATE MATERIALIZED VIEW.
(I seem to recall complaining about this before.)
regards, tom lane
On Sat, Jan 16, 2016 at 2:55 AM, <kardash.a.v@yandex.ru> wrote:
> Try to feed sql-commands below, then do dump and you will see errors while
> restore!
Here is a shorter sequence of commands to reproduce the failure:
CREATE TABLE stend (line integer);
CREATE FUNCTION point(line integer) RETURNS numeric
LANGUAGE sql
AS $$
WITH t(line) AS (
SELECT line
FROM stend
) SELECT (4 * line)::numeric FROM t $$;
pg_dump is usually quite smart at detecting dependencies and dumping
objects in the right order, but even if in this case pg_dump is
obviously wrong, there is no way for it to know that the function it
creates depends on this relation.
--
Michael
On Sat, Jan 16, 2016 at 10:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > kardash.a.v@yandex.ru writes: >> Try to feed sql-commands below, then do dump and you will see errors while >> restore! > > Well, this example doesn't work as-is because the command order is already > wrong. But the issue is that CREATE MATERIALIZED VIEW ... WITH NO DATA > tries to plan and execute the query, not just store it, which entirely > defeats pg_dump's attempt to avoid hidden dependencies. We need a less > chintzy solution to handling this variant of CREATE MATERIALIZED VIEW. > > (I seem to recall complaining about this before.) The first issue in the dump is the creation of point(), which depends on relation stend. -- Michael
Michael Paquier <michael.paquier@gmail.com> writes:
> On Sat, Jan 16, 2016 at 10:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, this example doesn't work as-is because the command order is already
>> wrong. But the issue is that CREATE MATERIALIZED VIEW ... WITH NO DATA
>> tries to plan and execute the query, not just store it, which entirely
>> defeats pg_dump's attempt to avoid hidden dependencies. We need a less
>> chintzy solution to handling this variant of CREATE MATERIALIZED VIEW.
>>
>> (I seem to recall complaining about this before.)
> The first issue in the dump is the creation of point(), which depends
> on relation stend.
point() is just fine, because check_relation_bodies is turned off.
pg_dump does that specifically because it can't see into the function
body to know what dependencies might be there.
The reason for separating dump/reload of matviews into two steps,
CREATE ... WITH NO DATA followed by REFRESH, is again to guard against
hidden dependencies. If the view were planned/executed only at the
REFRESH stage, this dump script would be fine as-is. It's the attempt
to avoid having two code paths in ExecCreateTableAs() that is causing
this failure.
(Having said that, it would still be possible to break it I think,
though much harder. Hiding relation dependencies inside functions
is not a good design technique.)
regards, tom lane
On Sun, Jan 17, 2016 at 12:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > point() is just fine, because check_relation_bodies is turned off. > pg_dump does that specifically because it can't see into the function > body to know what dependencies might be there. Thanks. Missed that. > The reason for separating dump/reload of matviews into two steps, > CREATE ... WITH NO DATA followed by REFRESH, is again to guard against > hidden dependencies. If the view were planned/executed only at the > REFRESH stage, this dump script would be fine as-is. It's the attempt > to avoid having two code paths in ExecCreateTableAs() that is causing > this failure. My brain is a bit fried now... But do you think that we could have some additional logic in ExecCreateTableAs() using check_relation_bodies? -- Michael