Re: [BUGS] 10.0: Logical replication doesn't execute BEFORE UPDATEOF trigger - Mailing list pgsql-bugs

From Aleksander Alekseev
Subject Re: [BUGS] 10.0: Logical replication doesn't execute BEFORE UPDATEOF trigger
Date
Msg-id 20171010152250.GA30140@e733.localdomain
Whole thread Raw
In response to Re: [BUGS] 10.0: Logical replication doesn't execute BEFORE UPDATE OF trigger  (Petr Jelinek <petr.jelinek@2ndquadrant.com>)
Responses Re: [BUGS] 10.0: Logical replication doesn't execute BEFORE UPDATE OF trigger
List pgsql-bugs
Hi Petr,

> let me start by saying that my view is that this is simply a
> documentation bug. Meaning that I didn't document that it does not work,
> but I also never intended it to work. Main reason is that we can't
> support the semantics of "UPDATE OF" correctly (see bellow). And I think
> it's better to not support something at all rather than making it behave
> differently in different cases.
>
> Now about the proposed patch, I don't think this is correct way to
> support this as it will only work when either PRIMARY KEY column was
> changed or when REPLICA IDENTITY is set to FULL for the table. And even
> then it will have very different semantics from how it works when the
> row is updated by SQL statement (all non-toasted columns will be
> reported as changed regardless of actually being changed or not).
>
> The more proper way to do this would be to run data comparison of the
> new tuple and the existing tuple values which a) will have different
> behavior than normal "UPDATE OF" triggers have because we still can't
> tell what columns were mentioned in the original query and b) will not
> exactly help performance (and perhaps c) one can write the trigger to
> behave this way already without impacting all other use-cases).

I personally think that solution proposed by Masahiko is much better
than current behavior. We could document current limitations you've
mentioned and probably add a corresponding warning during execution of
ALTER TABLE ... ENABLE REPLICA TRIGGER. I don't insist on this
particular approach though.

What I really don't like is that PostgreSQL allows to create something
that supposedly should work but in fact doesn't. Such behavior is
obviously a bug. So as an alternative we could just return an error in
response to ALTER TABLE ... ENABLE REPLICA TRIGGER query for triggers
that we know will never be executed.

--
Best regards,
Aleksander Alekseev

pgsql-bugs by date:

Previous
From: Дилян Палаузов
Date:
Subject: Re: [BUGS] ./configure error: Cannot find a working 64-bit integertype
Next
From: Alvaro Herrera
Date:
Subject: Re: [BUGS] ./configure error: Cannot find a working 64-bit integertype