Dear Vignesh,
Thanks for updating the patch. Two comments:
01.
```
+ ereport(ERROR,
+
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot execute %s while a sequence
synchronization worker for subscription \"%s\" is still running",
+ "ALTER SUBSCRIPTION ... REFRESH
SEQUENCES",
+ sub->name),
+ errhint("Try again after the current
synchronization completes."));
```
Maybe a translator note is needed, per other lines.
02.
```
@@ -2142,6 +2145,12 @@ AlterSubscription(ParseState *pstate,
AlterSubscriptionStmt *stmt,
* retain_dead_tuples.
*/
check_pub_rdt = sub->retaindeadtuples;
+
+ /*
+ * If the subscription already has sequences, ensure
the new
+ * publisher is new enough to synchronize them.
+ */
+ check_pub_seq = HasSubscriptionSequences(subid);
```
HasSubscriptionSequences() can spend long time because it searches
pg_subscription_rel
catalog linerly. Do you think we can do the deferrable approach? I imagined to
check the remote version first and then check the catalog if the version is
prior
than PG 19. Or it might be more costly to check the remote version?
Also, I find a case that REFRESH command can work even after the sequencesync
worker. I think it's harmless because of the LockSharedObject(), but let me
share
just in case.
1. run ALTER SUBSCRIPTION REFRESH SEQUENCES on the first session. all states for
the sequences can be initialized in pg_subscription_rel.
2. the apply worker finds the needs of sequencesync workers.
3. run ALTER SUBSCRIPTION REFRESH SEQUENCES on the second session. At that time
the sequencesync worker does not exist, so it does not raise an ERROR.
4. a sequencesync worker starts, but it waits for acquiring the shared object
with Access Exclusive.
5. the second session re-initialize the sequence states, then commit.
6. a sequencesync worker can resumes.
Can you come up with similar (and harmful) cases?
Best regards,
Hayato Kuroda
FUJITSU LIMITED
