[Tomas Vondra <tomas.vondra@enterprisedb.com>]

Hi,

While working on the column filtering patch, which touches about the
same places, I noticed two minor gaps in testing:

1) The regression tests do perform multiple ALTER PUBLICATION commands,
tweaking the row filter. But there are no checks the row filter was
actually modified / stored in the catalog. It might be just thrown away
and no one would notice.

2) There are no pg_dump tests.


So attached are two trivial patched, addressing this. The first one adds
a couple \dRp and \d commands, to show what the catalogs contain. The
second one adds a simple pg_dump test.


regards

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

Attachments: download all (2 files, .tar.gz)

0001-Verify-changing-WHERE-condition-for-a-publi-20220302.patch (text/x-patch, 6.9K) download
From e781f840e38701c63d8b57ff36bd520f2cced6ad Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas@2ndquadrant.com>
Date: Sat, 26 Feb 2022 17:33:09 +0100
Subject: [PATCH 1/3] Verify changing WHERE condition for a publication

Commit 52e4f0cd47 added support for row filters in logical replication,
including regression tests with multiple ALTER PUBLICATION commands,
modifying the row filter. But the tests never verified that the row
filter was actually updated in the catalog. This adds a couple \d and
\dRp commands, to verify the catalog was updated.
---
 src/test/regress/expected/publication.out | 66 +++++++++++++++++++++++
 src/test/regress/sql/publication.sql      |  8 +++
 2 files changed, 74 insertions(+)

diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 3c382e520e4..227ce759486 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -395,15 +395,81 @@ LINE 1: ...ICATION testpub5 ADD TABLE testpub_rf_tbl1 WHERE (b < '2' CO...
 DETAIL:  User-defined collations are not allowed.
 -- ok - NULLIF is allowed
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (NULLIF(1,2) = a);
+\d+ testpub_rf_tbl1
+                              Table "public.testpub_rf_tbl1"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | integer |           |          |         | plain    |              | 
+ b      | text    |           |          |         | extended |              | 
+Publications:
+    "testpub5" WHERE (NULLIF(1, 2) = a)
+
+\dRp+ testpub5
+                                    Publication testpub5
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f          | t       | f       | f       | f         | f
+Tables:
+    "public.testpub_rf_tbl1" WHERE (NULLIF(1, 2) = a)
+
 -- ok - built-in operators are allowed
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (a IS NULL);
+\d+ testpub_rf_tbl1
+                              Table "public.testpub_rf_tbl1"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | integer |           |          |         | plain    |              | 
+ b      | text    |           |          |         | extended |              | 
+Publications:
+    "testpub5" WHERE (a IS NULL)
+
+\dRp+ testpub5
+                                    Publication testpub5
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f          | t       | f       | f       | f         | f
+Tables:
+    "public.testpub_rf_tbl1" WHERE (a IS NULL)
+
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE ((a > 5) IS FALSE);
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (a IS DISTINCT FROM 5);
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE ((a, a + 1) < (2, 3));
 -- ok - built-in type coercions between two binary compatible datatypes are allowed
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (b::varchar < '2');
+\d+ testpub_rf_tbl1
+                              Table "public.testpub_rf_tbl1"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | integer |           |          |         | plain    |              | 
+ b      | text    |           |          |         | extended |              | 
+Publications:
+    "testpub5" WHERE (((b)::character varying)::text < '2'::text)
+
+\dRp+ testpub5
+                                    Publication testpub5
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f          | t       | f       | f       | f         | f
+Tables:
+    "public.testpub_rf_tbl1" WHERE (((b)::character varying)::text < '2'::text)
+
 -- ok - immutable built-in functions are allowed
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl4 WHERE (length(g) < 6);
+\d+ testpub_rf_tbl1
+                              Table "public.testpub_rf_tbl1"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | integer |           |          |         | plain    |              | 
+ b      | text    |           |          |         | extended |              | 
+
+\dRp+ testpub5
+                                    Publication testpub5
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f          | t       | f       | f       | f         | f
+Tables:
+    "public.testpub_rf_tbl4" WHERE (length(g) < 6)
+
 -- fail - user-defined types are not allowed
 CREATE TYPE rf_bug_status AS ENUM ('new', 'open', 'closed');
 CREATE TABLE rf_bug (id serial, description text, status rf_bug_status);
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 3f04d34264a..cd7e0182716 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -203,15 +203,23 @@ CREATE COLLATION user_collation FROM "C";
 ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl1 WHERE (b < '2' COLLATE user_collation);
 -- ok - NULLIF is allowed
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (NULLIF(1,2) = a);
+\d+ testpub_rf_tbl1
+\dRp+ testpub5
 -- ok - built-in operators are allowed
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (a IS NULL);
+\d+ testpub_rf_tbl1
+\dRp+ testpub5
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE ((a > 5) IS FALSE);
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (a IS DISTINCT FROM 5);
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE ((a, a + 1) < (2, 3));
 -- ok - built-in type coercions between two binary compatible datatypes are allowed
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl1 WHERE (b::varchar < '2');
+\d+ testpub_rf_tbl1
+\dRp+ testpub5
 -- ok - immutable built-in functions are allowed
 ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl4 WHERE (length(g) < 6);
+\d+ testpub_rf_tbl1
+\dRp+ testpub5
 -- fail - user-defined types are not allowed
 CREATE TYPE rf_bug_status AS ENUM ('new', 'open', 'closed');
 CREATE TABLE rf_bug (id serial, description text, status rf_bug_status);
-- 
2.34.1



0002-Test-publication-row-filters-in-pg_dump-tes-20220302.patch (text/x-patch, 1.3K) download
From 8ee67dd52a1fc08837aa85979dfc0842cc968012 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.vondra@postgresql.org>
Date: Tue, 1 Mar 2022 15:25:56 +0100
Subject: [PATCH 2/3] Test publication row filters in pg_dump tests

Commit 52e4f0cd47 added support for row filters when replicating tables,
but the commit added no pg_dump tests for this feature. So add at least
a simple test.
---
 src/bin/pg_dump/t/002_pg_dump.pl | 6 +++---
 1 file changed, 3 insertions(+), 3 deletions(-)

diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index dd065c758fa..c3bcef8c0ec 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2407,12 +2407,12 @@ my %tests = (
 		},
 	},
 
-	'ALTER PUBLICATION pub1 ADD TABLE test_second_table' => {
+	'ALTER PUBLICATION pub1 ADD TABLE test_second_table WHERE (col1 = 1)' => {
 		create_order => 52,
 		create_sql =>
-		  'ALTER PUBLICATION pub1 ADD TABLE dump_test.test_second_table;',
+		  'ALTER PUBLICATION pub1 ADD TABLE dump_test.test_second_table WHERE (col1 = 1);',
 		regexp => qr/^
-			\QALTER PUBLICATION pub1 ADD TABLE ONLY dump_test.test_second_table;\E
+			\QALTER PUBLICATION pub1 ADD TABLE ONLY dump_test.test_second_table WHERE ((col1 = 1));\E
 			/xm,
 		like => { %full_runs, section_post_data => 1, },
 		unlike => { exclude_dump_test_schema => 1, },
-- 
2.34.1