Thread: BUG #16202: Cannot restore database that has materialized view using crosstab (tablefunc)
BUG #16202: Cannot restore database that has materialized view using crosstab (tablefunc)
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 16202 Logged by: Wagner Engel Email address: wagner.engel@gmail.com PostgreSQL version: 12.1 Operating system: Debian 10 Description: If I build a materizalized view that uses the crosstab() function, a dump/restore cycle will not restore the database. A simple test case is: create.sql: -------------------------------- CREATE EXTENSION tablefunc; CREATE TABLE t ( id INTEGER ); CREATE VIEW v AS SELECT * FROM crosstab ( 'SELECT id FROM t as t1', 'SELECT id FROM t as t2' ) ct (a integer, b integer); CREATE MATERIALIZED VIEW mv AS SELECT * FROM v; -------------------------------- $ createdb -h localhost -U postgres abc $ psql -h localhost -U postgres abc < create.sql CREATE EXTENSION CREATE TABLE CREATE VIEW SELECT 0 $ pg_dump --host localhost --username "postgres" --format plain --file abc.sql abc $ createdb -h localhost -U postgres abc2 $ psql -h localhost -U postgres abc2 < abc.sql SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET CREATE EXTENSION COMMENT CREATE VIEW ALTER TABLE SET SET CREATE MATERIALIZED VIEW ALTER TABLE CREATE TABLE ALTER TABLE COPY 0 ERROR: relation "t" does not exist LINE 1: SELECT id FROM t as t2 ^ QUERY: SELECT id FROM t as t2 Using format custom and pg_restore also fails: $ pg_dump --host localhost --username "postgres" --format custom --file abc.backup abc $ createdb -h localhost -U postgres abc3 $ pg_restore --host localhost --username "postgres" -d abc3 abc.backup pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 2901; 0 1094129 MATERIALIZED VIEW DATA mv postgres pg_restore: error: could not execute query: ERROR: relation "t" does not exist LINE 1: SELECT id FROM t as t2 ^ QUERY: SELECT id FROM t as t2 Command was: REFRESH MATERIALIZED VIEW public.mv; pg_restore: warning: errors ignored on restore: 1 Extra: Within the abc.sql dump, the CREATE TABLE command is after both create view commands. Moving it before makes no difference. Versions: $ pg_dump -V pg_dump (PostgreSQL) 12.1 (Debian 12.1-1.pgdg100+1) $ pg_restore -V pg_restore (PostgreSQL) 12.1 (Debian 12.1-1.pgdg100+1) $ psql -V psql (PostgreSQL) 12.1 (Debian 12.1-1.pgdg100+1) $ psql -h localhost -U postgres abc -c "SELECT version();" version ------------------------------------------------------------------------------------------------------------------ PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit (1 row)
Re: BUG #16202: Cannot restore database that has materialized view using crosstab (tablefunc)
From
Tom Lane
Date:
PG Bug reporting form <noreply@postgresql.org> writes: > If I build a materizalized view that uses the crosstab() function, a > dump/restore cycle will not restore the database. > CREATE VIEW v AS > SELECT * FROM crosstab ( > 'SELECT id FROM t as t1', > 'SELECT id FROM t as t2' > ) ct (a integer, b integer); This view is unsafe because it doesn't schema-qualify its reference to table t. It will fail if run with a restrictive search_path, which is what pg_dump does for security reasons. An ordinary view reference to "t" would be all right, because the system understands what that is and can adjust the schema qualification; but here you've just got a string that happens to contain "t" in it. regards, tom lane
Re: BUG #16202: Cannot restore database that has materialized viewusing crosstab (tablefunc)
From
Wagner Engel
Date:
Indeed, that was it. Thank you so much!
Em sex., 10 de jan. de 2020 às 15:04, Tom Lane <tgl@sss.pgh.pa.us> escreveu:
PG Bug reporting form <noreply@postgresql.org> writes:
> If I build a materizalized view that uses the crosstab() function, a
> dump/restore cycle will not restore the database.
> CREATE VIEW v AS
> SELECT * FROM crosstab (
> 'SELECT id FROM t as t1',
> 'SELECT id FROM t as t2'
> ) ct (a integer, b integer);
This view is unsafe because it doesn't schema-qualify its reference
to table t. It will fail if run with a restrictive search_path,
which is what pg_dump does for security reasons.
An ordinary view reference to "t" would be all right, because the
system understands what that is and can adjust the schema qualification;
but here you've just got a string that happens to contain "t" in it.
regards, tom lane