Avoiding infinite loop in a master-master replication

Question

As an exercise, I am implementing a master-master replication in PostgreSQL, using triggers (requirement of the task). Basically there are 2 databases with tables that have almost exactly the same structure.

How do I avoid going into infinite loop? As I understand how it is done in production replication solutions, a variable that identifies the origin has to be passed - is that a correct approach? How should I implement it in my solution?

I am attaching full code required for running it, although the parts I am asking about are separated into two functions fired by triggers.

DROP DATABASE IF EXISTS db1;
DROP DATABASE IF EXISTS db2;

CREATE DATABASE db1;
CREATE DATABASE db2;

\c db1 

CREATE TABLE Person (
    IdPerson INT PRIMARY KEY     ,
    FirstName           TEXT    NOT NULL,
    LastName            TEXT     NOT NULL,
    BirthDate DATE
);


\c db2

CREATE TABLE People (
    IdPerson INT PRIMARY KEY     ,
    Name TEXT NOT NULL,
    BirthDate DATE
);

\c db1

-- Master-slave replication

CREATE EXTENSION dblink;

CREATE OR REPLACE FUNCTION fn_remote_1() RETURNS TRIGGER AS $body$
DECLARE
_sql text;
BEGIN
    SET LOCAL session_replication_role = 'replica';
    PERFORM dblink_connect('dbname=db2');
    IF TG_OP = 'INSERT' THEN
        PERFORM
        DBLINK_EXEC('dbname=db2','INSERT INTO People VALUES ('||new.IdPerson||','''||CONCAT(new.FirstName,' ',new.LastName)||''', '''||new.BirthDate||''')');
    ELSIF TG_OP = 'UPDATE' THEN
        _sql := 
        'UPDATE People SET Name='''||CONCAT(new.FirstName,' ',new.LastName)||''',BirthDate='''||new.BirthDate||''' WHERE IdPerson='||OLD.IdPerson||'';
        PERFORM DBLINK_EXEC(_sql);
    ELSIF TG_OP = 'DELETE' THEN
        _sql := format('
            DELETE FROM People 
            WHERE  IdPerson = %s;'
            , (OLD.IdPerson)::text);
        PERFORM DBLINK_EXEC(_sql);
    ELSE
        RAISE 'Trigger error on operation %', TG_OP;
END IF;
PERFORM dblink_disconnect();
RETURN NEW;
END;
$body$ LANGUAGE 'plpgsql';


CREATE TRIGGER TASK_EVERYONE AFTER INSERT OR UPDATE OR DELETE 
ON Person FOR EACH ROW
EXECUTE PROCEDURE fn_remote_1();

\c db1

INSERT INTO Person (IdPerson, FirstName, LastName, BirthDate) VALUES (1, 'Michael', 'Jordan', to_date('1963-09-01', 'YYYY-MM-DD'));
INSERT INTO Person (IdPerson, FirstName, LastName, BirthDate) VALUES (2, 'Jessica', 'Albo', to_date('1983-12-01', 'YYYY-MM-DD'));
INSERT INTO Person (IdPerson, FirstName, LastName, BirthDate) VALUES (3, 'Roman', 'Polanski', to_date('1992-12-01', 'YYYY-MM-DD'));

UPDATE Person SET LastName = 'Gasior' WHERE IdPerson=1;
DELETE FROM Person WHERE IdPerson='2';
DELETE FROM Person WHERE IdPerson=1;

INSERT INTO Person (IdPerson, FirstName, LastName, BirthDate) VALUES (1, 'Grace', 'Kelly', to_date('1963-09-01', 'YYYY-MM-DD'));
INSERT INTO Person (IdPerson, FirstName, LastName, BirthDate) VALUES (2, 'John', 'Kennedy', to_date('1983-12-01', 'YYYY-MM-DD'));


-- Master-master replication

\c db1
SELECT * FROM Person;
\c db2
SELECT * FROM People;

\c db2

CREATE EXTENSION dblink;

CREATE OR REPLACE FUNCTION fn_remote_2() RETURNS TRIGGER AS $body2$
DECLARE
_sql text;
BEGIN
    SET LOCAL session_replication_role = 'replica';
    PERFORM dblink_connect('dbname=db1');
    IF TG_OP = 'INSERT' THEN
        PERFORM DBLINK_EXEC('INSERT INTO Person VALUES ('||new.IdPerson||','''||new.Name||''','''||new.Name||''', '''||new.BirthDate||''')');
    ELSIF TG_OP = 'UPDATE' THEN
        _sql := 
        'UPDATE Person SET Name='''||CONCAT(new.FirstName,' ',new.LastName)||''',BirthDate='''||new.BirthDate||''' WHERE IdPerson='||OLD.IdPerson||'';
    ELSIF TG_OP = 'DELETE' THEN
        _sql := format('
            DELETE FROM Person 
            WHERE  IdPerson = %s;'
            , (OLD.IdPerson)::text);
    ELSE
        RAISE 'Trigger error on opearation %', TG_OP;
END IF;
PERFORM dblink_disconnect();
COMMIT;
RETURN NEW;
END;
$body2$ LANGUAGE 'plpgsql';

CREATE TRIGGER TASK_ADDITIONAL AFTER INSERT OR UPDATE OR DELETE 
ON People FOR ROW
WHEN (current_setting('session_replication_role') <> 'replica')
EXECUTE PROCEDURE fn_remote_2();


INSERT INTO People (IdPerson, Name, BirthDate) VALUES (4, 'Raymon Tusk', to_date('1944-09-01', 'YYYY-MM-DD'));
INSERT INTO People (IdPerson, Name, BirthDate) VALUES (5, 'Charlie Chaplin', to_date('1944-09-01', 'YYYY-MM-DD'));

\c db1
SELECT * FROM Person;
\c db2
SELECT * FROM People;

Evan Carroll · Answer 1 · 2017-05-19T18:03:01.387

This isn't easy, rolling your own is a bad idea. That said, there are a few ways to do this,

disable the triggers and rules (like with Bucardo)
Streaming log replication like with Postgres-BDR.

BDR is the more mature and better solution.

From the overview on Bucardo

A change is made to the table and gets recorded in the bucardo_delta table.
A notice is sent to the main Bucardo daemon, letting it know that the table has changed.
The daemon notifies the controller for that sync and returns to listening.
The controller creates a "kid" to handle the replication, or signals an existing one.
The kid starts a new transaction and disables triggers and rules on the tables in question.
It then gathers a list of which rows have changed since the last replication, and then compares the two to figure out what should be done.
If there is a conflict, then either the standard conflict handler, or a custom one, set per table, is run to sort things out.
Triggers and rules are re-enabled, and the transaction commits.
If the transaction fails, then any custom exception handlers are run.
The child signals to the controller that it has finished.

From the docs on Postgres-BDR

BDR doesn't rely on using triggers to collect changes and insert them into a queue table. Instead it processes the WAL using the changeset extraction mechanism developed by 2ndQuadrant for version 9.4 of PostgreSQL. This solution avoids the write amplification involved in trigger-based solutions.

Avoiding infinite loop in a master-master replication

1 Answers1