From 8cb78cb338a2106ea72c40d0f703bb087126b43a Mon Sep 17 00:00:00 2001 From: Peter Smith Date: Fri, 23 Aug 2024 20:39:57 +1000 Subject: [PATCH v30 2/2] Tap tests for generated columns Add tests for all combinations of generated column replication. Also test effect of 'include_generated_columns' option true/false. Author: Shubham Khanna, Peter Smith Reviewed-by: Vignesh C --- src/test/subscription/t/011_generated.pl | 454 +++++++++++++++++++++++ 1 file changed, 454 insertions(+) mode change 100644 => 100755 src/test/subscription/t/011_generated.pl diff --git a/src/test/subscription/t/011_generated.pl b/src/test/subscription/t/011_generated.pl old mode 100644 new mode 100755 index 8b2e5f4708..7b8c9d877c --- a/src/test/subscription/t/011_generated.pl +++ b/src/test/subscription/t/011_generated.pl @@ -96,4 +96,458 @@ is( $result, qq(1|22| 8|176|18 9|198|19), 'generated columns replicated with trigger'); +# cleanup +$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION sub1"); + +# ============================================================================= +# The following test cases exercise logical replication for all combinations +# where there is a generated column on one or both sides of pub/sub: +# - generated -> generated +# - generated -> normal +# - generated -> missing +# - missing -> generated +# - normal -> generated +# +# Furthermore, all combinations are tested for publish_generated_columns=false +# (see subscription sub1 of database 'postgres'), and +# publish_generated_columns=true (see subscription sub2 of database +# 'test_igc_true'). +# ============================================================================= + +$node_subscriber->safe_psql('postgres', "CREATE DATABASE test_igc_true"); + +# -------------------------------------------------- +# Testcase: generated -> generated +# Publisher table has generated column 'b'. +# Subscriber table has normal column 'b'. +# -------------------------------------------------- + +# Note, table 'tab_gen_to_gen' is essentially same as the 'tab1' table above. +# Since 'tab1' is already testing the publish_generated_columns=false case, +# here we need only test the publish_generated_columns=true case. + +# Create table and publication with publish_generated_columns=true. +$node_publisher->safe_psql( + 'postgres', qq( + CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 2) STORED); + INSERT INTO tab_gen_to_gen (a) VALUES (1), (2), (3); + CREATE PUBLICATION regress_pub_gen_to_gen FOR TABLE tab_gen_to_gen WITH (publish_generated_columns = true); +)); + +# Create table and subscription with copy_data=false. +# XXX copy_data=false for now. This will be changed later. +$node_subscriber->safe_psql( + 'test_igc_true', qq( + CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED); + CREATE SUBSCRIPTION regress_sub2_gen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub_gen_to_gen with (copy_data = false); +)); + +# Wait for initial sync. +$node_subscriber->wait_for_subscription_sync; + +# Initial sync test when publish_generated_columns=true. +# XXX copy_data=false for now, so there is no initial copy. This will be changed later. +$result = $node_subscriber->safe_psql('test_igc_true', + "SELECT a, b FROM tab_gen_to_gen"); +is($result, qq(), + 'tab_gen_to_gen initial sync, when publish_generated_columns=true'); + +# Insert data to verify incremental replication. +$node_publisher->safe_psql('postgres', + "INSERT INTO tab_gen_to_gen VALUES (4), (5)"); + +# Verify that an error occurs. This behaviour is same as tab_nogen_to_gen. +my $offset = -s $node_subscriber->logfile; +$node_subscriber->wait_for_log( + qr/ERROR: ( [A-Z0-9]:)? logical replication target relation "public.tab_gen_to_gen" is missing replicated column: "b"/, + $offset); + +# cleanup +$node_subscriber->safe_psql('test_igc_true', + "DROP SUBSCRIPTION regress_sub2_gen_to_gen"); +$node_publisher->safe_psql('postgres', + "DROP PUBLICATION regress_pub_gen_to_gen"); + +# -------------------------------------------------- +# Testcase: generated -> normal +# Publisher table has generated column 'b'. +# Subscriber table has normal column 'b'. +# -------------------------------------------------- + +# Create table. +$node_publisher->safe_psql( + 'postgres', qq( + CREATE TABLE tab_gen_to_nogen (a int, b int GENERATED ALWAYS AS (a * 2) STORED); + INSERT INTO tab_gen_to_nogen (a) VALUES (1), (2), (3); +)); + +# Create publication with publish_generated_columns=false. +$node_publisher->safe_psql('postgres', + "CREATE PUBLICATION regress_pub1_gen_to_nogen FOR TABLE tab_gen_to_nogen WITH (publish_generated_columns = false)" +); + +# Create table and subscription with copy_data=true. +$node_subscriber->safe_psql( + 'postgres', qq( + CREATE TABLE tab_gen_to_nogen (a int, b int); + CREATE SUBSCRIPTION regress_sub1_gen_to_nogen CONNECTION '$publisher_connstr' PUBLICATION regress_pub1_gen_to_nogen WITH (copy_data = true); +)); + +# Create publication with publish_generated_columns=true. +$node_publisher->safe_psql('postgres', + "CREATE PUBLICATION regress_pub2_gen_to_nogen FOR TABLE tab_gen_to_nogen WITH (publish_generated_columns = true)" +); + +# Create table and subscription with copy_data=false. +$node_subscriber->safe_psql( + 'test_igc_true', qq( + CREATE TABLE tab_gen_to_nogen (a int, b int); + CREATE SUBSCRIPTION regress_sub2_gen_to_nogen CONNECTION '$publisher_connstr' PUBLICATION regress_pub2_gen_to_nogen WITH (copy_data = false); +)); + +# Wait for initial sync. +$node_subscriber->wait_for_subscription_sync; + +# Initial sync test when publish_generated_columns=false. +# Verify that column 'b' is not replicated. +$result = $node_subscriber->safe_psql('postgres', + "SELECT a, b FROM tab_gen_to_nogen"); +is( $result, qq(1| +2| +3|), 'tab_gen_to_nogen initial sync, when publish_generated_columns=false'); + +# Initial sync test when publish_generated_columns=true. +# XXX copy_data=false for now. This will be changed later. +$result = $node_subscriber->safe_psql('test_igc_true', + "SELECT a, b FROM tab_gen_to_nogen"); +is($result, qq(), + 'tab_gen_to_nogen initial sync, when publish_generated_columns=true'); + +# Insert data to verify incremental replication +$node_publisher->safe_psql('postgres', + "INSERT INTO tab_gen_to_nogen VALUES (4), (5)"); + +# Incremental replication test when publish_generated_columns=false. +# Verify that column 'b' is not replicated. +$node_publisher->wait_for_catchup('regress_sub1_gen_to_nogen'); +$result = $node_subscriber->safe_psql('postgres', + "SELECT a, b FROM tab_gen_to_nogen ORDER BY a"); +is( $result, qq(1| +2| +3| +4| +5|), + 'tab_gen_to_nogen incremental replication, when publish_generated_columns=false' +); + +# Incremental replication test when publish_generated_columns=true. +# Verify that column 'b' is replicated. +$node_publisher->wait_for_catchup('regress_sub2_gen_to_nogen'); +$result = $node_subscriber->safe_psql('test_igc_true', + "SELECT a, b FROM tab_gen_to_nogen ORDER BY a"); +is( $result, qq(4|8 +5|10), + 'tab_gen_to_nogen incremental replication, when publish_generated_columns=true' +); + +# cleanup +$node_subscriber->safe_psql('postgres', + "DROP SUBSCRIPTION regress_sub1_gen_to_nogen"); +$node_subscriber->safe_psql('test_igc_true', + "DROP SUBSCRIPTION regress_sub2_gen_to_nogen"); +$node_publisher->safe_psql('postgres', + "DROP PUBLICATION regress_pub1_gen_to_nogen"); +$node_publisher->safe_psql('postgres', + "DROP PUBLICATION regress_pub2_gen_to_nogen"); + +# -------------------------------------------------- +# Testcase: generated -> missing +# Publisher table has generated column 'b'. +# Subscriber table does not have a column 'b'. +# -------------------------------------------------- + +# Create table. +$node_publisher->safe_psql( + 'postgres', qq( + CREATE TABLE tab_gen_to_missing (a int, b int GENERATED ALWAYS AS (a * 2) STORED); + INSERT INTO tab_gen_to_missing (a) VALUES (1), (2), (3); +)); + +# Create publication with publish_generated_columns=false. +$node_publisher->psql('postgres', + "CREATE PUBLICATION regress_pub1_gen_to_missing FOR TABLE tab_gen_to_missing WITH (publish_generated_columns = false)" +); + +# Create table and subscription with copy_data=true. +$node_subscriber->safe_psql( + 'postgres', qq( + CREATE TABLE tab_gen_to_missing (a int); + CREATE SUBSCRIPTION regress_sub1_gen_to_missing CONNECTION '$publisher_connstr' PUBLICATION regress_pub1_gen_to_missing WITH (copy_data = true); +)); + +# Create publication with publish_generated_columns=true. +$node_publisher->psql('postgres', + "CREATE PUBLICATION regress_pub2_gen_to_missing FOR TABLE tab_gen_to_missing WITH (publish_generated_columns = true)" +); + +# Create table and subscription with copy_data=false. +$node_subscriber->safe_psql( + 'test_igc_true', qq( + CREATE TABLE tab_gen_to_missing (a int); + CREATE SUBSCRIPTION regress_sub2_gen_to_missing CONNECTION '$publisher_connstr' PUBLICATION regress_pub2_gen_to_missing WITH (copy_data = false); +)); + +# Wait for initial sync. +$node_subscriber->wait_for_subscription_sync; + +# Initial sync test when publish_generated_columns=false. +# Verify generated columns are not replicated, so it is otherwise a normal data sync +$result = + $node_subscriber->safe_psql('postgres', "SELECT a FROM tab_gen_to_missing"); +is( $result, qq(1 +2 +3), 'tab_gen_to_missing initial sync, when publish_generated_columns=false'); + +# Initial sync test when include_generate_columns=true. +# XXX copy_data=false for now, so there is no initial copy. This will be changed later. +$result = $node_subscriber->safe_psql('test_igc_true', + "SELECT a FROM tab_gen_to_missing"); +is($result, qq(), + 'tab_gen_to_missing initial sync, when publish_generated_columns=true'); + +# Insert data to verify incremental replication. +$node_publisher->safe_psql('postgres', + "INSERT INTO tab_gen_to_missing VALUES (4), (5)"); + +# Incremental replication test when publish_generated_columns=false. +# Verify no error happens due to the missing column 'b'. +$node_publisher->wait_for_catchup('regress_sub1_gen_to_missing'); +$result = $node_subscriber->safe_psql('postgres', + "SELECT a FROM tab_gen_to_missing ORDER BY a"); +is( $result, qq(1 +2 +3 +4 +5), + 'tab_gen_to_missing incremental replication, when publish_generated_columns=false' +); + +# Incremental replication test when publish_generated_columns=true. +# Verify that an error is thrown due to the missing column 'b'. +$offset = -s $node_subscriber->logfile; +$node_subscriber->wait_for_log( + qr/ERROR: ( [A-Z0-9]+:)? logical replication target relation "public.tab_gen_to_missing" is missing replicated column: "b"/, + $offset); + +# cleanup +$node_subscriber->safe_psql('postgres', + "DROP SUBSCRIPTION regress_sub1_gen_to_missing"); +$node_subscriber->safe_psql('test_igc_true', + "DROP SUBSCRIPTION regress_sub2_gen_to_missing"); +$node_publisher->safe_psql('postgres', + "DROP PUBLICATION regress_pub1_gen_to_missing"); +$node_publisher->safe_psql('postgres', + "DROP PUBLICATION regress_pub2_gen_to_missing"); + +# -------------------------------------------------- +# Testcase: missing -> generated +# Publisher table has does not have a column 'b'. +# Subscriber table has generated column 'b'. +# -------------------------------------------------- + +# Create table. +$node_publisher->safe_psql( + 'postgres', qq( + CREATE TABLE tab_missing_to_gen (a int); + INSERT INTO tab_missing_to_gen (a) VALUES (1), (2), (3); +)); + +# Create publication with publish_generated_columns=false. +$node_publisher->safe_psql('postgres', + "CREATE PUBLICATION regress_pub1_missing_to_gen FOR TABLE tab_missing_to_gen WITH (publish_generated_columns = false)" +); + +# Create table and subscription with copy_data=true. +$node_subscriber->safe_psql( + 'postgres', qq( + CREATE TABLE tab_missing_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED); + CREATE SUBSCRIPTION regress_sub1_missing_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub1_missing_to_gen WITH (copy_data = true); +)); + +# Create publication with publish_generated_columns=true. +$node_publisher->safe_psql('postgres', + "CREATE PUBLICATION regress_pub2_missing_to_gen FOR TABLE tab_missing_to_gen WITH (publish_generated_columns = true)" +); + +# Create table and subscription with copy_data=false. +$node_subscriber->safe_psql( + 'test_igc_true', qq( + CREATE TABLE tab_missing_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED); + CREATE SUBSCRIPTION regress_sub2_missing_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub2_missing_to_gen WITH (copy_data = false); +)); + +# Wait for initial sync. +$node_subscriber->wait_for_subscription_sync; + +# Initial sync test when publish_generated_columns=false. +$result = $node_subscriber->safe_psql('postgres', + "SELECT a, b FROM tab_missing_to_gen"); +is( $result, qq(1|22 +2|44 +3|66), 'tab_missing_to_gen initial sync, when publish_generated_columns=false' +); + +# Initial sync test when include_generate_columns=true. +# XXX copy_data=false for now, so there is no initial copy. This will be changed later. +$result = $node_subscriber->safe_psql('test_igc_true', + "SELECT a FROM tab_gen_to_missing"); +is($result, qq(), + 'tab_missing_to_gen initial sync, when publish_generated_columns=true'); + +# Insert data to verify incremental replication. +$node_publisher->safe_psql('postgres', + "INSERT INTO tab_missing_to_gen VALUES (4), (5)"); + +# Incremental replication test when publish_generated_columns=false. +# Verify that column 'b' is not replicated. Subscriber generated value is used. +$node_publisher->wait_for_catchup('regress_sub1_missing_to_gen'); +$result = $node_subscriber->safe_psql('postgres', + "SELECT a, b FROM tab_missing_to_gen ORDER BY a"); +is( $result, qq(1|22 +2|44 +3|66 +4|88 +5|110), + 'tab_missing_to_gen incremental replication, when publish_generated_columns=false' +); + +# Incremental replication test when publish_generated_columns=true. +# Verify that column 'b' is not replicated. Subscriber generated value is used. +$node_publisher->wait_for_catchup('regress_sub2_missing_to_gen'); +$result = $node_subscriber->safe_psql('test_igc_true', + "SELECT a, b FROM tab_missing_to_gen ORDER BY a"); +is( $result, qq(4|88 +5|110), + 'tab_missing_to_gen incremental replication, when publish_generated_columns=true' +); + +# cleanup +$node_subscriber->safe_psql('postgres', + "DROP SUBSCRIPTION regress_sub1_missing_to_gen"); +$node_subscriber->safe_psql('test_igc_true', + "DROP SUBSCRIPTION regress_sub2_missing_to_gen"); +$node_publisher->safe_psql('postgres', + "DROP PUBLICATION regress_pub1_missing_to_gen"); +$node_publisher->safe_psql('postgres', + "DROP PUBLICATION regress_pub2_missing_to_gen"); + +# -------------------------------------------------- +# Testcase: normal -> generated +# Publisher table has normal column 'b'. +# Subscriber table has generated column 'b'. +# -------------------------------------------------- + +# Create table. +$node_publisher->safe_psql( + 'postgres', qq( + CREATE TABLE tab_nogen_to_gen (a int, b int); + INSERT INTO tab_nogen_to_gen (a, b) VALUES (1, 1), (2, 2), (3, 3); +)); + +# Create publication with publish_generated_columns=false. +$node_publisher->safe_psql('postgres', + "CREATE PUBLICATION regress_pub1_nogen_to_gen FOR TABLE tab_nogen_to_gen WITH (publish_generated_columns = false)" +); + +# Create table and subscription copy_data=true. +$node_subscriber->safe_psql( + 'postgres', qq( + CREATE TABLE tab_nogen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED); + CREATE SUBSCRIPTION regress_sub1_nogen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub1_nogen_to_gen WITH (copy_data = true); +)); + +# Verify that an error occurs, then drop the failing subscription. +$offset = -s $node_subscriber->logfile; +$node_subscriber->wait_for_log( + qr/ERROR: ( [A-Z0-9]:)? logical replication target relation "public.tab_nogen_to_gen" is missing replicated column: "b"/, + $offset); +$node_subscriber->safe_psql('postgres', + "DROP SUBSCRIPTION regress_sub1_nogen_to_gen"); + +# Create publication with publish_generated_columns=true. +$node_publisher->safe_psql('postgres', + "CREATE PUBLICATION regress_pub2_nogen_to_gen FOR TABLE tab_nogen_to_gen WITH (publish_generated_columns = true)" +); + +# Create table and subscription with copy_data=false.. +$node_subscriber->safe_psql( + 'test_igc_true', qq( + CREATE TABLE tab_nogen_to_gen (a int, b int GENERATED ALWAYS AS (a * 22) STORED); + CREATE SUBSCRIPTION regress_sub2_nogen_to_gen CONNECTION '$publisher_connstr' PUBLICATION regress_pub2_nogen_to_gen WITH (copy_data = false); +)); + +# Verify that an error occurs, then drop the failing subscription +$offset = -s $node_subscriber->logfile; +$node_publisher->safe_psql('postgres', + "INSERT INTO tab_nogen_to_gen VALUES (4), (5)"); +$node_subscriber->wait_for_log( + qr/ERROR: ( [A-Z0-9]:)? logical replication target relation "public.tab_nogen_to_gen" is missing replicated column: "b"/, + $offset); +$node_subscriber->safe_psql('test_igc_true', + "DROP SUBSCRIPTION regress_sub2_nogen_to_gen"); + +# cleanup +$node_publisher->safe_psql('postgres', + "DROP PUBLICATION regress_pub1_nogen_to_gen"); +$node_publisher->safe_psql('postgres', + "DROP PUBLICATION regress_pub2_nogen_to_gen"); + +# ============================================================================= +# Misc test. +# +# A "normal -> generated" replication fails, reporting an error that the +# subscriber side column is missing. +# +# In this test case we use DROP EXPRESSION to change the subscriber gerenated +# column into a normal column, then verify replication works ok. +# ============================================================================= + +# Create publication and table with normal column 'b' +$node_publisher->safe_psql( + 'postgres', qq( + CREATE TABLE tab_alter (a int, b int); + CREATE PUBLICATION regress_pub_alter FOR TABLE tab_alter; +)); + +# Create subscription and table with a generated column 'b' +$node_subscriber->safe_psql( + 'postgres', qq( + CREATE TABLE tab_alter (a int, b int GENERATED ALWAYS AS (a * 22) STORED); + CREATE SUBSCRIPTION regress_sub_alter CONNECTION '$publisher_connstr' PUBLICATION regress_pub_alter WITH (copy_data = false); +)); + +# Wait for initial sync. +$node_subscriber->wait_for_subscription_sync; + +# Change the generated column 'b' to be a normal column. +$node_subscriber->safe_psql('postgres', + "ALTER TABLE tab_alter ALTER COLUMN b DROP EXPRESSION"); + +# Insert data to verify replication. +$node_publisher->safe_psql('postgres', + "INSERT INTO tab_alter VALUES (1,1), (2,2), (3,3)"); + +# Verify that replication works, now that the subscriber column 'b' is normal +$node_publisher->wait_for_catchup('regress_sub_alter'); +$result = $node_subscriber->safe_psql('postgres', + "SELECT a, b FROM tab_alter ORDER BY a"); +is( $result, qq(1|1 +2|2 +3|3), 'after drop generated column expression'); + +# cleanup +$node_subscriber->safe_psql('postgres', + "DROP SUBSCRIPTION regress_sub_alter"); +$node_publisher->safe_psql('postgres', "DROP PUBLICATION regress_pub_alter"); + done_testing(); -- 2.34.1