PostgreSQL 10 logical replication trigger function not executed

Question

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.

Laurenz Albe · Accepted Answer · 2019-06-19 16:01:40Z

8

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;

answered Jun 19, 2019 at 16:01

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nick Barnes Over a year ago

This will make the trigger fire, but if the goal is to keep sequences in sync across a multi-master setup, I don't think this trigger is going to do it...

DevWizard Over a year ago

I've used this tutorial, I'm using wal_level = logical digitalocean.com/community/tutorials/…

Laurenz Albe Over a year ago

@NickBarnes I agree. I just tried to explain why the triggers don't fire.

DevWizard Over a year ago

I'll give it a try and see what happens

Сергей Ячменев · Accepted Answer · 2021-03-11 12:40:44Z

1

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)))

answered Mar 11, 2021 at 12:40

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

211 bronze badge

Collectives™ on Stack Overflow

PostgreSQL 10 logical replication trigger function not executed

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related