Reputation: 47
I have setup postgres_fdw between two databases (sourcedb, targetdb) so that I can create Foreign Data Tables in targetdb from a schema in sourcedb.
All the above is configured and working as expected.
The next step was to re-Import Foreign Schema each time I have changes in the views in sourcedb.
In order to achieve this I created two functions in sourcedb:
In the first function (fn_create_views) I am creating the views dynamically in a loop. After the loop ends I am calling the second function that drops all foreign data tables and Import foreign schema through a dblink connecting on the targerdb.
CREATE FUNCTION fn_create_views ()
RETURNS BOOLEAN
LANGUAGE plpgsql
as $$
BEGIN
FOR .. IN
EXECUTE '..'
LOOP
EXECUTE format('CREATE OR REPLACE VIEW .. AS
SELECT * FROM ...', params);
END LOOP;
PERFORM fn_recreate_foreign_data_tables('source_foreign_server','target_foreign_server');
return true;
END $$;
CREATE FUNCTION fn_recreate_foreign_data_tables(_source_foreign_server varchar, _targer_foreign_server varchar)
returns void
language plphsql
as $$
DECLARE
_sql_exec text;
BEGIN
_sql_exec := (SELECT format('SELECT public.dblink_exec(%L,
''DO
$dblink$
DECLARE
l_rec record;
BEGIN
FOR l_rec IN (SELECT foreign_table_schema, foreign_table_name
FROM information_schema.foreign_tables
WHERE foreign_server_name = ''%L'')
LOOP
EXECUTE format(''''drop foreign table %I.%I'''', l_rec.foreign_table_schema, l_rec.foreign_table_name);
END LOOP;
IMPORT FOREIGN SCHEMA ..
FROM SERVER foreign_server INTO ..;
END $dblink$;'')', _source_foreign_server, _target_foreign_server));
EXECUTE _sql_exec;
end $$;
The issue I am experiencing with the above is that during the 'IMPORT FOREIGN SCHEMA' the 'CREATE VIEW' is not committed as a result although all the foreign tables are dropped its not Importing anything into the targetdb schema.
After reading several posts here in SO, some recommend to run the 'CREATE VIEW' command through dblink on the same DB. Apparently this works perfectly since I guess dblink would open a separate transaction each time.
My question now is, is there another simpler way to do the above without calling the above functions separately ?
Thank you!
Upvotes: 0
Views: 1914
Reputation: 247235
You need to do COMMIT
the local transaction in which you create the views before you can use them with foreign tables.
I see two options:
Create the views in a dblink
call to the local database. Then the transaction will be committed when dblink_exec
is done.
Run a COMMIT
between the calls to fn_create_views
and fn_recreate_foreign_data_tables
.
Upvotes: 1