CODEWITHSUNDEEP
postgresqlreplicationdatabase-replicationmulti-master-replication
DevWizard
DevWizard

Reputation: 697

PostgreSQL 10 logical replication trigger function not executed

I've implemented the Logical replication in PostgreSQL 10 within 2 db in 2 different server and I'm aware that the sequences are not sync so I created a trigger function like this in both database:

CREATE FUNCTION update_ogc_fid()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
AS $BODY$
Declare 
iInt integer;
BEGIN
execute 'Select nextval('|| chr(39) || 'address_ogc_fid_seq' || chr(39) ||');' into iInt;
NEW.ogc_fid = iInt;
RETURN NEW;
END;
$BODY$;

CREATE TRIGGER update_ogc_fid_address
    BEFORE INSERT 
    ON address
    FOR EACH ROW
    EXECUTE PROCEDURE update_ogc_fid();

A part from the fact that is actually using just odd ID in the DB1 and even in DB2 why is the function not triggered in the second database?

If I insert a record in the DB1 i have ID 3, it do the replication and in the DB2 i have the same record with the same ID, I'm going to check the sequence in the DB2 and is still 1 instead of be 3.

Now I'm going to insert a record in the DB2 and I have ID 2 and then 4.

For what I want to achieve that is a master master system, in case that one is down I can write in the second one I don't really care too much but thinking in case of a disaster when I'm going to restore the DB in the server that is down (maybe with a pg-dump backup) I need to update all the sequences to start writing from where was before.

Upvotes: 3

Views: 3742

Answers (2)

Сергей Ячменев
Сергей Ячменев

Reputation: 11

CREATE  TRIGGER yours_trigger_name
AFTER INSERT OR UPDATE ON yours_table_name
FOR EACH ROW
EXECUTE PROCEDURE yours_trigger_procedure_name();

alter table yours_table_name enable always trigger yours_trigger_name;
alter table yours_table_name enable replica trigger yours_trigger_name;

This construction works in replication, but if you want to use UPDATE OF yours_field_name instead of just UPDATE it doesn't work. I don't know why)))

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247485

Triggers are by default not executed on logical standby servers, because the parameter session_replication_role is set to replica when the changes are applied.

You can change a trigger so that it fires even during replication:

ALTER TABLE mytab
   ENABLE ALWAYS TRIGGER atrigger;

Upvotes: 8

Related Questions