Hi, Here are my review comments for patch v22-0001
All comments now are only for the TAP test.
======
src/test/subscription/t/011_generated.pl
1. I added all new code for the missing combination test case
"gen-to-missing". See nitpicks diff.
- create a separate publication for this "tab_gen_to_missing" table
because the test gives subscription errors.
- for the initial data
- for the replicated data
~~~
2. I added sub1 and sub2 subscriptions for every combo test
(previously some were absent). See nitpicks diff.
~~~
3. There was a missing test case for nogen-to-gen combination, and
after experimenting with this I am getting a bit suspicious,
Currently, it seems that if a COPY is attempted then the error would
be like this:
2024-08-01 17:16:45.110 AEST [18942] ERROR: column "b" is a generated column
2024-08-01 17:16:45.110 AEST [18942] DETAIL: Generated columns cannot
be used in COPY.
OTOH, if a COPY is not attempted (e.g. copy_data = false) then patch
0001 allows replication to happen. And the generated value of the
subscriber "b" takes precedence.
I have included these tests in the nitpicks diff of patch 0001.
Those results weren't exactly what I was expecting. That is why it is
so important to include *every* test combination in these TAP tests --
because unless we know how it works today, we won't know if we are
accidentally breaking the current behaviour with the other (0002,
0003) patches.
Please experiment in patches 0001 and 0002 using tab_nogen_to_gen more
to make sure the (new?) patch errors make sense and don't overstep by
giving ERRORs when they should not.
~~~~
Also, many other smaller issues/changes were done:
~~~
Creating tables:
nitpick - rearranged to keep all combo test SQLs in a consistent order
throughout this file
1/ gen-to-gen
2/ gen-to-nogen
3/ gen-to-missing
4/ missing-to-gen
5/ nogen-to-gen
nitpick - fixed the wrong comment for CREATE TABLE tab_nogen_to_gen.
nitpick - tweaked some CREATE TABLE comments for consistency.
nitpick - in the v22 patch many of the generated col 'b' use different
computations for every test. It makes it unnecessarily difficult to
read/review the expected results. So, I've made them all the same. Now
computation is "a * 2" on the publisher side, and "a * 22" on the
subscriber side.
~~~
Creating Publications and Subscriptions:
nitpick - added comment for all the CREATE PUBLICATION
nitpick - added comment for all the CREATE SUBSCRIPTION
nitpick - I moved the note about copy_data = false to where all the
node_subscriber2 subscriptions are created. Also, don't explicitly
refer to "patch 000" in the comment, because that will not make any
sense after getting pushed.
nitpick - I changed many subscriber names to consistently use "sub1"
or "sub2" within the name (this is the visual cue of which
node_subscriber<n> they are on). e.g.
/regress_sub_combo2/regress_sub2_combo/
~~~
Initial Sync tests:
nitpick - not sure if it is possible to do the initial data tests for
"nogen_to_gen" in the normal place. For now, it is just replaced by a
comment.
NOTE - Maybe this should be refactored later to put all the initial
data checks in one place. I'll think about this point more in the next
review.
~~~
nitpick - Changed cleanup I drop subscriptions before publications.
nitpick - remove the unnecessary blank line at the end.
======
Please see the attached diffs patch (apply it atop patch 0001) which
includes all the nipick changes mentioned above.
~~
BTW, For a quicker turnaround and less churning please consider just
posting the v23-0001 by itself instead of waiting to rebase all the
subsequent patches. When 0001 settles down some more then rebase the
others.
~~
Also, please run the indentation tool over this code ASAP.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
diff --git a/src/test/subscription/t/011_generated.pl
b/src/test/subscription/t/011_generated.pl
index 05b83f6..504714a 100644
--- a/src/test/subscription/t/011_generated.pl
+++ b/src/test/subscription/t/011_generated.pl
@@ -34,59 +34,60 @@ $node_subscriber->safe_psql('postgres',
"CREATE TABLE tab1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *
22) STORED, c int)"
);
+# tab_gen_to_gen:
# publisher-side has generated col 'b'.
# subscriber-side has generated col 'b', with different computation.
$node_publisher->safe_psql('postgres',
- "CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a + 10)
STORED)");
+ "CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 2)
STORED)");
$node_subscriber->safe_psql('postgres',
- "CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a + 20)
STORED)");
+ "CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22)
STORED)");
$node_subscriber2->safe_psql('postgres',
- "CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a + 20)
STORED)");
+ "CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22)
STORED)");
+# tab_gen_to_nogen:
# publisher-side has generated col 'b'.
# subscriber-side has non-generated col 'b'.
$node_publisher->safe_psql('postgres',
"CREATE TABLE tab_gen_to_nogen (a int, b int GENERATED ALWAYS AS (a *
2) STORED)");
-$node_subscriber->safe_psql('postgres', "CREATE TABLE tab_gen_to_nogen (a int,
b int)");
-$node_subscriber2->safe_psql('postgres', "CREATE TABLE tab_gen_to_nogen (a
int, b int)");
+$node_subscriber->safe_psql('postgres',
+ "CREATE TABLE tab_gen_to_nogen (a int, b int)");
+$node_subscriber2->safe_psql('postgres',
+ "CREATE TABLE tab_gen_to_nogen (a int, b int)");
+# tab_gen_to_missing:
# publisher-side has generated col 'b'.
# subscriber-side col 'b' is missing.
$node_publisher->safe_psql('postgres',
- "CREATE TABLE tab_gen_to_missing (a int, b int GENERATED ALWAYS AS (a *
2) STORED)"
-);
+ "CREATE TABLE tab_gen_to_missing (a int, b int GENERATED ALWAYS AS (a *
2) STORED)");
$node_subscriber->safe_psql('postgres',
- "CREATE TABLE tab_gen_to_missing (a int)"
-);
+ "CREATE TABLE tab_gen_to_missing (a int)");
$node_subscriber2->safe_psql('postgres',
- "CREATE TABLE tab_gen_to_missing (a int)"
-);
+ "CREATE TABLE tab_gen_to_missing (a int)");
+# tab_missing_to_gen:
# publisher-side col 'b' is missing.
-# subscriber-side col 'b' is generated.
-$node_publisher->safe_psql('postgres', "CREATE TABLE tab_nogen_to_gen (a int,
b int)");
+# subscriber-side has generated col 'b'.
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab_missing_to_gen (a int)");
$node_subscriber->safe_psql('postgres',
- "CREATE TABLE tab_nogen_to_gen (a int, b int GENERATED ALWAYS AS (a *
22) STORED)");
+ "CREATE TABLE tab_missing_to_gen (a int, b int GENERATED ALWAYS AS (a *
22) STORED)");
$node_subscriber2->safe_psql('postgres',
- "CREATE TABLE tab_nogen_to_gen (a int, b int GENERATED ALWAYS AS (a *
22) STORED)");
+ "CREATE TABLE tab_missing_to_gen (a int, b int GENERATED ALWAYS AS (a *
22) STORED)");
-# publisher-side col 'b' is missing.
-# subscriber-side col 'b' is generated.
+# tab_nogen_to_gen:
+# publisher-side has non-generated col 'b'.
+# subscriber-side has generated col 'b'.
$node_publisher->safe_psql('postgres',
- "CREATE TABLE tab_missing_to_gen (a int)"
-);
+ "CREATE TABLE tab_nogen_to_gen (a int, b int)");
$node_subscriber->safe_psql('postgres',
- "CREATE TABLE tab_missing_to_gen (a int, b int GENERATED ALWAYS AS (a *
2) STORED)"
-);
+ "CREATE TABLE tab_nogen_to_gen (a int, b int GENERATED ALWAYS AS (a *
22) STORED)");
$node_subscriber2->safe_psql('postgres',
- "CREATE TABLE tab_missing_to_gen (a int, b int GENERATED ALWAYS AS (a *
2) STORED)"
-);
-
+ "CREATE TABLE tab_nogen_to_gen (a int, b int GENERATED ALWAYS AS (a *
22) STORED)");
# tab_order:
-# publisher-side has generated cols 'b' and 'c' but
-# subscriber-side has non-generated col 'b', and generated-col 'c'
-# where columns on publisher/subscriber are in a different order
+# publisher-side has generated cols 'b' and 'c'.
+# subscriber-side has non-generated col 'b', and generated-col 'c'.
+# columns on publisher/subscriber are in a different order
$node_publisher->safe_psql('postgres',
"CREATE TABLE tab_order (a int, b int GENERATED ALWAYS AS (a * 2)
STORED, c int GENERATED ALWAYS AS (a * 2) STORED)"
);
@@ -107,6 +108,7 @@ $node_subscriber2->safe_psql('postgres',
$node_publisher->safe_psql('postgres',
"INSERT INTO tab1 (a) VALUES (1), (2), (3)");
+
$node_publisher->safe_psql('postgres',
"INSERT INTO tab_gen_to_gen (a) VALUES (1), (2), (3)");
$node_publisher->safe_psql('postgres',
@@ -114,52 +116,58 @@ $node_publisher->safe_psql('postgres',
$node_publisher->safe_psql('postgres',
"INSERT INTO tab_gen_to_missing (a) VALUES (1), (2), (3)");
$node_publisher->safe_psql('postgres',
- "INSERT INTO tab_nogen_to_gen (a, b) VALUES (1, 1), (2, 2), (3, 3)");
-$node_publisher->safe_psql('postgres',
"INSERT INTO tab_missing_to_gen (a) VALUES (1), (2), (3)");
$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_nogen_to_gen (a, b) VALUES (1, 1), (2, 2), (3, 3)");
+
+$node_publisher->safe_psql('postgres',
"INSERT INTO tab_order (a) VALUES (1), (2), (3)");
$node_publisher->safe_psql('postgres',
"INSERT INTO tab_alter (a) VALUES (1), (2), (3)");
+# create publications
+#
+# pub_combo_gen_to_missing is not included in pub_combo, because some tests
give errors.
+
$node_publisher->safe_psql('postgres',
- "CREATE PUBLICATION regress_pub1 FOR TABLE tab1");
+ "CREATE PUBLICATION regress_pub FOR TABLE tab1");
$node_publisher->safe_psql('postgres',
- "CREATE PUBLICATION regress_pub_combo FOR TABLE tab_gen_to_gen,
tab_gen_to_nogen, tab_gen_to_missing, tab_missing_to_gen");
+ "CREATE PUBLICATION regress_pub_combo FOR TABLE tab_gen_to_gen,
tab_gen_to_nogen, tab_missing_to_gen");
$node_publisher->safe_psql('postgres',
- "CREATE PUBLICATION regress_pub_nogen_to_gen FOR TABLE
tab_nogen_to_gen");
+ "CREATE PUBLICATION regress_pub_combo_gen_to_missing FOR TABLE
tab_gen_to_missing");
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION regress_pub_combo_nogen_to_gen FOR TABLE
tab_nogen_to_gen");
$node_publisher->safe_psql('postgres',
"CREATE PUBLICATION regress_pub_misc FOR TABLE tab_order");
+# create subscriptions
+#
+# Note that all subscriptions created on node_subscriber2 use copy_data =
false,
+# because copy_data = true with include_generated_columns is not yet supported.
+# For this reason, the expected inital data on snode_subscriber2 is always
empty.
+
$node_subscriber->safe_psql('postgres',
- "CREATE SUBSCRIPTION regress_sub1 CONNECTION '$publisher_connstr'
PUBLICATION regress_pub1"
+ "CREATE SUBSCRIPTION regress_sub1 CONNECTION '$publisher_connstr'
PUBLICATION regress_pub"
);
-
$node_subscriber->safe_psql('postgres',
- "CREATE SUBSCRIPTION regress_sub_combo CONNECTION '$publisher_connstr'
PUBLICATION regress_pub_combo"
+ "CREATE SUBSCRIPTION regress_sub1_combo CONNECTION '$publisher_connstr'
PUBLICATION regress_pub_combo"
);
-
-$node_subscriber2->safe_psql('postgres',
- "CREATE SUBSCRIPTION regress_sub_combo2 CONNECTION '$publisher_connstr'
PUBLICATION regress_pub_combo WITH (include_generated_columns = true, copy_data
= false)"
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION regress_sub1_combo_gen_to_missing CONNECTION
'$publisher_connstr' PUBLICATION regress_pub_combo_gen_to_missing"
);
+
$node_subscriber2->safe_psql('postgres',
- "CREATE SUBSCRIPTION regress_sub_nogen_to_gen CONNECTION
'$publisher_connstr' PUBLICATION regress_pub_nogen_to_gen WITH
(include_generated_columns = true, copy_data = false)"
+ "CREATE SUBSCRIPTION regress_sub2_combo CONNECTION '$publisher_connstr'
PUBLICATION regress_pub_combo WITH (include_generated_columns = true, copy_data
= false)"
);
-
$node_subscriber2->safe_psql('postgres',
- "CREATE SUBSCRIPTION regress_sub_misc CONNECTION '$publisher_connstr'
PUBLICATION regress_pub_misc WITH (include_generated_columns = true, copy_data
= false)"
+ "CREATE SUBSCRIPTION regress_sub2_misc CONNECTION '$publisher_connstr'
PUBLICATION regress_pub_misc WITH (include_generated_columns = true, copy_data
= false)"
);
#####################
# Wait for initial sync of all subscriptions
#####################
-# Here, copy_data = false because COPY and include_generated_columns are not
-# allowed at the same time for patch 0001.
-# And that is why all expected results on subscriber2 will be empty.
-# This limitation will be changed in patch 0002.
-
$node_subscriber->wait_for_subscription_sync;
$node_subscriber2->wait_for_subscription_sync;
@@ -170,9 +178,9 @@ is( $result, qq(1|22
# gen-to-gen
$result = $node_subscriber->safe_psql('postgres', "SELECT a, b FROM
tab_gen_to_gen");
-is( $result, qq(1|21
-2|22
-3|23), 'generated columns initial sync, when include_generated_columns=false');
+is( $result, qq(1|22
+2|44
+3|66), 'generated columns initial sync, when include_generated_columns=false');
$result = $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM
tab_gen_to_gen");
is( $result, qq(), 'generated columns initial sync, when
include_generated_columns=true');
@@ -184,18 +192,25 @@ is( $result, qq(1|
$result = $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM
tab_gen_to_nogen");
is( $result, qq(), 'generated columns initial sync, when
include_generated_columns=true');
-# nogen-to-gen
-$result = $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM
tab_nogen_to_gen");
-is( $result, qq(), 'generated columns initial sync, when
include_generated_columns=true');
+# gen-to-missing
+# Note, node_subscriber2 is not subscribing to this yet. See later.
+$result = $node_subscriber->safe_psql('postgres', "SELECT a FROM
tab_gen_to_missing");
+is( $result, qq(1
+2
+3), 'generated columns initial sync, when include_generated_columns=false');
-# missing-to_gen
+# missing-to-gen
$result = $node_subscriber->safe_psql('postgres', "SELECT a, b FROM
tab_missing_to_gen");
-is( $result, qq(1|2
-2|4
-3|6), 'generated columns initial sync, when include_generated_columns=false');
+is( $result, qq(1|22
+2|44
+3|66), 'generated columns initial sync, when include_generated_columns=false');
$result = $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM
tab_missing_to_gen");
is( $result, qq(), 'generated columns initial sync, when
include_generated_columns=true');
+# nogen-to-gen
+# Note, node_subscriber is not subscribing to this yet. See later
+# Note, node_subscriber2 is not subscribing to this yet. See later
+
$result = $node_subscriber2->safe_psql('postgres',
"SELECT a, b, c FROM tab_order ORDER BY a");
is( $result, qq(), 'generated column initial sync');
@@ -204,7 +219,6 @@ $result = $node_subscriber2->safe_psql('postgres',
"SELECT a, b, c FROM tab_alter ORDER BY a");
is( $result, qq(), 'unsubscribed table initial data');
-
# data to replicate
$node_publisher->safe_psql('postgres', "INSERT INTO tab1 VALUES (4), (5)");
@@ -230,30 +244,30 @@ is( $result, qq(1|22|
# insert data
$node_publisher->safe_psql('postgres', "INSERT INTO tab_gen_to_gen VALUES (4),
(5)");
-# regress_sub_combo: (include_generated_columns = false)
+# regress_sub1_combo: (include_generated_columns = false)
# Confirm that col 'b' is not replicated.
#$node_publisher->wait_for_catchup('regress_pub_combo');
-$node_publisher->wait_for_catchup('regress_sub_combo');
+$node_publisher->wait_for_catchup('regress_sub1_combo');
$result =
$node_subscriber->safe_psql('postgres', "SELECT a, b FROM tab_gen_to_gen
ORDER BY a");
-is( $result, qq(1|21
-2|22
-3|23
-4|24
-5|25),
- 'confirm generated columns are NOT replicated, when
include_generated_columns=false'
+is( $result, qq(1|22
+2|44
+3|66
+4|88
+5|110),
+ 'confirm generated columns are not replicated when
include_generated_columns=false'
);
-# regress_sub_combo2: (include_generated_columns = true)
+# regress_sub2_combo: (include_generated_columns = true)
# Confirm that col 'b' is not replicated. We can know this because the result
# value is the subscriber-side computation (which is different from the
# publisher-side computation for this column).
-$node_publisher->wait_for_catchup('regress_sub_combo2');
+$node_publisher->wait_for_catchup('regress_sub2_combo');
$result =
$node_subscriber2->safe_psql('postgres', "SELECT a, b FROM tab_gen_to_gen
ORDER BY a");
-is( $result, qq(4|24
-5|25),
- 'confirm generated columns are NOT replicated when the subscriber-side
column is also generated'
+is( $result, qq(4|88
+5|110),
+ 'confirm generated columns are not replicated when the subscriber-side
column is also generated'
);
#####################
@@ -266,9 +280,9 @@ is( $result, qq(4|24
# insert data
$node_publisher->safe_psql('postgres', "INSERT INTO tab_gen_to_nogen VALUES
(4), (5)");
-# regress_sub_combo: (include_generated_columns = false)
+# regress_sub1_combo: (include_generated_columns = false)
# Confirm that col 'b' is not replicated.
-$node_publisher->wait_for_catchup('regress_sub_combo');
+$node_publisher->wait_for_catchup('regress_sub1_combo');
$result =
$node_subscriber->safe_psql('postgres', "SELECT a, b FROM tab_gen_to_nogen
ORDER BY a");
is( $result, qq(1|
@@ -279,9 +293,9 @@ is( $result, qq(1|
'confirm generated columns are not replicated when the subscriber-side
column is not generated'
);
-# regress_sub_combo2: (include_generated_columns = true)
+# regress_sub2_combo: (include_generated_columns = true)
# Confirm that col 'b' is replicated.
-$node_publisher->wait_for_catchup('regress_sub_combo2');
+$node_publisher->wait_for_catchup('regress_sub2_combo');
$result =
$node_subscriber2->safe_psql('postgres', "SELECT a, b FROM tab_gen_to_nogen
ORDER BY a");
is( $result, qq(4|8
@@ -290,28 +304,48 @@ is( $result, qq(4|8
);
#####################
-# TEST tab_nogen_to_gen
+# TEST tab_gen_to_missing
#
# publisher-side has generated col 'b'.
-# subscriber-side has non-generated col 'b'.
+# subscriber-side col 'b' is missing.
#####################
# insert data
-$node_publisher->safe_psql('postgres', "INSERT INTO tab_nogen_to_gen VALUES
(4), (5)");
+$node_publisher->safe_psql('postgres', "INSERT INTO tab_gen_to_missing VALUES
(4), (5)");
-# regress_sub_nogen_to_gen: (include_generated_columns = false)
+# regress_sub1_combo_gen_to_missing: (include_generated_columns = false)
# Confirm that col 'b' is not replicated.
-$node_publisher->wait_for_catchup('regress_sub_nogen_to_gen');
+$node_publisher->wait_for_catchup('regress_sub1_combo_gen_to_missing');
$result =
- $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM tab_nogen_to_gen
ORDER BY a");
-is( $result, qq(4|88
-5|110),
- 'confirm generated columns are replicated when the subscriber-side
column is not generated'
+ $node_subscriber->safe_psql('postgres', "SELECT a FROM tab_gen_to_missing
ORDER BY a");
+is( $result, qq(1
+2
+3
+4
+5),
+ 'missing generated column, include_generated_columns = false'
+);
+
+# regress_sub2_combo_gen_to_missing: (include_generated_columns = true)
+# Confirm that col 'b' is not replicated and it will throw an error.
+my $offset2 = -s $node_subscriber2->logfile;
+
+# The subscription is created here, because it causes the tablesync worker to
restart repetitively.
+$node_subscriber2->safe_psql('postgres',
+ "CREATE SUBSCRIPTION regress_sub2_combo_gen_to_missing CONNECTION
'$publisher_connstr' PUBLICATION regress_pub_combo_gen_to_missing with
(include_generated_columns = true, copy_data = false)"
);
+# insert data
+$node_publisher->safe_psql('postgres', "INSERT INTO tab_gen_to_missing VALUES
(6)");
+
+$node_subscriber2->wait_for_log(
+ qr/ERROR: ( [A-Z0-9]+:)? logical replication target relation
"public.tab_gen_to_missing" is missing replicated column: "b"/,
+ $offset2);
+
#Cleanup
-$node_publisher->safe_psql('postgres',"DROP PUBLICATION
regress_pub_nogen_to_gen");
-$node_subscriber2->safe_psql('postgres',"DROP SUBSCRIPTION
regress_sub_nogen_to_gen");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION
regress_sub1_combo_gen_to_missing");
+$node_subscriber2->safe_psql('postgres', "DROP SUBSCRIPTION
regress_sub2_combo_gen_to_missing");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION
regress_pub_combo_gen_to_missing");
#####################
# TEST tab_missing_to_gen
@@ -323,33 +357,79 @@ $node_subscriber2->safe_psql('postgres',"DROP
SUBSCRIPTION regress_sub_nogen_to_
# insert data
$node_publisher->safe_psql('postgres', "INSERT INTO tab_missing_to_gen VALUES
(4), (5)");
-# regress_sub_combo: (include_generated_columns = false)
+# regress_sub1_combo: (include_generated_columns = false)
# Confirm that col 'b' is not replicated, but is generated as normal
-$node_publisher->wait_for_catchup('regress_sub_combo');
+$node_publisher->wait_for_catchup('regress_sub1_combo');
$result =
$node_subscriber->safe_psql('postgres', "SELECT a, b FROM tab_missing_to_gen
ORDER BY a");
-is( $result, qq(1|2
-2|4
-3|6
-4|8
-5|10),
+is( $result, qq(1|22
+2|44
+3|66
+4|88
+5|110),
'confirm when publisher col is missing, subscriber generated columns
are generated as normal'
);
-# regress_sub_combo2: (include_generated_columns = true)
+# regress_sub2_combo: (include_generated_columns = true)
# Confirm that col 'b' is not replicated, but is generated as normal
-$node_publisher->wait_for_catchup('regress_sub_combo2');
+$node_publisher->wait_for_catchup('regress_sub2_combo');
$result =
$node_subscriber2->safe_psql('postgres', "SELECT a, b FROM
tab_missing_to_gen ORDER BY a");
-is( $result, qq(4|8
-5|10),
+is( $result, qq(4|88
+5|110),
'confirm when publisher col is missing, subscriber generated columns
are generated as normal'
);
-#Cleanup
+# cleanup
+$node_subscriber->safe_psql('postgres',"DROP SUBSCRIPTION regress_sub1_combo");
+$node_subscriber2->safe_psql('postgres',"DROP SUBSCRIPTION
regress_sub2_combo");
$node_publisher->safe_psql('postgres',"DROP PUBLICATION regress_pub_combo");
-$node_subscriber->safe_psql('postgres',"DROP SUBSCRIPTION regress_sub_combo");
-$node_subscriber2->safe_psql('postgres',"DROP SUBSCRIPTION
regress_sub_combo2");
+
+#####################
+# TEST tab_nogen_to_gen
+#
+# publisher-side has non-generated col 'b'.
+# subscriber-side has generated col 'b'.
+#####################
+
+# insert data
+$node_publisher->safe_psql('postgres', "INSERT INTO tab_nogen_to_gen VALUES
(4), (5)");
+
+# regress_sub1_combo_nogen_to_gen: (include_generated_columns = false)
+# Confirm that col 'b' is not replicated and it will throw a COPY error.
+#
+# The subscription is created here, because it causes the tablesync worker to
restart repetitively.
+my $offset = -s $node_subscriber->logfile;
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION regress_sub1_combo_nogen_to_gen CONNECTION
'$publisher_connstr' PUBLICATION regress_pub_combo_nogen_to_gen WITH
(include_generated_columns = false)"
+);
+$node_subscriber->wait_for_log(
+ qr/ERROR: ( [A-Z0-9]:)? column "b" is a generated column/,
+ $offset);
+
+# regress_sub2_combo_nogen_to_gen: (include_generated_columns = true)
+#
+# XXX
+# when copy_data=false, no COPY error occurs.
+# the col 'b' is not replicated; the subscriber-side generated value is
inserted.
+$node_subscriber2->safe_psql('postgres',
+ "CREATE SUBSCRIPTION regress_sub2_combo_nogen_to_gen CONNECTION
'$publisher_connstr' PUBLICATION regress_pub_combo_nogen_to_gen WITH
(include_generated_columns = true, copy_data = false)"
+);
+
+# insert data
+$node_publisher->safe_psql('postgres', "INSERT INTO tab_nogen_to_gen VALUES
(6)");
+
+$node_publisher->wait_for_catchup('regress_sub2_combo_nogen_to_gen');
+$result =
+ $node_subscriber2->safe_psql('postgres', "SELECT a, b FROM tab_nogen_to_gen
ORDER BY a");
+is( $result, qq(6|132),
+ 'confirm when publisher col is not generated, subscriber generated
columns are generated as normal'
+);
+
+# cleanup
+
+$node_subscriber2->safe_psql('postgres',"DROP SUBSCRIPTION
regress_sub2_combo_nogen_to_gen");
+$node_publisher->safe_psql('postgres',"DROP PUBLICATION
regress_pub_combo_nogen_to_gen");
#####################
# TEST tab_order:
@@ -362,27 +442,27 @@ $node_subscriber2->safe_psql('postgres',"DROP
SUBSCRIPTION regress_sub_combo2");
# insert data
$node_publisher->safe_psql('postgres', "INSERT INTO tab_order VALUES (4),
(5)");
-# regress_sub_misc: (include_generated_columns = true)
-# Confirm depsite different orders replication occurs to the correct columns
-$node_publisher->wait_for_catchup('regress_sub_misc');
+# regress_sub2_misc: (include_generated_columns = true)
+# Confirm that depsite different orders replication occurs to the correct
columns
+$node_publisher->wait_for_catchup('regress_sub2_misc');
$result =
$node_subscriber2->safe_psql('postgres',
"SELECT a, b, c FROM tab_order ORDER BY a");
is( $result, qq(4|8|88
-5|10|110), 'replicate generated columns with different order on subscriber');
+5|10|110), 'replicate generated columns with different order on the
subscriber');
#####################
# TEST tab_alter
#
-# Add new table to existing publication, then
+# Add a new table to existing publication, then
# do ALTER SUBSCRIPTION ... REFRESH PUBLICATION
#####################
$node_publisher->safe_psql('postgres',
"ALTER PUBLICATION regress_pub_misc ADD TABLE tab_alter");
$node_subscriber2->safe_psql('postgres',
- "ALTER SUBSCRIPTION regress_sub_misc REFRESH PUBLICATION");
-$node_publisher->wait_for_catchup('regress_sub_misc');
+ "ALTER SUBSCRIPTION regress_sub2_misc REFRESH PUBLICATION");
+$node_publisher->wait_for_catchup('regress_sub2_misc');
$result = $node_subscriber2->safe_psql('postgres',
"SELECT a, b, c FROM tab_alter ORDER BY a");
is( $result, qq(1||22
@@ -404,7 +484,7 @@ $node_subscriber2->safe_psql('postgres',
$node_publisher->safe_psql('postgres',
"INSERT INTO tab_alter (a) VALUES (4), (5)");
-# confirmed replication now works for the subscriber nogen col
+# confirm that replication now works for the subscriber nogen col
$result = $node_subscriber2->safe_psql('postgres',
"SELECT a, b, c FROM tab_alter ORDER BY a");
is( $result, qq(1||22
@@ -414,8 +494,8 @@ is( $result, qq(1||22
5|10|10), 'after drop generated column expression');
#Cleanup
+$node_subscriber2->safe_psql('postgres',"DROP SUBSCRIPTION regress_sub2_misc");
$node_publisher->safe_psql('postgres',"DROP PUBLICATION regress_pub_misc");
-$node_subscriber2->safe_psql('postgres',"DROP SUBSCRIPTION regress_sub_misc");
#####################
# try it with a subscriber-side trigger
