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

On Wed, Mar 19, 2025 at 11:17 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Tue, 18 Mar 2025 at 17:34, Shubham Khanna
> <khannashubham1197(at)gmail(dot)com> wrote:
> >
> > I have added an additional test case to 040_pg_createsubscriber.pl as suggested.
> >
> > The attached patch contains the suggested changes.
>
> How about we change the below code:
> +# Verify that user databases (postgres, $db1, $db2) got subscriptions. Both
> +# $db1 and $db2 must be escaped to pass the safe_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_u->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)
> +{
> + my $result = $bgconn->query_safe("FETCH cursor;");
> +
> + is($result, $dbname, "subscription is created on $dbname");
> +}
>
> like:
> $result = $node_u->safe_psql($db1, 'SELECT datname FROM
> pg_subscription, pg_database WHERE subdbid = pg_database.oid and
> datistemplate = \'f\' ORDER BY pg_database.oid');
> is($result, "postgres
> $db1
> $db2", 'subscription is created on the required databases');
>
> I felt this might simplify your verification logic.
>

I agree with you on this; switching to a single query with safe_psql()
will indeed simplify the test and make the verification logic cleaner.

The attached patch contains the suggested change.

Thanks and regards,
Shubham Khanna.