diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl index 7574a3903e..79ef8b1764 100644 --- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl +++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl @@ -496,19 +496,34 @@ command_ok( $node_s2->start; -# Verify that only user databases got subscriptions (not template databases) +# Verify that user databases (postgres, $db1, $db2) got subscriptions. Both +# $db1 and $db2 must be escaped to pass the sqfe_psql(), but it is difficult. +# Thus, we define a cursor, obtain a dbname from the instance and compere one +# by one. my @user_dbs = ('postgres', $db1, $db2); + +my $bgconn = $node_s2->background_psql('postgres'); +$bgconn->query_safe( + qq[ + BEGIN; + DECLARE cursor CURSOR FOR SELECT datname FROM pg_subscription, pg_database WHERE subdbid = pg_database.oid and datistemplate = 'f' ORDER BY pg_database.oid; +]); + +# Fetch from the cursor three times and confirm the existence of the +# subscription on $dbname foreach my $dbname (@user_dbs) { - $result = $node_s2->safe_psql('postgres', - "SELECT count(*) FROM pg_subscription, pg_database WHERE subdbid = pg_database.oid and datistemplate = 'f';" - ); - is($result, '3', "Subscription created successfully for $dbname"); - $result = $node_s2->safe_psql('postgres', - "SELECT count(*) FROM pg_subscription, pg_database WHERE subdbid = pg_database.oid and datistemplate = 't';" - ); - is($result, '0', "Subscription created successfully for $dbname"); + $result = $bgconn->query_safe("FETCH cursor;"); + + is($result, $dbname, "subscription is created on $dbname"); } +$bgconn->quit; + +# Verify template databases do not have subscriptions +$result = $node_s2->safe_psql('postgres', + "SELECT count(*) FROM pg_subscription, pg_database WHERE subdbid = pg_database.oid and datistemplate = 't';" +); +is($result, '0', 'subscription is not created on template databases'); # Run pg_createsubscriber on node S. --verbose is used twice # to show more information.