Date: Fri, 4 Jan 2019 05:20:48 -0700 (MST)
From: PegoraroF10 <marcos@f10.com.br>
To: pgsql-docs@postgresql.org
Message-ID: <1546604448278-0.post@n3.nabble.com>
Subject: UPDATE OF on REPLICA TRIGGERS
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

We tried a lot, maybe for some days, to have some triggers working properly
on a replica server. So we read a discussion that is not possible to have
UPDATE OF in a trigger which works only on REPLICA. Our tests was production
using version 10 and replica using version 11.

I thing it would be helpful if the docs had this explained. Or an exception
would occur when we try to create a REPLICA TRIGGER with UPDATE OF.

Document it with something like ... replace your "UPDATE OF col1, col2" when
you create your trigger which works only on replica for "new.col1 is
distinct of old.col1" inside your trigger function.

CREATE OR REPLACE FUNCTION CalculateSomethingOnReplica() RETURNS TRIGGER AS
$$
BEGIN
  IF tg_op = 'update' THEN
    IF new.col1 IS NOT DISTINCT FROM old.col1 THEN
      --DoTheCalc
    END IF;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER CalculateSomethingOnReplica AFTER UPDATE ON MyTable FOR EACH
ROW EXECUTE PROCEDURE CalculateSomethingOnReplica();
ALTER TABLE MyTable ENABLE REPLICA TRIGGER CalculateSomethingOnReplica;


--
Sent from: http://www.postgresql-archive.org/PostgreSQL-docs-f2165602.html