pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Amit Kapila <akapila@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Allow specifying row filters for logical replication of tables.
Date: Tue, 22 Feb 2022 02:54:32 +0000
Message-ID: <E1nMLJw-0004bf-Vg@gemulon.postgresql.org> (raw)

Allow specifying row filters for logical replication of tables.

This feature adds row filtering for publication tables. When a publication
is defined or modified, an optional WHERE clause can be specified. Rows
that don't satisfy this WHERE clause will be filtered out. This allows a
set of tables to be partially replicated. The row filter is per table. A
new row filter can be added simply by specifying a WHERE clause after the
table name. The WHERE clause must be enclosed by parentheses.

The row filter WHERE clause for a table added to a publication that
publishes UPDATE and/or DELETE operations must contain only columns that
are covered by REPLICA IDENTITY. The row filter WHERE clause for a table
added to a publication that publishes INSERT can use any column. If the
row filter evaluates to NULL, it is regarded as "false". The WHERE clause
only allows simple expressions that don't have user-defined functions,
user-defined operators, user-defined types, user-defined collations,
non-immutable built-in functions, or references to system columns. These
restrictions could be addressed in the future.

If you choose to do the initial table synchronization, only data that
satisfies the row filters is copied to the subscriber. If the subscription
has several publications in which a table has been published with
different WHERE clauses, rows that satisfy ANY of the expressions will be
copied. If a subscriber is a pre-15 version, the initial table
synchronization won't use row filters even if they are defined in the
publisher.

The row filters are applied before publishing the changes. If the
subscription has several publications in which the same table has been
published with different filters (for the same publish operation), those
expressions get OR'ed together so that rows satisfying any of the
expressions will be replicated.

This means all the other filters become redundant if (a) one of the
publications have no filter at all, (b) one of the publications was
created using FOR ALL TABLES, (c) one of the publications was created
using FOR ALL TABLES IN SCHEMA and the table belongs to that same schema.

If your publication contains a partitioned table, the publication
parameter publish_via_partition_root determines if it uses the partition's
row filter (if the parameter is false, the default) or the root
partitioned table's row filter.

Psql commands \dRp+ and \d <table-name> will display any row filters.

Author: Hou Zhijie, Euler Taveira, Peter Smith, Ajin Cherian
Reviewed-by: Greg Nancarrow, Haiying Tang, Amit Kapila, Tomas Vondra, Dilip Kumar, Vignesh C, Alvaro Herrera, Andres Freund, Wei Wang
Discussion: https://www.postgresql.org/message-id/flat/CAHE3wggb715X%2BmK_DitLXF25B%3DjE6xyNCH4YOwM860JR7HarGQ%4...

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/52e4f0cd472d39d07732b99559989ea3b615be78

Modified Files
--------------
doc/src/sgml/catalogs.sgml                  |   9 +
doc/src/sgml/ref/alter_publication.sgml     |  12 +-
doc/src/sgml/ref/alter_subscription.sgml    |   7 +-
doc/src/sgml/ref/create_publication.sgml    |  38 +-
doc/src/sgml/ref/create_subscription.sgml   |  27 +-
src/backend/catalog/pg_publication.c        |  59 +-
src/backend/commands/publicationcmds.c      | 583 ++++++++++++++++++-
src/backend/executor/execReplication.c      |  39 +-
src/backend/nodes/copyfuncs.c               |   1 +
src/backend/nodes/equalfuncs.c              |   1 +
src/backend/parser/gram.y                   |  38 +-
src/backend/replication/logical/proto.c     |  36 +-
src/backend/replication/logical/tablesync.c | 142 ++++-
src/backend/replication/pgoutput/pgoutput.c | 833 ++++++++++++++++++++++++----
src/backend/utils/cache/relcache.c          |  98 +++-
src/bin/pg_dump/pg_dump.c                   |  30 +-
src/bin/pg_dump/pg_dump.h                   |   1 +
src/bin/psql/describe.c                     |  26 +-
src/bin/psql/tab-complete.c                 |  29 +-
src/include/catalog/catversion.h            |   2 +-
src/include/catalog/pg_publication.h        |  18 +-
src/include/catalog/pg_publication_rel.h    |   6 +
src/include/commands/publicationcmds.h      |   2 +
src/include/nodes/parsenodes.h              |   1 +
src/include/replication/logicalproto.h      |  11 +-
src/include/replication/pgoutput.h          |   1 +
src/include/replication/reorderbuffer.h     |   6 +-
src/include/utils/rel.h                     |   2 +-
src/include/utils/relcache.h                |   5 +-
src/test/regress/expected/publication.out   | 352 ++++++++++++
src/test/regress/sql/publication.sql        | 236 ++++++++
src/test/subscription/t/028_row_filter.pl   | 695 +++++++++++++++++++++++
src/tools/pgindent/typedefs.list            |   3 +
33 files changed, 3113 insertions(+), 236 deletions(-)



Message-ID: <E1nMLJw-0004bf-Vg@gemulon.postgresql.org>
Permalink:  ../E1nMLJw-0004bf-Vg@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1nMLJw-0004bf-Vg@gemulon.postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: akapila@postgresql.org
  Subject: Re: pgsql: Allow specifying row filters for logical replication of tables.
  In-Reply-To: <E1nMLJw-0004bf-Vg@gemulon.postgresql.org>
  References: <E1nMLJw-0004bf-Vg@gemulon.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox