时间戳

首页 > 解决方案 > PostgreSQL 在同一个事务中创建视图和导入外部模式

postgresql - PostgreSQL 在同一个事务中创建视图和导入外部模式

问题描述

我已经在两个数据库（sourcedb、targetdb）之间设置了 postgres_fdw，以便我可以从 sourcedb 中的模式在 targetdb 中创建外部数据表。

以上所有内容均已配置并按预期工作。

下一步是每次我在 sourcedb 中更改视图时重新导入外部模式。

为了实现这一点，我在 sourcedb 中创建了两个函数：

  1. fn_create_views
  2. fn_recreate_foreign_data_tables

在第一个函数 (fn_create_views) 中，我在循环中动态创建视图。循环结束后，我将调用第二个函数，该函数删除所有外部数据表并通过连接在 targerdb 上的 dblink 导入外部模式。

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 $$;

我在上面遇到的问题是，在“IMPORT FOREIGN SCHEMA”期间，“CREATE VIEW”没有提交，尽管所有外部表都被删除了，它没有将任何东西导入到 targetdb 模式中。

在阅读了 SO 中的几篇文章后，一些人建议通过 dblink 在同一个数据库上运行“CREATE VIEW”命令。显然这很有效，因为我猜 dblink 每次都会打开一个单独的事务。

我现在的问题是，是否有另一种更简单的方法可以在不单独调用上述函数的情况下完成上述操作？

谢谢！

标签： postgresqlfunctionviewdblinkpostgres-fdw

解决方案

您需要先执行COMMIT创建视图的本地事务，然后才能将它们与外部表一起使用。

我看到两个选项：

  • dblink在对本地数据库的调用中创建视图。然后事务将在dblink_exec完成时提交。

  • 在对和COMMIT的调用之间运行 a 。fn_create_viewsfn_recreate_foreign_data_tables

推荐阅读