Re: Enhance 'pg_createsubscriber' to retrieve databases automatically when no database is provided.

On Fri, Feb 28, 2025 at 6:33 PM Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:
>
> Dear Shubham,
>
> Thanks for updating the patch.
>
> I think the modification [1] is not correct - the loop is meaningless because the same
> query would be executed every time. How about idea like attached? Here, instead of
> try escaping dbname, dbname is directly obtained from the instance and they are compared.
>
> How do you think?
>
> [1]:
> ```
> +# Verify that only user databases got subscriptions (not template databases)
> +my @user_dbs = ('postgres', $db1, $db2);
> +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");
> +}
> ```
>

I agree with your suggestion and have incorporated the proposed
changes in the latest patch. Instead of escaping dbname, I now fetch
it directly from the instance for comparison, making the loop more
meaningful.

Additionally, as suggested by Ashutosh in [1], I have split the
040_pg_createsubscriber.pl file into three parts to improve clarity.

The attached patch includes all the suggested changes.

[1] - https://www.postgresql.org/message-id/CAExHW5uJHYAge99oS_iPfGWwZ_eCr2xFCNnifQkGs2GXeMQKGQ%40mail.gmail.com

Thanks and regards,
Shubham Khanna.