BUG #15282: Materialized view with transitive TYPE dependency failsrefresh using pg_restore and psql
От | PG Bug reporting form |
---|---|
Тема | BUG #15282: Materialized view with transitive TYPE dependency failsrefresh using pg_restore and psql |
Дата | |
Msg-id | 153184074037.1405.15097715315257989847@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #15282: Materialized view with transitive TYPE dependency fails refresh using pg_restore and psql
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 15282 Logged by: Keith Hickey Email address: kwhickey@gmail.com PostgreSQL version: 10.4 Operating system: RHEL7, Mac OSX Description: Scenario: A materialized view runs a select query that uses a function on one of its projected columns which internally uses an enum TYPE to map the column value to an enum value. Action and Resulting Error: Trying to restore a database setup in the above way will fail with error: ERROR: type "<type_name>" does not exist Reproduction: This can be reproduced with a "plain" (.sql) dump of the database using pg_dump and then restoring the schema and data by running psql against that dump. It can also be reproduced when using a pg_dump --format=directory dump of the database, and restoring that dump using pg_restore. Failed Workarounds: Lots of things, that didn't work. (1) using --list to create a file to use with pg_restore and --use-list that excluded MATERIALIZED VIEW DATA initially. Then doing this again to include only MATERIALIZED VIEW DATA in a 2nd pg_restore call. It still resulted in the same error. (2) Doing the same as above but excluding MATERIALIZED VIEW (create) and MATERIALIZED VIEW DATA statements initially, and then running only those in a 2nd pg_restore call (3) Doing the same as above, creating the database manually, then using pg_restore --list / --use-list to create only TYPES, then calling pg_restore to do everything but MATERIALIZED VIEW DATA, then finally using pg_restore to do MATERIALIZED VIEW DATA only. Same result. Also separating out the functions did not help either. (4) Doing pg_restore in 3 phases using the --section flag: run once with --section=pre-data, a second time with --section=data, a third time with --section=post-data. Failed the 3rd time with the same error. (5) Setting up default privileges in the database to GRANT ALL PRIVILEGES on all objects (including TYPEs) before running pg_restore. (6) Restoring a plain sql dump of the database with psql. This failed so long as the REFRESH MATERIALIZED VIEW call was in the same psql invocation as the TYPE creation. Successful Workaround: Exclude the MATERIALIZED VIEW DATA entries from the pg_restore, do the pg_restore, then follow that with a psql call to REFRESH each materialized view Example Database Reproducing this Error: -- -- PostgreSQL database dump -- -- Dumped from database version 10.4 -- Dumped by pg_dump version 10.4 SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; SET client_min_messages = warning; SET row_security = off; CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; CREATE TYPE public.customer_tier AS ENUM ( 'gold', 'silver', 'bronze' ); CREATE FUNCTION public.get_customer_tier(total_purchase numeric) RETURNS public.customer_tier LANGUAGE plpgsql AS $$ declare declare result text; begin if total_purchase < 10 then result='bronze'; elsif total_purchase < 100 then result='silver'; else result='gold'; end if; return result::customer_tier; end;$$; SET default_tablespace = ''; SET default_with_oids = false; CREATE TABLE public.customer ( id integer NOT NULL, name text NOT NULL ); CREATE TABLE public.product ( id integer NOT NULL, name text, price numeric DEFAULT 0.00 ); CREATE TABLE public.purchase ( id integer NOT NULL, product_id integer NOT NULL, customer_id integer NOT NULL, quantity integer DEFAULT 1 ); CREATE MATERIALIZED VIEW public.customer_rewards AS SELECT c.id AS customer_id, sum(((x.quantity)::numeric * p.price)) AS total_purchase, public.get_customer_tier(sum(((x.quantity)::numeric * p.price))) AS customer_tier FROM ((public.purchase x JOIN public.customer c ON ((c.id = x.customer_id))) JOIN public.product p ON ((p.id = x.product_id))) GROUP BY c.id WITH NO DATA; COPY public.customer (id, name) FROM stdin; 1 c1 2 c2 \. COPY public.product (id, name, price) FROM stdin; 1 p1 1.00 2 p2 2.00 3 p3 3.00 \. COPY public.purchase (id, product_id, customer_id, quantity) FROM stdin; 1 1 1 1 2 2 2 2 3 3 1 3 4 2 1 2 \. REFRESH MATERIALIZED VIEW public.customer_rewards; -- -- PostgreSQL database dump complete -- pg_dump Commands: 〉pg_dump --dbname postgresql://root:password@127.0.0.1:5432/restore_matview_bug --jobs 16 --compress=6 --format=directory --file restore_matview_bug_dump --no-privileges --no-owner --verbose 〉pg_dump --dbname postgresql://root:password@127.0.0.1:5432/restore_matview_bug --file restore_matview_bug_dump.sql --no-privileges --no-owner --verbose Failed Restore Commands and Errors: (1) pg_restore 〉pg_restore --jobs 16 --dbname postgresql://root:password@127.0.0.1:5432/restore_matview_bug_test --verbose --exit-on-error restore_matview_bug_dump/ ... pg_restore: launching item 2433 MATERIALIZED VIEW DATA customer_rewards pg_restore: creating MATERIALIZED VIEW DATA "public.customer_rewards" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 2433; 0 16411 MATERIALIZED VIEW DATA customer_rewards root pg_restore: [archiver (db)] could not execute query: ERROR: type "customer_tier" does not exist LINE 1: SELECT result::customer_tier ^ QUERY: SELECT result::customer_tier CONTEXT: PL/pgSQL function public.get_customer_tier(numeric) line 1 at RETURN Command was: REFRESH MATERIALIZED VIEW public.customer_rewards; pg_restore: [parallel archiver] a worker process died unexpectedly (2) psql 〉psql --dbname postgresql://root:password@127.0.0.1:5432/restore_matview_bug_test --file restore_matview_bug_dump.sql psql:restore_matview_bug_dump.sql:161: ERROR: type "customer_tier" does not exist LINE 1: SELECT result::customer_tier ^ QUERY: SELECT result::customer_tier CONTEXT: PL/pgSQL function public.get_customer_tier(numeric) line 1 at RETURN Possibly Similar Issues: May be related to Bug #13870 and #13907: https://www.postgresql.org/message-id/flat/20160115175546.2968.6033%40wrigleys.postgresql.org#20160115175546.2968.6033@wrigleys.postgresql.org "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." And here: http://www.postgresql-archive.org/BUG-13907-Restore-materialized-view-throw-permission-denied-td5885472.html
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tomas VondraДата:
Сообщение: Re: BUG #15281: Set role does not affect superuser privleges
Следующее
От: Moshe JacobsonДата:
Сообщение: Re: pg_restore: All GRANTs on table fail when any one role is missing