postgresql - PostgreSQL : trigger that tests for dblink connection and establishes if not present

Question

我正在尝试在PostgreSQL 9.6中设置一个触发器，当将行插入到自己的表中时，该触发器将使用dblink将行插入到另一个数据库中。由于这些插入的数量可能很大，所以我不想为每个插入都连接到数据库或从数据库断开连接，所以我希望每个插入都使用一个持久连接。但是我认为我将不得不测试连接是否可用，因为很多情况都可能导致连接断开。

我的伪代码如下:

-- 1. test if named dblink connection already exists
-- 2.   if it does not, create a named dblink connection
-- 3. insert data via dblink

到目前为止，我是这样的:

CREATE OR REPLACE FUNCTION db_link_trigger()
   RETURNS trigger AS
$BODY$
BEGIN
   -- 1. test if named dblink connection already exists
   IF (SELECT COALESCE('dblinktest' = ANY (dblink_get_connections()), false)) = false THEN 
      -- 2. if it does not, create a named dblink connection
      RAISE NOTICE 'dblink connection not established.  Connecting now';
      PERFORM dblink_connect('dblinktest', 'hostaddr=192.168.1.30 port=5433 dbname=otherdb user=myuser password=mypassword');
   ELSE
      RAISE NOTICE 'dblink connection already established';
   END IF;

   -- 3. insert data via dblink
   PERFORM dblink_exec('dblinktest', 'insert into mytable(data) values(''' || NEW.data || ''');');

   RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

我主要关心的是如何处理错误以及如何处理触发器的几乎同时调用。考虑是否同时插入两个INSERTS，并且以前没有名为'dblinktest'的dblink。当第一个看到连接不存在时，它将继续进行设置。然后第二个可能会看到该链接也未建立并尝试自行连接，但是它将失败，因为第一个将在其之前进行连接，并且会引发如下错误:

ERROR:  duplicate connection name

我该如何处理这样的错误？ postgresql是否有类似python的伪代码？

try:
  if dblink connection is not established:
    establish dblink connection
except 'ERROR:  duplicate connection name':
  pass # do nothing
finally:
  insert row into other db via dblink connection

Answer 1

在这种情况下，您可以从此链接https://www.postgresql.org/docs/10/errcodes-appendix.html获取错误代码:

42710->重复对象

并使用带有EXCEPTION子句的PL/pgSQL BEGIN块来管理此错误

例如:

 CREATE OR REPLACE FUNCTION db_link_function()
 RETURNS void AS
 $BODY$
 declare
  rec record;
 BEGIN

 BEGIN   
  PERFORM  dblink_connect('dblinktest', 'hostaddr=127.0.0.1 port=5435 dbname=dell user=postgres password=password');
  --the EXCEPTION
  EXCEPTION
   WHEN duplicate_object THEN --code error 42710
    RAISE NOTICE 'this connections exists';

 END;
   --select data via dblink
 SELECT * FROM dblink('dblinktest','SELECT * FROM categories where category=1') AS t(cid int, cname text) into rec;
 raise notice 'value of rec: %,%', rec.cid, rec.cname;  


END;
$BODY$
LANGUAGE plpgsql;

我建议使用FDW代替dblink https://www.postgresql.org/docs/10/postgres-fdw.html