Horton
Menu

Re: Column Filtering in Logical Replication

vignesh C <vignesh21@gmail.com>

View thread
From: vignesh C <vignesh21@gmail.com>
To: Peter Smith <smithpb2250@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2022-08-25T09:38:39Z
Lists: pgsql-hackers 
On Tue, Aug 23, 2022 at 7:52 AM Peter Smith <smithpb2250@gmail.com> wrote:
>
> On Mon, Aug 22, 2022 at 9:25 PM vignesh C <vignesh21@gmail.com> wrote:
> >
> ...
>
> > Few comments:
> > 1) I felt no expressions are allowed in case of column filters. Only
> > column names can be specified. The second part of the sentence
> > confuses what is allowed and what is not allowed. Won't it be better
> > to remove the second sentence and mention that only column names can
> > be specified.
> > +   <para>
> > +    Column list can contain only simple column references. Complex
> > +    expressions, function calls etc. are not allowed.
> > +   </para>
> >
>
> This wording was lifted verbatim from the commit message [1]. But I
> see your point that it just seems to be overcomplicating a simple
> rule. Modified as suggested.
>
> > 2) tablename should be table name.
> > +   <para>
> > +    A column list is specified per table following the tablename, and
> > enclosed by
> > +    parenthesis. See <xref linkend="sql-createpublication"/> for details.
> > +   </para>
> >
> > We have used table name in the same page in other instances like:
> > a) The row filter is defined per table. Use a WHERE clause after the
> > table name for each published table that requires data to be filtered
> > out. The WHERE clause must be enclosed by parentheses.
> > b) The tables are matched between the publisher and the subscriber
> > using the fully qualified table name.
> >
>
> Fixed as suggested.
>
> > 3) One small whitespace issue:
> > git am v2-0001-Column-List-replica-identity-rules.patch
> > Applying: Column List replica identity rules.
> > .git/rebase-apply/patch:30: trailing whitespace.
> >    if the publication publishes only <command>INSERT</command> operations.
> > warning: 1 line adds whitespace errors.
> >
>
> Fixed.
>
> ~~~
>
> PSA the v3* patch set.

Thanks for the updated patch.
Few comments:
1) We can shuffle the columns in publisher table and subscriber to
show that the order of the column does not matter
+   <para>
+    Create a publication <literal>p1</literal>. A column list is defined for
+    table <literal>t1</literal> to reduce the number of columns that will be
+    replicated.
+<programlisting>
+test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 (id, a, b, c);
+CREATE PUBLICATION
+test_pub=#
+</programlisting></para>

2) We can try to keep the line content to less than 80 chars
+   <para>
+    A column list is specified per table following the tablename, and
enclosed by
+    parenthesis. See <xref linkend="sql-createpublication"/> for details.
+   </para>

3) tablename should be table name like it is used in other places
+   <para>
+    A column list is specified per table following the tablename, and
enclosed by
+    parenthesis. See <xref linkend="sql-createpublication"/> for details.
+   </para>

4a) In the below, you could include mentioning "Only the column list
data of publication <literal>p1</literal> are replicated."
+    <para>
+     Insert some rows to table <literal>t1</literal>.
+<programlisting>
+test_pub=# INSERT INTO t1 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1');
+INSERT 0 1

4b) In the above, we could mention that the insert should be done on
the "publisher side" as the previous statements are executed on the
subscriber side.

Regards,
Vignesh

Commits

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

  1. Doc: Explain about Column List feature.

  2. Doc: fix column list vs. replica identity rules.

  3. Prohibit combining publications with different column lists.

  4. Fix the check to limit sync workers.

  5. Wait for subscription to sync in t/031_column_list.sql

  6. Move prattrs to the pg_publication_rel section in docs

  7. Allow specifying column lists for logical replication

  8. Fix row filters with multiple publications

  9. Fix publish_as_relid with multiple publications

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

  11. Add index on pg_publication_rel.prpubid

  12. Avoid using DefElemAction in AlterPublicationStmt

  13. Small cleanups related to PUBLICATION framework code

  14. Add PublicationTable and PublicationRelInfo structs

  15. Fix various concurrency issues in logical replication worker launching