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:

fn_create_views
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!

What are these views? Dropping foreign tables and creating them again in the same transaction should work just fine. — Laurenz Albe, Dec 11 '19 at 10:56
@LaurenzAlbe the views are simple select queries over a materialized view. The purpose of the above functions is to redeploy all materialized view dependencies each time I want to make a change in the materialized view query. — Giannis Dim, Dec 12 '19 at 08:19
@LaurenzAlbe The drop & create of the foreign tables works perfectly in the same transaction. But what I want is to create the views, drop and create of the foreign tables all in the same transaction. — Giannis Dim, Dec 12 '19 at 08:30
What exactly is the connection between the views and the foreign tables? Do the view definitions use the foreign tables? — Laurenz Albe, Dec 12 '19 at 10:02
@LaurenzAlbe The other way around, foreign tables points to each views in the sourcedb. I am creating all views in the schema that importing with the following command IMPORT FOREIGN SCHEMA .. — Giannis Dim, Dec 12 '19 at 11:57

score 1 · Accepted Answer · answered Dec 12 '19 at 13:00

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.

answered Dec 12 '19 at 13:00

Laurenz Albe

209,280
17
206
263

Thank you! I will probably go with the 2nd option and create a wrapper function and call the two functions saparately. – Giannis Dim Dec 13 '19 at 14:01

PostgreSQL Create Views and Import Foreign Schema in the same transaction

1 Answers1