On Wed, May 28, 2025 at 12:15 AM Zhijie Hou (Fujitsu) <houzj.f...@fujitsu.com> wrote: > I think the SQL API was mainly intended for testing and debugging purposes > where controlled sync operations are useful. For production use, the slotsync > worker (with sync_replication_slots=on) is recommended because it > automatically > handles this problem and requires minimal manual intervention. But to avoid > confusion, I think we should clearly document this distinction.
If this analysis is correct, this should never have been committed, at least not in this form. When we ship something, it needs to work. Testing and debugging facilities are best placed in src/test/modules or in contrib; if for some reason they really need to be in src/backend, then they had better be clearly documented as such. What really annoys me about this is that the function gives every superficial impression of being something you could actually use. Why wouldn't a user believe that if they periodically connect and run pg_sync_replication_slots(), things will be OK? I can certainly imagine a user *wanting* that to work. I'd like that to work. But it seems like either it's impossible for some reason that isn't clear to me, and we just went ahead and shipped it in a non-working state anyway, or it is possible to make it work and we didn't do the necessary engineering before something got committed. Either way, that's really disappointing. > I think the issue occurs because unlike the slotsync worker, the SQL API > removes temporary slots when the function ends, so it cannot hold back the > standby's catalog_xmin. If transactions on the primary keep advancing xids, > the > source slot's catalog_xmin on the primary fails to catch up with the standby's > nextXid, causing sync failure. I still don't understand how this problem arises in the first place. It seems like you're describing a situation where we need to prevent the standby from getting ahead of the primary, but that should be impossible by definition. -- Robert Haas EDB: http://www.enterprisedb.com
