Hi Jeremy, > My whole point is that in most architectures, DBAs decide to deploy the same > SQL on providers and subscribers. Yes it isn't perfect, but IMO, it is very > helpful to try to automate that idea, as opposed to trying to actually > replicate DDL at the low level. The latter is better, yes, but seems to > have proven extremely difficult. Hence, why you see the advent of functions > to pipe DDL through the replication stream. >
The community is currently working on in the current commitfest to try and get logical decoding of 2PC in into the core.
Once something like that gets in, for a majority of subset of DDLs (which works inside transaction blocks), one of the use cases of that functionality could be to trap these DDLs and convert them into implicit 2PC commands. Details need to be worked out, but we would get all the logical replication cluster nodes in sync with each other and issue a PREPARE transaction involving this DDL on all nodes in the logical replication cluster. If any of the nodes is not able to successfully prepare this DDL, then we can rollback or else commit the 2PC, thus moving the entire logical cluster consistently in terms of schema changes.
We'll still need a mechanism to transport them to downstreams (like WAL messages) and to send responses upstream. For responses I think we will finally want to add a backchannel to the logical replication protocol as I've wanted for a long while: downstream can send a COPY message on COPY BOTH proto back to upstream, which passes it to a callback on the output plugin for the output plugin to act on.
The main issue I had when I tried to prototype this before was IIRC not knowing how to set up the right snapshot in which to execute the callback.