Search code examples
postgresqltriggersreplicationplpgsqldblink

Trigger to insert rows in remote database after deletion

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

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?

Here an example that works fine:

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

  • 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.

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

    • Use format() to escape strings more elegantly.

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

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

    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.

    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:

    Then:

    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.

    If you are serious about this:

    insert this row in the table 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;

    Related: