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

On 2014-11-14 12:38:52 -0500, Robert Haas wrote:
> >> This is basically the same problem as multi-master replication
> >> conflicts, except with DDL.  Resolving replication conflicts is not a
> >> very easy thing to get right even if you're only concerned about the
> >> rows in the tables.  It's probably harder if you're worried about the
> >> schema, too.
> >
> > I don't think it's a sane thing to do multimaster with differing schemas
> > for individual relations, except maybe additional nonunique indexes.
> 
> But that's exactly what you ARE doing, isn't it?  I mean, if you
> replicate in only one direction, nothing keeps someone from modifying
> things on the replica independently of BDR, and if you replicate in
> both directions, well that's multi-master.

Meh. By that definition any logical replication solution does multi
master replication. Either you tell your users that that's not allowed,
or you just prevent it by technical means. Absolutely the same is true
for table contents.

FWIW, in BDR we *do* prevent schemas from being modified independently
on different nodes (unless you set the 'running with scissors' guc).

> I am quite doubtful about whether there will ever be a second, working
> implementation, so I see all of this code - and the maintenance effort
> associated with it - as something that will really only benefit BDR.
> I understand that you don't see it that way, and I'm not saying that
> you are offering anything in bad faith, but it looks to me like even
> with all of this very substantial amount of infrastructure, you're
> still going to need a big pile of additional code inside BDR to
> actually make it work, and I don't hear anyone else offering to
> develop something similar.

I don't know what to say about this. I don't see any other realistic way
to perform DDL replication in logical rep, and nearly all my
conversations with users have indicated that they want that.

I think it's a good idea to structure independent features in a way that
other solutions can reuse them. But I sure as hell can't force them to
use it - especially as there's unfortunately not too much development
going on in the existing logical replication solutions for postgres.

Btw, I really think there's quite legitimate use cases for this besides
logical replication solutions - e.g. schema tracking is quite a sensible
use case.

> >> By the way, the fact that you're planning to do log-based replication
> >> of DML and trigger-based replication of DDL scares the crap out of me.
> >> I'm not sure how that's going to work at all if the two are
> >> interleaved in the same transaction.
> >
> > Maybe that's based on a misunderstanding. All the event trigger does is
> > insert a event into a (local) queue. That's then shipped to the other
> > side using the same replication mechanisms as used for rows. Then, when
> > receiving changes in that ddl queue the standby performs those actions
> > before continuing with the replay.
> > That makes the interleaving on the standby to be pretty much the same as
> > on the primary.
> 
> OK.  But that's also not something that I can really imagine us ever
> adopting in core.

Well, that bit really depends on what the user (e.g. a replication
solution, or a schema tracking feature) needs. There's certain things
that you can quite easily do as part of core (e.g. insert something into
the WAL stream), that you just can't as external code.

I don't think there's any external replication solution that won't have
some form of internal queue to manipulate its internal state. For an
external solution such a queue currently pretty much has to be some
table, but for an eventual in core solution it could be done
differently.

> If we were going to do DDL replication in core, I have to believe we'd
> find a way to put all of the information in the WAL stream, not use
> triggers.

I agree that we might want to represent the transport to standbys
differently for something in core. That there's many different ways the
deparsing output could be used imo is a good reason why that part of the
mechanism isn't part of this submission.

I don't really understand the arguments against triggers in general
though. We're already using them quite extensively - I don't see why DDL
replication has to meet some completely different bar than say foreign
key checks or deferred uniqueness checks. We easily can add 'implicit'
event triggers, that aren't defined inside the catalog if we feel like
it. I'm just not sure we really would need/want to.

> > Yes, it's not trivial. And I think there's some commands where you might
> > not want to try but either scream ERROR or just rereplicate the whole
> > relation or such.
> >
> > I very strongly feel that we (as postgres devs) have a *much* higher
> > chance of recognizing these cases than either some random users (that
> > write slonik scripts or similar) or some replication solution authors
> > that aren't closely involved with -hackers.
> 
> It's clearly the job of the replication solution authors to figure
> these details out.  I'm not going to get into the business of passing
> judgement on the relative competence of different groups of
> replication developers.

It's not a question of relative competence. It's a question of the focus
of that competence. There's just not many reasons a replication solution
developer needs to have detailed knowledge about postgres' internal
parsetrees for DDL statements. It's not like extensive documentation
about their meaning exists.

> There's no question that 2ndQuadrant is doing
> great, exciting work, but we should not tilt the playing field in a
> way that crowds other people out, even new players that haven't yet
> written their first line of code.

Huh? I'm not seing how I'm doing that in any way.

> > Right, it's far from easy. But imo that's an argument for providing the
> > tools to do it as robust as we can in core.
> 
> I agree with that on general principle, but I do not have confidence
> in this patch set as the best foundation for DDL replication.

Can you please hint at some other workable design? I don't think there
really is anything else.

How should DDL be replicated in a logical replication solution but via
DDL? It's not like just replicating the catalog rows or anything like
that is something even remotely sane.  As stated before, everything the
user has to do manually is *much* more error prone than what's presented
here.

Greetings,

Andres Freund

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