Trigger to insert rows in remote database after deletion

Question

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;

score 4 · Accepted Answer · edited May 23 '17 at 12:32

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:
- Persistent inserts in a UDF even if the function aborts

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:

Run batch file with psql command without password

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;

How do I do large non-blocking updates in PostgreSQL?

I appreciate your answer but what i need to do is archiving data from master server to a standby server by deleting data from master and reinsert them in standby , i have used stream replication but the problem is that when i delete data from master it is deleted automatically from standby , would you please suggest another solution that can solve my problem — hacene abdessamed, May 10 '16 at 07:43
This is the best solution when the local and the remote table are identique , thanks a lot — hacene abdessamed, May 10 '16 at 08:17

score 2 · Answer 2 · answered May 04 '16 at 08:20

You can use quote_nullable for this! Also, concat_ws comes very handy:

CREATE OR REPLACE FUNCTION flux_tresorerie_historique_backup_row()
RETURNS trigger AS
$BODY$
BEGIN
  perform dblink_connect('dbname=gtr_bd_archive user=postgres password=postgres');
  perform dblink_exec('insert into flux_tresorerie_historique values('||
    concat_ws(', ', quote_nullable(OLD.id_flux_historique),
                    quote_nullable(OLD.date_operation_flux),
                    quote_nullable(OLD.date_valeur_flux),
                    quote_nullable(to_char(OLD.date_rapprochement_flux, 'YYYY-MM-DD')),
                    quote_nullable(OLD.libelle_flux),
                    quote_nullable(OLD.montant_flux),
                    quote_nullable(OLD.contre_valeur_dzd),
                    quote_nullable(OLD.rib_compte_bancaire),
                    quote_nullable(OLD.frais_flux),
                    quote_nullable(OLD.sens_flux),
                    quote_nullable(OLD.statut_flux),
                    quote_nullable(OLD.code_devise),
                    quote_nullable(OLD.code_mode_paiement),
                    quote_nullable(OLD.code_agence),
                    quote_nullable(OLD.code_compte),
                    quote_nullable(OLD.code_banque),
                    quote_nullable(OLD.date_maj_flux),
                    quote_nullable(OLD.statut_frais),
                    quote_nullable(OLD.reference_flux),
                    quote_nullable(OLD.code_commission),
                    quote_nullable(OLD.id_flux)
           )||');');
  perform dblink_disconnect();
  RETURN NULL;
END;

Note that it is OK to place non-sting values between single quotes, since a quoted literal is for PostgreSQL just as good a literal value as one without the quotes, so it is convenient to place all of the columns processed by quote_nullable. Also note that quote_nullable will already output dates in YYYY-MM-DD format (e.g. select quote_nullable(now()::date) would result in '2016-05-04'), so you may want to simplify OLD.date_rapprochement_flux even further by removing the to_char.

Trigger to insert rows in remote database after deletion

2 Answers2

Linked