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

On Thu, Nov 13, 2014 at 7:45 AM, Andres Freund <andres@2ndquadrant.com> wrote:
>> Right.  And that's why it's cool that logical decoding can operate
>> through DDL differences.  The apply side might not be able to cope
>> with what pops out, but that's not logical decoding's fault, and
>> different apply-sides can adopt different policies as to how to deal
>> with whatever problems crop up.
>
> I think pretty much all of the solutions just say "oops, you're on your
> own". And I can't blame them for that. Once there's a schema difference
> and it causes problem there's really not much that can be done.

Agreed.

>> I don't know exactly what you mean by "a normal single master setup".
>> Surely the point of logical decoding is that the replica might not be
>> identical to the master.
>
> I actually think that that's not primary the point if you talk about
> individual objects. The majority of objects will be exactly the same on
> all nodes. If you actually want to have differening objects on the nodes
> you'll have to opt out/in (depending on your solution) of ddl
> replication for those objects.

I'm not saying it's the primary point; I'm just saying that it can happen.

>> And if it isn't, then a command that
>> succeeded on the master might fail on the standby - for example,
>> because an object by that name already exists there, or because a type
>> doesn't exist there.  (Even if you replicate a CREATE EXTENSION
>> command, there's no guarantee that the .so exists on the target.) Then
>> what?
>
> Sure. There's reasons logical replication isn't always a win. But I
> don't see why that's a reason not to make it as robust as possible.

I agree.

> Btw, the .so problem exists for wal shipping as as well.

Not in the same way.  You might not be able to access the data on the
standby if the .so defining the datatype isn't present, but the
replication itself doesn't care.

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

>> > The solution here doesn't force you to do that, does it? It's something
>> > that can be used by more than replication solution?
>>
>> In theory, yes.
>
> What's the practical point here?

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 think generally logical replication has more failure cases than
> physical ones. Which you seem to agree with.

Yes, I agree with that.

>> Physical replication is basically no-fail because it just
>> says, hey, go write these bytes into this page, and we can pretty much
>> always do that. But statement-based logical replication means
>> basically executing arbitrary chunks of code all over the backend, and
>> there is just no way to guarantee that code won't throw an error.  So
>> the best we can do is to hope that those errors will get reported back
>> to the user, which is going to require some kind of distributed
>> transaction.  Your idea to just run the replicated DDL statements on
>> the standby before committing on the master is one approach to that
>> problem, and probably the simplest one, but not the only one - one can
>> imagine something that resembles true clustering, for example.
>
> I think that's generally not what people need for primary/standby
> cases (of subsets of tables). In practice there aren't many failures
> like that besides schema differences. And there's just many usecases
> that aren't doable with physical replication, so we can't advise people
> doing that.

I can't really follow this, especially the first sentence.

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

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

> 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.  You're
talking about layering a lot of other complexity on top of the
deparsing stuff in this patch to produce a usable solution, and even
then I am not really convinced that it's going to be the final
solution to this problem.  Maybe it will, but I think there are enough
chinks in the armor to be really, really cautious about being certain
this is the right road to go down.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company