Re: Minimal logical decoding on standbys

Hi,

On 4/7/23 8:24 PM, Drouvot, Bertrand wrote:
> Hi,
> 
> On 4/7/23 5:47 PM, Andres Freund wrote:
>> Hi,
>>
>>>> - write a test that invalidated logical slots do not lead to retaining WAL
>>>
>>> I'm not sure how to do that since pg_switch_wal() and friends can't be executed on
>>> a standby.
>>
>> You can do it on the primary and wait for the records to have been applied.
>>
> 
> Thanks, will give it a try in a couple of hours.

I looked at it but I think we'd also need things like pg_walfile_name() on the standby but is not allowed.

>> Is this patchset sufficient to subscribe to a publication on a physical
>> standby, assuming the publication is created on the primary? If so, we should
>> have at least a minimal test. If not, we should note that restriction
>> explicitly.
> 
> I gave it a try and it does work.
> 
> "
> node3 subscribes to node2 (standby).
> Insert done in node1 (primary) where the publication is created => node3 see the changes.
> "
> 
> I started to create the TAP test but currently stuck as the "create subscription" waits for a
checkpoint/pg_log_standby_snapshot()on the primary.
 
> 
> So, trying to make use of things like:
> 
> "my %psql_subscriber = ('stdin' => '', 'stdout' => '');
> $psql_subscriber{run} =
>    $node_subscriber->background_psql('postgres', \$psql_subscriber{stdin},
>      \$psql_subscriber{stdout},
>      $psql_timeout);
> $psql_subscriber{stdout} = '';
> "
> 
> But in vain so far...
> 

please find attached sub_in_progress.patch that "should work" but "does not" because
the wait_for_subscription_sync() call produces:

"
error running SQL: 'psql:<stdin>:1: ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.'
while running 'psql -XAtq -d port=61441 host=/tmp/45dt3wqs2p dbname='postgres' -f - -v ON_ERROR_STOP=1' with sql
'SELECTpg_current_wal_lsn()'
 
"

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com