Re: BDR - triggers on receiving node?

On 25 March 2015 at 19:15, Peter Mogensen <apm(at)one(dot)com> wrote:

> Hi,
>
> Traditionally it hadn't made much sense to fire a trigger on a receiving
> replica node (slave) - for many reasons, including it being read-only.
>
> But with BDRs multi parter, partial replication and the possibility that
> some tables are either actually or logically local to a single node,
> there's scenarios where this makes sense.
>
> Is it possible?
>

It might be possible to fire FOR EACH ROW triggers, though this is not
currently supported. BDR does its row changes at a lower level than SQL, so
it doesn't invoke INSERT, UPDATE or DELETE statements directly. That helps
with the efficiently of apply, but also means you can't easily fire
triggers on those changes simply by creating ENABLE REPLICA or ENABLE
ALWAYS triggers and using session_replication_role = 'replica'.

It is not practical to fire FOR EACH STATEMENT triggers, because BDR has no
idea what upstream statement caused which change.

Most likely BDR would need to look for ENABLE REPLICA / ENABLE ALWAYS
triggers and fire them directly. Or a new pseudo-trigger callback procedure
for row-apply could be added, somewhat similar to the existing
functionality for conflict handlers.

> Say ... I have a table in a BDR replicated database with an "ON UPDATE"
> trigger. - and that trigger wants to locally find out the local
> txid_snapshot_xmin() when a change was applied to the local node.
>

Why would you want to do that? Just out of interest?

> Not the upstream txid of the transaction which actually made the change
> originally (which only makes sense on that node) - but on the local node
> receiving the change via BDR LLSR.
>
> Can that be done?

At this time I think you'd have to patch the BDR apply worker to do what
you wanted.

There are already a few areas where downstream filtering or apply hooks are
of interest, so this might be an area that can be enhanced in future.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services