Horton

Re: row filtering for logical replication

Tomas Vondra <tomas.vondra@enterprisedb.com>

View thread
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Euler Taveira <euler@eulerto.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Peter Smith <smithpb2250@gmail.com>, Rahila Syed <rahilasyed90@gmail.com>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Önder Kalacı <onderkalaci@gmail.com>, japin <japinli@hotmail.com>, Michael Paquier <michael@paquier.xyz>, David Steele <david@pgmasters.net>, Craig Ringer <craig@2ndquadrant.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, Amit Langote <amitlangote09@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2021-07-14T14:33:49Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Release cache tuple when no longer needed

  2. Add some additional tests for row filters in logical replication.

  3. Fix one of the tests introduced in commit 52e4f0cd47.

  4. Allow specifying row filters for logical replication of tables.

  5. Move scanint8() to numutils.c

  6. Replace Test::More plans with done_testing

  7. Reduce relcache access in WAL sender streaming logical changes

  8. Small cleanups related to PUBLICATION framework code

  9. Add a view to show the stats of subscription workers.

  10. Allow publishing the tables of schema.

  11. Doc: improve documentation of CREATE/ALTER SUBSCRIPTION.

  12. Add PublicationTable and PublicationRelInfo structs

  13. Remove unused argument "txn" in maybe_send_schema().

  14. Add prepare API support for streaming transactions in logical replication.

  15. Unify PostgresNode's new() and get_new_node() methods

  16. Use l*_node() family of functions where appropriate

  17. Add support for prepared transactions to built-in logical replication.

  18. Restore the portal-level snapshot after procedure COMMIT/ROLLBACK.

  19. Rename a parse node to be more general

  20. Remove unused column atttypmod from initial tablesync query

  21. SEARCH and CYCLE clauses

On 7/14/21 2:50 PM, Amit Kapila wrote:
> On Wed, Jul 14, 2021 at 3:58 PM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>>
>> On 7/14/21 7:39 AM, Amit Kapila wrote:
>>> On Wed, Jul 14, 2021 at 6:28 AM Euler Taveira <euler@eulerto.com> wrote:
>>>>
>>>> On Tue, Jul 13, 2021, at 6:06 PM, Alvaro Herrera wrote:
>>>>
>>>> 1. if you use REPLICA IDENTITY FULL, then the expressions would work
>>>> even if they use any other column with DELETE.  Maybe it would be
>>>> reasonable to test for this in the code and raise an error if the
>>>> expression requires a column that's not part of the replica identity.
>>>> (But that could be relaxed if the publication does not publish
>>>> updates/deletes.)
>>>>
>>>
>>> +1.
>>>
>>>> I thought about it but came to the conclusion that it doesn't worth it.  Even
>>>> with REPLICA IDENTITY FULL expression evaluates to false if the column allows
>>>> NULL values. Besides that REPLICA IDENTITY is changed via another DDL (ALTER
>>>> TABLE) and you have to make sure you don't allow changing REPLICA IDENTITY
>>>> because some row filter uses the column you want to remove from it.
>>>>
>>>
>>> Yeah, that is required but is it not feasible to do so?
>>>
>>>> 2. For UPDATE, does the expression apply to the old tuple or to the new
>>>> tuple?  You say it's the new tuple, but from the user point of view I
>>>> think it would make more sense that it would apply to the old tuple.
>>>> (Of course, if you're thinking that the R.I. is the PK and the PK is
>>>> never changed, then you don't really care which one it is, but I bet
>>>> that some people would not like that assumption.)
>>>>
>>>> New tuple. The main reason is that new tuple is always there for UPDATEs.
>>>>
>>>
>>> I am not sure if that is a very good reason to use a new tuple.
>>>
>>
>> True. Perhaps we should look at other places with similar concept of
>> WHERE conditions and old/new rows, and try to be consistent with those?
>>
>> I can think of:
>>
>> 1) updatable views with CHECK option
>>
>> 2) row-level security
>>
>> 3) triggers
>>
>> Is there some reasonable rule which of the old/new tuples (or both) to
>> use for the WHERE condition? Or maybe it'd be handy to allow referencing
>> OLD/NEW as in triggers?
>>
> 
> I think apart from the above, it might be good if we can find what
> some other databases does in this regard?
> 

Yeah, that might tell us what the users would like to do with it. I did 
some quick search, but haven't found much :-( The one thing I found is 
that Debezium [1] allows accessing both the "old" and "new" rows through 
value.before and value.after, and use both for filtering.

I haven't found much about how this works in other databases, sadly.

Perhaps the best way forward is to stick to the approach that INSERT 
uses new, DELETE uses old and UPDATE works as DELETE+INSERT (probably), 
and leave anything fancier (like being able to reference both versions 
of the row) for a future patch.


[1] 
https://wanna-joke.com/wp-content/uploads/2015/01/german-translation-comics-science.jpg

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company