0001-Verify-changing-WHERE-condition-for-a-publi-20220307.patch
text/x-patch
Filename: 0001-Verify-changing-WHERE-condition-for-a-publi-20220307.patch
Type: text/x-patch
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch 0001
Subject: Verify changing WHERE condition for a publication
| File | + | − |
|---|---|---|
| src/test/regress/expected/publication.out | 66 | 0 |
| src/test/regress/sql/publication.sql | 8 | 0 |
From 362723188a36b7e0147df7404fe64aa2e4f48bfb 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 4e191c120ac..6d16600aaea 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -417,15 +417,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 5457c56b33f..c135a601a30 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -206,15 +206,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