Horton

Thread

Commits

  1. Have logical replication subscriber fire column triggers

  1. logical replication does not fire per-column triggers

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2019-12-13T13:25:47Z

    The logical replication subscription side does not fire per-column 
update triggers when applying an update change.  (Per-table update 
triggers work fine.)  This patch fixes that.  Should be backpatched to PG10.

A patch along these lines is also necessary to handle triggers involving 
generated columns in the apply worker.  I'll work on that separately.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  2. Re: logical replication does not fire per-column triggers

    Euler Taveira <euler@timbira.com.br> — 2019-12-14T02:13:55Z

    Em sex., 13 de dez. de 2019 às 10:26, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> escreveu:
>
> The logical replication subscription side does not fire per-column
> update triggers when applying an update change.  (Per-table update
> triggers work fine.)  This patch fixes that.  Should be backpatched to PG10.
>
Using the regression test example, table tab_fk_ref have columns id
and bid. If you add a trigger "BEFORE UPDATE OF bid" into subscriber
that fires on replica, it will always fire even if you are **not**
changed bid in publisher. In logical replication protocol all columns
were changed unless it is a (unchanged) TOAST column (if a column is
part of the PK/REPLICA IDENTITY we can compare both values and figure
out if the value changed, however, we can't ensure that a value
changed for the other columns -- those that are not PK/REPLICA
IDENTITY). It is clear that not firing the trigger is wrong but firing
it when you say that you won't fire it is also wrong. Whichever
behavior we choose, limitation should be documented. I prefer the
behavior that ignores "OF col1" and always fire the trigger (because
we can add a filter inside the function/procedure).

+ /* Populate updatedCols for trigger manager */
Add a comment that explains it is not possible to (always) determine
if a column changed. Hence, "OF col1" syntax will be ignored.

+ for (int i = 0; i < remoteslot->tts_tupleDescriptor->natts; i++)
+ {
+ RangeTblEntry *target_rte = list_nth(estate->es_range_table, 0);
+
It should be outside the loop.

+ if (newtup.changed)
It should be newtup.changed[i].

You should add a test that exposes "ignore OF col1" such as:

$node_publisher->safe_psql('postgres',
    "UPDATE tab_fk_ref SET id = 6 WHERE id = 1;");


-- 
   Euler Taveira                                   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

  3. Re: logical replication does not fire per-column triggers

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2019-12-16T13:37:00Z

    On 2019-12-14 03:13, Euler Taveira wrote:
> Using the regression test example, table tab_fk_ref have columns id
> and bid. If you add a trigger "BEFORE UPDATE OF bid" into subscriber
> that fires on replica, it will always fire even if you are **not**
> changed bid in publisher. In logical replication protocol all columns
> were changed unless it is a (unchanged) TOAST column (if a column is
> part of the PK/REPLICA IDENTITY we can compare both values and figure
> out if the value changed, however, we can't ensure that a value
> changed for the other columns -- those that are not PK/REPLICA
> IDENTITY). It is clear that not firing the trigger is wrong but firing
> it when you say that you won't fire it is also wrong. Whichever
> behavior we choose, limitation should be documented. I prefer the
> behavior that ignores "OF col1" and always fire the trigger (because
> we can add a filter inside the function/procedure).

There is a small difference: If the subscriber has extra columns not 
present on the publisher, then a column trigger covering only columns in 
published column set will not fire.

In practice, a column trigger is just an optimization.  The column it is 
triggering on might not have actually changed.  The opposite is worse, 
not firing the trigger when the column actually has changed.

> + /* Populate updatedCols for trigger manager */
> Add a comment that explains it is not possible to (always) determine
> if a column changed. Hence, "OF col1" syntax will be ignored.

done

> + for (int i = 0; i < remoteslot->tts_tupleDescriptor->natts; i++)
> + {
> + RangeTblEntry *target_rte = list_nth(estate->es_range_table, 0);
> +
> It should be outside the loop.

fixed

> + if (newtup.changed)
> It should be newtup.changed[i].

fixed

> You should add a test that exposes "ignore OF col1" such as:
> 
> $node_publisher->safe_psql('postgres',
>      "UPDATE tab_fk_ref SET id = 6 WHERE id = 1;");

done

New patch attached.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  4. Re: logical replication does not fire per-column triggers

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2020-01-06T10:40:10Z

    On 2019-12-16 14:37, Peter Eisentraut wrote:
> New patch attached.

I have committed this and backpatched it to PG10.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services