Dear Cagri,
Thanks for updating the patch. I read and tested your patch. Here are my
comments.
01.
According to the REFRESH PUBLICATION command, we must prohibit to synchronize
tables if the two_phase is enabled, but REFRESH TABLE seems to bypass the
restriction.
Also, if there are prepared transactions done by the subscription and they
modify
target relations, the command would stuck forever - the apply worker won't start
again. So should we have the same guard as the REFRESH PUBLICATION?
02.
You have already checked the case the existence of tablesync workers, but
not for the leader worker. I feel we should ensure via logicalrep_workers_find()
like ALTER SUBSCRIPTION SET (two_phase). Also, we can remove the part from
AlterSubscription_refresh_table().
03.
Regarding the partition table, I found the case that TRUNCATE happened but
srsubstate cannot be updated. It's because ExecuteTruncate() truncates tables
all
child tables but AlterSubscription_refresh_table() updates tuples relid is
exactly matched. Per my experiment, this can cause the issue if the different
publications
publish the root and child separately, and they are subscribed by the different
subscription.
See attached reproducer.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
#!/bin/bash
PUB_DATA=data_pub
PUB_LOG=log.pub
PUB_PORT=5432
SUB_DATA=data_sub
SUB_LOG=log.sub
SUB_PORT=5433
pg_ctl stop -D $PUB_DATA
pg_ctl stop -D $SUB_DATA
rm -rf $PUB_DATA $PUB_LOG $SUB_DATA $SUB_LOG
initdb -D $PUB_DATA -U postgres -c wal_level=logical -c port=$PUB_PORT
pg_ctl start -D $PUB_DATA -l $PUB_LOG
(
echo "
CREATE TABLE foo (a int, b int, c int) PARTITION BY HASH (a);
CREATE TABLE foo_1 PARTITION OF foo FOR VALUES WITH (MODULUS 3, REMAINDER
0);
CREATE TABLE foo_2 PARTITION OF foo FOR VALUES WITH (MODULUS 3, REMAINDER
1);
CREATE TABLE foo_3 PARTITION OF foo FOR VALUES WITH (MODULUS 3, REMAINDER
2);
CREATE PUBLICATION pub1 FOR TABLE foo WHERE (a >= 15) WITH
(publish_via_partition_root = true);
CREATE PUBLICATION pub2 FOR TABLE foo_1 WHERE (a < 10);
"
) | psql -U postgres -p $PUB_PORT
initdb -D $SUB_DATA -U postgres -c port=$SUB_PORT
pg_ctl start -D $SUB_DATA -l $SUB_LOG
(
echo "
CREATE TABLE foo (a int PRIMARY KEY, b int, c int) PARTITION BY HASH (a);
CREATE TABLE foo_1 PARTITION OF foo FOR VALUES WITH (MODULUS 3, REMAINDER
0) ;
CREATE TABLE foo_2 PARTITION OF foo FOR VALUES WITH (MODULUS 3, REMAINDER
1);
CREATE TABLE foo_3 PARTITION OF foo FOR VALUES WITH (MODULUS 3, REMAINDER
2);
CREATE SUBSCRIPTION sub1 CONNECTION 'user=postgres port=$PUB_PORT'
PUBLICATION pub1 WITH (streaming=parallel, copy_data=off, two_phase = on);
CREATE SUBSCRIPTION sub2 CONNECTION 'user=postgres port=$PUB_PORT'
PUBLICATION pub2 WITH (streaming=parallel, copy_data=off, two_phase = on);
"
) | psql -U postgres -p $SUB_PORT
echo "#####################"
echo "#Insert initial data#"
echo "#####################"
psql -U postgres -p $PUB_PORT -c "INSERT INTO foo VALUES (generate_series(1,
20));"
sleep 1s
echo "#######################################################"
echo "#Confirm foo_1 status: status is 'r', and has 9 tuples#"
echo "#######################################################"
(
echo "
SELECT oid, relname, srsubstate FROM pg_class, pg_subscription_rel WHERE
oid = srrelid;
SELECT * FROM foo_1;
"
) | psql -U postgres -p $SUB_PORT
echo "###########################################"
echo "#run ALTER SUBSCRIPTION REFRESH TABLE sub1#"
echo "###########################################"
(
echo "
ALTER SUBSCRIPTION sub1 DISABLE;
ALTER SUBSCRIPTION sub1 REFRESH TABLE foo;
"
) | psql -U postgres -p $SUB_PORT
echo "############################"
echo "#Confirm foo_1 status again#"
echo "############################"
(
echo "
SELECT oid, relname, srsubstate FROM pg_class, pg_subscription_rel WHERE
oid = srrelid;
SELECT * FROM foo_1;
"
) | psql -U postgres -p $SUB_PORT
echo "####################################"
echo "#Enable the subscription sub1 again#"
echo "####################################"
(
echo "
ALTER SUBSCRIPTION sub1 ENABLE;
"
) | psql -U postgres -p $SUB_PORT
sleep 1s
echo "##########################################################"
echo "#... but some tuples are lost. foo_1 should have 9 tuples#"
echo "##########################################################"
(
echo "
SELECT oid, relname, srsubstate FROM pg_class, pg_subscription_rel WHERE
oid = srrelid;
SELECT * FROM foo_1;
"
) | psql -U postgres -p $SUB_PORT
