From e74f2a3ee51134cce35a84ae47fd5e3523f6b685 Mon Sep 17 00:00:00 2001 From: "houzj.fnst" Date: Thu, 9 Jun 2022 18:05:31 +0800 Subject: [PATCH] Test cases for DDL replication Author: Takamichi Osumi --- src/test/subscription/t/032_ddl_replication.pl | 236 +++++++++++++++++++++++++ 1 file changed, 236 insertions(+) create mode 100644 src/test/subscription/t/032_ddl_replication.pl diff --git a/src/test/subscription/t/032_ddl_replication.pl b/src/test/subscription/t/032_ddl_replication.pl new file mode 100644 index 0000000..5bf7109 --- /dev/null +++ b/src/test/subscription/t/032_ddl_replication.pl @@ -0,0 +1,236 @@ +# Copyright (c) 2022, PostgreSQL Global Development Group +# Regression tests for logical replication of DDLs +# +use strict; +use warnings; +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +my $node_publisher = PostgreSQL::Test::Cluster->new('publisher'); +$node_publisher->init(allows_streaming => 'logical'); +$node_publisher->append_conf('postgresql.conf', 'autovacuum = off'); +$node_publisher->start; + +my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber'); +$node_subscriber->init(allows_streaming => 'logical'); +$node_subscriber->append_conf('postgresql.conf', 'autovacuum = off'); +$node_subscriber->start; + +my $ddl = "CREATE TABLE test_rep(id int primary key, name varchar, value integer);"; +$node_publisher->safe_psql('postgres', $ddl); +$node_publisher->safe_psql('postgres', "INSERT INTO test_rep VALUES (1, 'data1', 1);"); +$node_subscriber->safe_psql('postgres', $ddl); + +my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres'; +$node_publisher->safe_psql('postgres', + "CREATE PUBLICATION mypub FOR ALL TABLES with (publish = 'insert, update, delete, ddl');"); +$node_subscriber->safe_psql('postgres', + "CREATE SUBSCRIPTION mysub CONNECTION '$publisher_connstr' PUBLICATION mypub;"); +$node_publisher->wait_for_catchup('mysub'); + +# Make sure we have fully synchronized the table. +# This prevents ALTER TABLE command below from being executed during table syncrhonization. +$node_subscriber->poll_query_until('postgres', + "SELECT COUNT(1) = 0 FROM pg_subscription_rel sr WHERE sr.srsubstate NOT IN ('s', 'r') AND sr.srrelid = 'test_rep'::regclass" +); + +# Test ALTER TABLE ADD +$node_publisher->safe_psql('postgres', "ALTER TABLE test_rep ADD c4 int;"); +$node_publisher->safe_psql('postgres', "INSERT INTO test_rep VALUES (2, 'data2', 2, 2);"); +$node_publisher->wait_for_catchup('mysub'); +my $result = $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM test_rep WHERE c4 = 2;"); +is($result, qq(1), 'ALTER test_rep ADD replicated'); + +# Test ALTER TABLE DROP +$node_publisher->safe_psql('postgres', "ALTER TABLE test_rep DROP c4;"); +$node_publisher->safe_psql('postgres', "DELETE FROM test_rep where id = 2;"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from test_rep;"); +is($result, qq(1), 'ALTER test_rep DROP replicated'); + +# Test ALTER TABLE ALTER COLUMN TYPE +$node_publisher->safe_psql('postgres', "ALTER TABLE test_rep ALTER COLUMN value TYPE varchar"); +$node_publisher->safe_psql('postgres', "INSERT INTO test_rep VALUES (3, 'data3', '3');"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM test_rep WHERE value = '3';"); +is($result, qq(1), 'ALTER test_rep ALTER COLUMN TYPE replicated'); + +# Test ALTER TABLE ALTER SET DEFAULT +# Check if we have the default value after the direct insert to subscriber node. +$node_publisher->safe_psql('postgres', "ALTER TABLE test_rep ALTER COLUMN value SET DEFAULT 'foo'"); +$node_publisher->wait_for_catchup('mysub'); +$node_subscriber->safe_psql('postgres', "INSERT INTO test_rep VALUES (4, 'data4', '4');"); +$result = $node_subscriber->safe_psql('postgres', "SELECT value FROM test_rep WHERE id = 4;"); +is($result, '4', 'ALTER test_rep ALTER SET DEFAULT replicated'); + +# Test ALTER TABLE ALTER DROP DEFAULT +# Check if we don't have the default value previously defined. +$node_publisher->safe_psql('postgres', "ALTER TABLE test_rep ALTER COLUMN value DROP DEFAULT;"); +$node_publisher->wait_for_catchup('mysub'); +$node_subscriber->safe_psql('postgres', "INSERT INTO test_rep VALUES (5, 'data5');"); +$result = $node_subscriber->safe_psql('postgres', "SELECT value IS NULL FROM test_rep WHERE id = 5;"); +is($result, q(t), 'ALTER test_rep ALTER DROP DEFAULT replicated'); + +# Test ALTER TABLE ALTER SET NOT NULL +# Remove the existing record that contains null value first. +my ($stdout, $stderr); +$node_subscriber->safe_psql('postgres', "DELETE FROM test_rep WHERE id = 5;"); +$node_publisher->safe_psql('postgres', "ALTER TABLE test_rep ALTER value SET NOT NULL;"); +$node_publisher->wait_for_catchup('mysub'); +$node_subscriber->psql('postgres', "INSERT INTO test_rep VALUES (6, 'data6');", + on_error_stop => 0, + stderr => \$stderr, + stdout => \$stdout); +$stderr =~ /ERROR: null value in column \"value\" of relation \"test_rep\" violates not-null constraint/ + or die "failed to replicate ALTER TABLE ALTER SET NOT NULL"; + +# Test ALTER TABLE ALTER DROP NOT NULL +$node_publisher->safe_psql('postgres', "ALTER TABLE test_rep ALTER value DROP NOT NULL;"); +$node_publisher->wait_for_catchup('mysub'); +# Insert same data that has NULL value. This failed before but now should succeed. +$node_subscriber->safe_psql('postgres', "INSERT INTO test_rep VALUES (6, 'data6');"); +$result = $node_subscriber->safe_psql('postgres', "SELECT value IS NULL FROM test_rep WHERE id = 6;"); +is($result, q(t), "ALTER test_rep ALTER DROP NOT NULL replicated"); + +# Test ALTER TABLE UNLOGGED +$node_publisher->safe_psql('postgres', 'ALTER TABLE test_rep SET UNLOGGED;'); +$node_publisher->wait_for_catchup('mysub'); +$node_publisher->safe_psql('postgres', "INSERT INTO test_rep VALUES (7, 'data7', '7');"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM test_rep WHERE id = 7;"); +is($result, qq(0), 'ALTER test_rep SET UNLOGGED replicated'); + +# Test ALTER TABLE LOGGED +$node_publisher->safe_psql('postgres', 'ALTER TABLE test_rep SET LOGGED;'); +$node_publisher->wait_for_catchup('mysub'); +$node_publisher->safe_psql('postgres', "INSERT INTO test_rep VALUES (8, 'data8', '8');"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM test_rep WHERE id = 8;"); +is($result, qq(1), 'ALTER test_rep SET LOGGED replicated'); + +# Test CREATE TABLE and DML changes +$node_publisher->safe_psql('postgres', "CREATE TABLE tmp (a int, b varchar);"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from pg_tables where tablename = 'tmp';"); +is($result, qq(1), 'CREATE tmp is replicated'); +$node_publisher->safe_psql('postgres', "INSERT INTO tmp values (1, 'a')"); +$node_publisher->safe_psql('postgres', "INSERT INTO tmp values (2, 'b')"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from tmp;"); +is($result, qq(2), 'DML Changes to tmp are replicated'); + +# Test CREATE TABLE INHERITS +$node_publisher->safe_psql('postgres', "CREATE TABLE tmp2 (c3 int) INHERITS (tmp);"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from pg_tables where tablename = 'tmp2';"); +is($result, qq(1), 'CREATE TABLE INHERITS is replicated'); +$node_publisher->safe_psql('postgres', "INSERT INTO tmp2 VALUES (1, 'a', 1);"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from tmp2;"); +is($result, qq(1), 'inserting some data to inherited table replicated'); +$node_publisher->safe_psql('postgres', "DROP TABLE tmp2"); + +# Test CREATE TABLE LIKE +$node_publisher->safe_psql('postgres', "CREATE TABLE tmp3 (c3 int, LIKE tmp);"); +$node_publisher->safe_psql('postgres', "INSERT INTO tmp3 VALUES (1, 1, 'a');"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from pg_tables where tablename = 'tmp3';"); +is($result, qq(1), 'CREATE TABLE LIKE replicated'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from tmp3;"); +is($result, qq(1), 'insert some data to a table defined with LIKE replicated'); +$node_publisher->safe_psql('postgres', "DROP TABLE tmp3"); + +# Test CREATE UNLOGGED TABLE +$node_publisher->safe_psql('postgres', "CREATE UNLOGGED TABLE tmp4 (id int);"); +$node_publisher->safe_psql('postgres', "INSERT INTO tmp4 VALUES (1);"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from pg_tables where tablename = 'tmp4';"); +is($result, qq(1), 'CREATE UNLOGGED TABLE is replicated correctly'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from tmp4;"); +is($result, qq(0), 'inserting data to unlogged table is not replicated correctly'); +$node_publisher->safe_psql('postgres', "DROP TABLE tmp4"); + +# Test CREATE TABLE IF NOT EXISTS +$node_publisher->safe_psql('postgres', "CREATE TABLE IF NOT EXISTS tmp5 (id int);"); +$node_publisher->safe_psql('postgres', "INSERT INTO tmp5 VALUES (1);"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from pg_tables where tablename = 'tmp5';"); +is($result, qq(1), 'CREATE TABLE IF NOT EXISTS replicated'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from tmp5;"); +is($result, qq(1), 'inserting data to a table replicated'); +$node_publisher->safe_psql('postgres', "DROP TABLE tmp5"); + +# Test CREATE TABLE IF NOT EXISTS (check if we skip to create a table +# when we have the table on the subscriber in advance, and if we succeed +# in replicating changes.) +$node_subscriber->safe_psql('postgres', "CREATE TABLE tmp6 (id int);"); +$node_publisher->safe_psql('postgres', "CREATE TABLE IF NOT EXISTS tmp6 (id int);"); +$node_publisher->safe_psql('postgres', "INSERT INTO tmp6 VALUES (1);"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from tmp6;"); +is($result, qq(1), 'CREATE TABLE IF NOT EXISTS replicated'); +$node_publisher->safe_psql('postgres', "DROP TABLE tmp6"); + +# Test CREATE TABLE IF NOT EXISTS (check if we skip to create a table +# when we have the table on the publisher, but not on the subscriber.) +$node_publisher->safe_psql('postgres', "CREATE TABLE tmp6 (id int);"); +$node_publisher->safe_psql('postgres', "CREATE TABLE IF NOT EXISTS tmp6 (id int);"); +$node_publisher->safe_psql('postgres', "INSERT INTO tmp6 VALUES (1);"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from tmp6;"); +is($result, qq(1), 'CREATE TABLE IF NOT EXISTS replicated'); +$node_publisher->safe_psql('postgres', "DROP TABLE tmp6"); + +# Test CREATE TABLE with constraint +$node_publisher->safe_psql('postgres', "CREATE TABLE tmp7 (id int UNIQUE);"); +$node_publisher->safe_psql('postgres', "INSERT INTO tmp7 VALUES (1);"); +$node_publisher->wait_for_catchup('mysub'); +$node_subscriber->psql('postgres', "INSERT INTO tmp7 VALUES (1);", + on_error_stop => 0, + stderr => \$stderr, + stdout => \$stdout); +$stderr =~ /ERROR: duplicate key value violates unique constraint "tmp7_id_key"/ + or die "failed to replicate constraint at creating table"; +$node_publisher->safe_psql('postgres', "DROP TABLE tmp7;"); + +# Test CREATE TABLE WITH strorage_parameter +$node_publisher->safe_psql('postgres', "CREATE TABLE tmp8 (id int) WITH (fillfactor = 80);"); +$node_publisher->wait_for_catchup('mysub'); +$node_subscriber->psql('postgres', "SELECT reloptions FROM pg_class WHERE relname = 'tmp8';", + on_error_stop => 0, + stderr => \$stderr, + stdout => \$stdout); +$stdout =~ /{fillfactor=80}/ + or die "failed to replicate storage option"; +$node_publisher->safe_psql('postgres', "DROP TABLE tmp8;"); + +# Test CREATE TABLE TABLESPACE (creating a tablespace itself is not replicated) +my $basedir = $node_publisher->basedir; +my $tablespace_dir = "$basedir/tblspc_pub"; +mkdir($tablespace_dir); +$node_publisher->safe_psql('postgres', "CREATE TABLESPACE mytblspc LOCATION '$tablespace_dir';"); +$basedir = $node_subscriber->basedir; +$tablespace_dir = "$basedir/tblspc_sub"; +mkdir ($tablespace_dir); + +$node_subscriber->safe_psql('postgres', "CREATE TABLESPACE mytblspc LOCATION '$tablespace_dir';"); +$node_publisher->safe_psql('postgres', "CREATE TABLE tmp12 (id int) TABLESPACE mytblspc;"); +$node_publisher->wait_for_catchup('mysub'); +$node_publisher->safe_psql('postgres', "INSERT INTO tmp12 VALUES (1);"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from tmp12;"); +is($result, qq(1), 'CREATE TABLE TABLESPACE replicated'); + +# Test DROP TABLE +$node_publisher->safe_psql('postgres', "DROP TABLE tmp;"); +$node_publisher->wait_for_catchup('mysub'); +$result = $node_subscriber->safe_psql('postgres', "SELECT count(*) from pg_tables where tablename = 'tmp';"); +is($result, qq(0), 'TABLE tmp is dropped'); + +pass "DDL replication tests passed:"; + +$node_subscriber->stop; +$node_publisher->stop; + +done_testing(); -- 2.7.2.windows.1