Обсуждение: Materialized view not created with import
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
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
On 07/05/2016 06:28 AM, Aurelien Praga wrote: > 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 <http://uc.id> AS user_corporation_id, > get_allowed_news_for_user_corporation(uc.id <http://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? https://www.postgresql.org/docs/9.5/static/app-pgrestore.html Using one of the custom dump formats: " -l --list List the contents of the archive. The output of this operation can be used as input to the -L option. Note that if filtering switches such as -n or -t are used with -l, they will restrict the items listed. -L list-file --use-list=list-file Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored. list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line. See below for examples. " > > Thank you, > > Aurélien Praga -- Adrian Klaver adrian.klaver@aklaver.com