Re: Feature Request - DDL deployment with logical replication

On 31 March 2018 at 15:53, Nikhil Sontakke <nikhils(at)2ndquadrant(dot)com> wrote:

> 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.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services