Re: Add CREATE support to event triggers - Mailing list pgsql-hackers

On 2014-11-12 16:36:30 -0500, Robert Haas wrote:
> On Mon, Nov 10, 2014 at 9:02 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> > +1. Adding columns is a PITA, you have to manually ensure you do it on all
> > slaves first.
> >
> > Drop is somewhat worse, because you have to do it on the master first,
> > opposite of the (more usual) case of adding a column.
> >
> > RENAME is a complete disaster.
> >
> > Handing scripts to your replication system to execute isn't a very good
> > alternative either; it assumes that you actually have a script (bad
> > assumption with ORMs), and that you have a reasonable way to get that script
> > to wherever you run your replication system.
> 
> I don't disagree with any of that, but running the command on the
> master and then propagating it to the slaves where it may succeed or
> fail - and if it fails, you won't know unless you're watching the logs
> on those machines, and, oh by the way, replication will also be broken
> - is not good either.

That's already the situation today with all the logical replication
solutions. They *constantly* break in the field. Most commonly because
of DDL differences.

I don't understand why you think it's likely for logical replication to
break due to this? You mean because deparse yielded a invalid statement?
In a normal single master setup there really shouldn't be scenarios
where that happens? Except bugs - but as you know we had more than in
HS/SR as well?

Or are you worried about stuff like ALTER TABLE ... USING()? I think
that's the replication solution's job to take care of/prevent.

For multimaster the situation is more complex, I agree, but I don't
think in core stuff needs to solve that for now?

We are thinking about extending 2PC to be usable across logical
decoding. That's a relatively simple patch. Then it's possible to do the
DDL on the primary, ship it to the standby, apply it there, and only
afterwards commit the prepared xact if that was successfull.  That's
quite cool - but somewhat in the remit of the replication solution.

> I think the approach to DDL
> replication that Alvaro, Andres, et al. are proposing here is
> absolutely fine - even praiseworthy - as an out-of-core solution that
> users can adopt if they are willing to accept the associated risks, as
> many users probably will be.  But you wouldn't convince me to run it
> on any production system for which I was responsible.

The solution here doesn't force you to do that, does it? It's something
that can be used by more than replication solution?

I just don't see the alternative you're proposing? I've so far not even
seen a credible *sketch* of an alternative design that also can handle
ALTER.  The only current alternatives are 1) the user inserts some
events into the queue manually. If they depend on any local state you're
screwed. If they have syntax errors they're often screwed. 2). The user
does all actions on the standby first. Then on the primary. That's hard
for ALTER ADD COLUMN and similar, and just about impossible for renaming
things.

Greetings,

Andres Freund

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