Re: Add an option to skip loading missing publication to avoid logical replication failure

[vignesh C]

On Tue, 25 Feb 2025 at 15:32, vignesh C <vignes...@gmail.com> wrote:
>
> The attached script has the script that was used for testing. Here the
> NUM_RECORDS count should be changed accordingly for each of the tests
> and while running the test with the patch change uncomment the drop
> publication command.

I have done further analysis on the test and changed the test to
compare it better with HEAD. The execution time is in milliseconds.
Brach/records  |  100     |  1000   |  10000    |  100000  |  1000000
Head               |   10.43  |  15.86  |   64.44    |  550.56  |   8991.04
Patch              |   11.35  |  17.26   |   73.50    |  640.21  |  10104.72
% diff              |   -8.82  |  -8.85    |   -14.08   |   -16.28  |  -12.38

There is a  performance degradation in the range of 8.8 to 16.2 percent.

Test Details (With Head):
a) Create two publications for the same table. b) Insert the records
listed in the table below. c) Use pg_recvlogical to capture the
changes.
Test Details (With Patch):
a) Create two publications for the same table.b) Drop one
publication(to check the impact of skip missing publication), ensuring
that changes from the remaining publication continue to be captured.
c) Insert the records listed in the table below.d) Use pg_recvlogical
to capture the changes.

The performance degradation is in the range of 8.8 to 16.2
percentage.The script used for the testing is attached, while running
with patch the drop publication command in script should be
uncommented and the record count should be changed for each of the
run. Also I changed  the patch so that  we need not execute the
get_rel_sync_entry code flow for every record in case of missing
publication case and to do so only in case when the publications have
changed. The attached v4 version patch has the changes for the same.

Regards,
Vignesh

v4-0001-Fix-logical-replication-breakage-after.patch
Description: Binary data

#!/bin/bash

#####

SLOT_NAME=test
PLUGIN_NAME=pgoutput
NUM_RECORDS=100000
LOOP=1

#####

for i in `seq 1 $LOOP`
do
# Cleanup previous result

pg_ctl stop -D data
rm -rf data logfile

# Initialize an instance

initdb -D data -U postgres -c wal_level=logical

# Start the instance
pg_ctl -D data -l logfile start

# Create a table
psql -U postgres -c "CREATE TABLE foo (id int);"
psql -U postgres -c "CREATE publication pub1 for table foo;"
psql -U postgres -c "CREATE publication pub2 for table foo;"
#psql -U postgres -c "drop publication pub1;"

# Create a replication slot
psql -U postgres -c "SELECT * FROM pg_create_logical_replication_slot('$SLOT_NAME', '$PLUGIN_NAME')"

    # Insert tuples (this transaction will be decoded)
    psql -U postgres -c "INSERT INTO foo VALUES (generate_series(1, $NUM_RECORDS))"

    # Confirm current WAL location
    WAL_POS=$(psql -qtAX -U postgres -c "SELECT * FROM pg_current_wal_lsn()")


	t1=$(($(date +%s%N)/1000))
	echo $t1 > run_${i}.dat
	
	# Run pg_recvlogical till the current WAL location
	time pg_recvlogical -d postgres -U postgres --start -S $SLOT_NAME -E $WAL_POS -f - -o publication_names='pub1,pub2' -o proto_version=4 ) &>> run_${i}.dat
	
	t2=$(($(date +%s%N)/1000))
	echo $t2 >> run_${i}.dat

	t3=$((t2-t1))
	echo "execution time=$t3" >> run_${i}.dat
done