Horton

RE: row filtering for logical replication

Shinoda, Noriyoshi <noriyoshi.shinoda@hpe.com>

View thread
From: "Shinoda, Noriyoshi (PN Japan FSIP)" <noriyoshi.shinoda@hpe.com>
To: Amit Kapila <amit.kapila16@gmail.com>, Euler Taveira <euler@eulerto.com>
Cc: "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, Peter Smith <smithpb2250@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, "Greg Nancarrow" <gregn4422@gmail.com>, vignesh C <vignesh21@gmail.com>, "Ajin Cherian" <itsajin@gmail.com>, "tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.com>, Dilip Kumar <dilipbalaut@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>, Amit Langote <amitlangote09@gmail.com>, "PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>
Date: 2022-02-24T02:13:31Z
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

Hi, 
Thank you for developing of the great feature. 
If multiple tables are specified when creating a PUBLICATION, 
is it supposed that the WHERE clause condition is given to only one table? 
I attached the operation log below.

--- operation log ---
postgres=> CREATE TABLE data1(c1 INT PRIMARY KEY, c2 VARCHAR(10));
CREATE TABLE
postgres=> CREATE TABLE data2(c1 INT PRIMARY KEY, c2 VARCHAR(10));
CREATE TABLE
postgres=> CREATE PUBLICATION pub1 FOR TABLE data1,data2 WHERE (c1 < 1000);
CREATE PUBLICATION
postgres=> \d data1
                      Table "public.data1"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           | not null |
 c2     | character varying(10) |           |          |
Indexes:
    "data1_pkey" PRIMARY KEY, btree (c1)
Publications:
    "pub1"

postgres=> \d data2
                      Table "public.data2"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           | not null |
 c2     | character varying(10) |           |          |
Indexes:
    "data2_pkey" PRIMARY KEY, btree (c1)
Publications:
    "pub1" WHERE (c1 < 1000)

postgres=> SELECT prrelid, prqual FROM pg_publication_rel;
 prrelid |                                       prqual
---------+-----------------------------------------------------------------------------
   16408 |
   16413 | {OPEXPR :opno 97 :opfuncid 66 :opresulttype 16 :opretset false :opcol
lid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
:varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 53} {CONST :con
sttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnu
ll false :location 58 :constvalue 4 [ -24 3 0 0 0 0 0 0 ]}) :location 56}
(2 rows)

Regards,
Noriyoshi Shinoda

-----Original Message-----
From: Amit Kapila <amit.kapila16@gmail.com> 
Sent: Wednesday, February 23, 2022 11:06 AM
To: Euler Taveira <euler@eulerto.com>
Cc: houzj.fnst@fujitsu.com; Peter Smith <smithpb2250@gmail.com>; Alvaro Herrera <alvherre@alvh.no-ip.org>; Greg Nancarrow <gregn4422@gmail.com>; vignesh C <vignesh21@gmail.com>; Ajin Cherian <itsajin@gmail.com>; tanghy.fnst@fujitsu.com; Dilip Kumar <dilipbalaut@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>; Amit Langote <amitlangote09@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: row filtering for logical replication

On Tue, Feb 22, 2022 at 4:47 AM Euler Taveira <euler@eulerto.com> wrote:
>
> On Thu, Feb 17, 2022, at 3:36 AM, Amit Kapila wrote:
>
> As there is a new version, I would like to wait for a few more days 
> before committing. I am planning to commit this early next week (by
> Tuesday) unless others or I see any more things that can be improved.
>
> Amit, I don't have additional comments or suggestions. Let's move on. 
> Next topic. :-)
>

Pushed!

--
With Regards,
Amit Kapila.