Re: row filtering for logical replication

On Tue, Jul 20, 2021 at 6:29 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> I think in terms of referring to old and new rows, we already have
> terminology which we used at various other similar places. See Create
> Rule docs [1]. For where clause, it says "Within condition and
> command, the special table names NEW and OLD can be used to refer to
> values in the referenced table. NEW is valid in ON INSERT and ON
> UPDATE rules to refer to the new row being inserted or updated. OLD is
> valid in ON UPDATE and ON DELETE rules to refer to the existing row
> being updated or deleted.". We need similar things for the WHERE
> clause in publication if we want special syntax to refer to old and
> new rows.
>

I have no doubt we COULD allow references to OLD and NEW in the WHERE
clause, but do we actually want to?
This is what I thought could cause confusion, when mixed with the
model that I previously described.
It's not entirely clear to me exactly how it works, when the WHERE
clause is applied to the OLD and NEW rows, when the WHERE condition
itself can refer to OLD and/or NEW (coupled with the fact that NEW
doesn't make sense for DELETE and OLD doesn't make sense for INSERT).
Combine that with the fact that a publication can have multiple tables
each with their own WHERE clause, and tables can be dropped/(re)added
to the publication with a different WHERE clause, and it starts to get
a little complicated working out exactly what the result should be.

Regards,
Greg Nancarrow
Fujitsu Australia