PostgreSQL Create Views and Import Foreign Schema in the same transaction

Question

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:

1. fn_create_views
2. fn_recreate_foreign_data_tables

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!

Answer 1

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.