删除后触发在远程数据库中插入行 [英] Trigger to insert rows in remote database after deletion

问题描述

我创建了一个触发器，其工作方式如下:
从表flux_tresorerie_historique中删除数据后，将其插入到另一个数据库归档文件中的表flux_tresorerie_historique中

I have created a trigger that works like this:
After deleting data from table flux_tresorerie_historique it insert this row in the table flux_tresorerie_historique that is located in another database archive

我使用dblink在远程数据库中插入数据，问题是查询的创建太困难了，尤其是该表包含20多个列，并且我想为其他10个表创建类似的功能.

I use dblink to insert data in the remote database, the problem is that the creation of the query is too hard especially that the table contain more than 20 columns, and I want to create similar functions for 10 other tables.

还有另一种快速的方法可以确保完成此任务吗?

Is there another rapid way to ensure this task?

这里有个很好的例子:

CREATE OR REPLACE FUNCTION flux_tresorerie_historique_backup_row()
RETURNS trigger AS
$BODY$
DECLARE date_rapprochement_flux TEXT;
DECLARE code_commission  TEXT;
DECLARE reference_flux TEXT;
BEGIN
IF OLD.date_rapprochement_flux is null
THEN
date_rapprochement_flux = 'NULL';
ELSE
date_rapprochement_flux = ''''||to_char(OLD.date_rapprochement_flux, 'YYYY-MM-DD')||'''';
END IF;

IF OLD.code_commission is null
THEN
code_commission = 'NULL';
ELSE
code_commission = ''''||replace(OLD.code_commission,'''','''''')||'''';
END IF;

IF OLD.reference_flux is null
THEN
reference_flux = 'NULL';
ELSE
reference_flux = ''''||replace(OLD.reference_flux,'''','''''')||'''';
END IF;

perform dblink_connect('dbname=gtr_bd_archive user=postgres     password=postgres');
perform dblink_exec('insert into flux_tresorerie_historique values('||OLD.id_flux_historique||','''||OLD.date_operation_flux||''','''||OLD.date_valeur_flux||''','||date_rapprochement_flux||','''||replace(OLD.libelle_flux,'''','''''')||''','||OLD.montant_flux||','||OLD.contre_valeur_dzd||','''||replace(OLD.rib_compte_bancaire,'''','''''')||''','||OLD.frais_flux||','''||replace(OLD.sens_flux,'''','''''')||''','''||replace(OLD.statut_flux,'''','''''')||''','''||replace(OLD.code_devise,'''','''''')||''','''||replace(OLD.code_mode_paiement,'''','''''')||''','''||replace(OLD.code_agence,'''','''''')||''','''||replace(OLD.code_compte,'''','''''')||''','''||replace(OLD.code_banque,'''','''''')||''','''||OLD.date_maj_flux||''','''||replace(OLD.statut_frais,'''','''''')||''','||reference_flux||','||code_commission||','||OLD.id_flux||');');
perform dblink_disconnect();
RETURN NULL;
END;

推荐答案

这是复制的有限应用程序.需求差异很大，因此存在许多针对不同情况的既定解决方案. 请考虑手册中的概述.

This is a limited application of replication. Requirements vary a lot, so there are a number of different established solutions, addressing different situations. Consider the overview in the manual.

您的基于触发器的手工编织解决方案是相对 个删除相对而言可行的选择.为每行打开和关闭单独的连接会产生相当大的开销.还有其他各种选择.

Your hand-knit, trigger-based solution is one viable option for relatively few deletions. Opening and closing a separate connection for every row incurs quite an overhead. There are other various options.

，在使用dblink时，我建议进行一些修改.最重要的是:

While working with dblink I suggest some modifications. Most importantly:

• 使用format()可以更优雅地转义字符串.

• Use format() to escape strings more elegantly.

传递 整行 ，而不是传递并转义每一列.

Pass the whole row instead of passing and escaping every single column.

不要在每个触发器功能中都放置密码.
使用FOREIGN SERVER加USER MAPPING.详细说明在这里:

Don't place the password in every single trigger function.
Use a FOREIGN SERVER plus USER MAPPING. Detailed instructions here:

• 即使函数中止，也会在UDF中持久插入

基本上，在源服务器上运行一次:

Basically, run once on the source server:

CREATE SERVER myserver FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (hostaddr '127.0.0.1', dbname 'gtr_bd_archive');

CREATE USER MAPPING FOR role_source SERVER myserver
OPTIONS (user 'postgres', password 'secret');

最好不要在目标服务器上以超级用户身份登录.使用具有有限特权的专用角色来避免特权升级.

Preferably, don't log in as superuser at the target server. Use a dedicated role with limited privileges to avoid privilege escalation.

并在目标服务器上使用密码文件允许无密码访问.这样，您甚至不必将密码存储在USER MAPPING中.相关答案的最后一章中的说明:

And use a password file on the target server to allow password-less access. This way you don't even have to store the password in the USER MAPPING. Instructions in the last chapter of this related answer:

• 使用不带密码的psql命令运行批处理文件

然后:

CREATE OR REPLACE FUNCTION pg_temp.flux_tresorerie_historique_backup_row()
  RETURNS trigger AS
$func$
BEGIN
   PERFORM dblink_connect('myserver');  -- name of foreign server from above

   PERFORM dblink_exec( format(
   $$
   INSERT INTO flux_tresorerie_historique  -- provide target column list!
   SELECT (r).id_flux_historique
        , (r).date_operation_flux
        , (r).date_valeur_flux
        , (r).date_rapprochement_flux::date  -- 'YYYY-MM-DD' is default ISO format anyway
        , (r).libelle_flux
        , (r).montant_flux
        , (r).contre_valeur_dzd
        , (r).rib_compte_bancaire
        , (r).frais_flux
        , (r).sens_flux
        , (r).statut_flux
        , (r).code_devise
        , (r).code_mode_paiement
        , (r).code_agence
        , (r).code_compte
        , (r).code_banque
        , (r).date_maj_flux
        , (r).statut_frais
        , (r).reference_flux
        , (r).code_commission
        , (r).id_flux
   FROM   (SELECT %L::flux_tresorerie_historique) t(r)
   $$, OLD::text));  -- cast whole row type

   PERFORM dblink_disconnect();
   RETURN NULL;  -- only for AFTER trigger
END
$func$  LANGUAGE plpgsql;

如果行类型不匹配，则应列出目标表的列列表.

You should spell out the list of columns for the target table if the row types don't match.

如果您对此很认真:

在表flux_tresorerie_historique

即，您插入 整行 ，并且目标行的类型相同(无需从时间戳中提取日期等)，可以进一步简化传递整行.

I.e., you insert the whole row and the target row type is identical (no extracting a date from a timestamp etc.), you can simplify much further passing the whole row.

CREATE OR REPLACE FUNCTION flux_tresorerie_historique_backup_row()
  RETURNS trigger AS
$func$
BEGIN
   PERFORM dblink_connect('myserver');  -- name of foreign server

   PERFORM dblink_exec( format(
   $$
   INSERT INTO flux_tresorerie_historique
   SELECT (%L::flux_tresorerie_historique).*
   $$
   , OLD::text));

   PERFORM dblink_disconnect();
   RETURN NULL;  -- only for AFTER trigger
END
$func$  LANGUAGE plpgsql;

相关:

• 我如何做大PostgreSQL中的非阻塞更新?

这篇关于删除后触发在远程数据库中插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！