From: | Aurelien Praga <aurelien(dot)praga(at)inovia-team(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Materialized view not created with import |
Date: | 2016-07-05 13:28:11 |
Message-ID: | CAPW4YMZB7tYYv_dky-_4p-+jGikefifrd7RzGu7ty0Bw2-tfhQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I need your help because I have a materialized view not created during an
import.
This materialized view is using a function and this function depends on a
table.
When importing the database dump:
- the function is created
- the materialized view is not created because the table used by the
function is imported yet
- the table is created
Here is the code (in the correct order):
CREATE TABLE user_corporation_rights (
id integer NOT NULL,
-- ...
);
CREATE OR REPLACE FUNCTION
get_allowed_news_for_user_corporation(user_corporation_id_param integer)
RETURNS character varying[] AS
$BODY$
SELECT id FROM user_corporation_rights --...
$BODY$
LANGUAGE sql VOLATILE;
CREATE MATERIALIZED VIEW news_rights_by_user_corporation AS
SELECT uc.id AS user_corporation_id, get_allowed_news_for_user_corporation(
uc.id) AS news_list
FROM user_corporation uc
WITH DATA;
The Postgresql version is 9.5.
I think pg_dump doesn't know that the table is used in the function for the
materialized view so it puts it at the end of the file because of its name.
I tried to export/import the database in a .sql file and in a binary file
but it's the same problem.
For the moment, I think about 2 solutions:
- export the materialized views separately of the tables/functions/data
- maintain the materialized view definition in a .sql file to import after
each pg_restore
Do you have a better solution?
Thank you,
Aurélien Praga
From | Date | Subject | |
---|---|---|---|
Next Message | Christian Castelli | 2016-07-05 13:30:54 | Avoid deadlocks on alter table |
Previous Message | Krzysztof Kaczkowski | 2016-07-05 11:36:39 | Re: Cluster on NAS and data center. |