[SOLVED] PostgreSQL 10 logical replication trigger function not executed

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.

Solution

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;