From: | lalbin(at)scharp(dot)org |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #14147: Restore of MatView of Foreign Data Table fails |
Date: | 2016-05-18 16:07:57 |
Message-ID: | 20160518160757.16249.48982@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: 14147
Logged by: Lloyd Albin
Email address: lalbin(at)scharp(dot)org
PostgreSQL version: 9.5.3
Operating system: SUSE Linux (64-bit)
Description:
I have found that restoration of Materialized Views of Foreign Data Tables
where user postgres does not have a user mapping will fail. It seems that it
is trying to execute the Foreign Data Table in some way instead of reading
the Foreign Table Definition.
The only workaround that I have found is to create a user mapping for user
postgres for every foreign data connection.
Example:
CREATE DATABASE db_a
WITH ENCODING = 'UTF8'
TEMPLATE = template1;
CREATE ROLE joe LOGIN PASSWORD 'xxx';
SET ROLE joe;
CREATE TABLE public.test (
id SERIAL,
v TEXT,
PRIMARY KEY(id)
)
WITH (oids = false);
SET ROLE postgres;
CREATE DATABASE db_b
WITH ENCODING = 'UTF8'
TEMPLATE = template1;
CREATE EXTENSION postgres_fdw;
GRANT USAGE
ON FOREIGN DATA WRAPPER postgres_fdw TO joe;
CREATE SERVER db_a
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
host 'localhost',
port '5432',
dbname 'db_a');
CREATE USER MAPPING FOR joe
SERVER db_a
OPTIONS (
user 'joe',
password 'xxx');
CREATE FOREIGN TABLE public."test" (
id INTEGER NOT NULL,
v TEXT
)
SERVER db_a
OPTIONS (
schema_name 'public',
table_name 'test');
CREATE MATERIALIZED VIEW public.mv_test AS SELECT * FROM public.test;
CREATE DATABASE db_c
WITH ENCODING = 'UTF8'
TEMPLATE = template1;
pg_dump -h localhost -U postgres -Fc db_b > db_b.pgdump
pg_restore -h localhost -U postgres -d db_c db_b.pgdump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 183; 1259 16430
MATERIALIZED VI
EW mv_test joe
pg_restore: [archiver (db)] could not execute query: ERROR: user mapping
not fo
und for "postgres"
Command was: CREATE MATERIALIZED VIEW mv_test AS
SELECT test.id,
test.v
FROM test
WITH NO DATA;
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"mv_test"
does not exist
Command was: ALTER TABLE mv_test OWNER TO joe;
pg_restore: [archiver (db)] Error from TOC entry 2104; 0 16430 MATERIALIZED
VIEW
DATA mv_test joe
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"mv_test"
does not exist
Command was: REFRESH MATERIALIZED VIEW mv_test;
WARNING: errors ignored on restore: 3
From | Date | Subject | |
---|---|---|---|
Next Message | ziyun.wang | 2016-05-18 16:36:49 | BUG #14148: postgres does not support GST timezone |
Previous Message | bbabhu | 2016-05-18 14:34:09 | BUG #14146: Connection refused (0x0000274D/10061 |