Re: Add CREATE support to event triggers - Mailing list pgsql-hackers
| From | Christopher Browne |
|---|---|
| Subject | Re: Add CREATE support to event triggers |
| Date | |
| Msg-id | CAFNqd5UMx2kyc7U7xEGMzdyn2JFgYEo=eSxag36BxACdqG19JQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Add CREATE support to event triggers (Robert Haas <robertmhaas@gmail.com>) |
| Responses |
Re: Add CREATE support to event triggers
(Petr Jelinek <petr@2ndquadrant.com>)
Re: Add CREATE support to event triggers (Andres Freund <andres@2ndquadrant.com>) |
| List | pgsql-hackers |
On 8 November 2014 17:49, Robert Haas <robertmhaas@gmail.com> wrote:
> > We could just integrate those parts, and be done with it. But would that
> > actually be a good thing for the community? Then slony needs to do it
> > and potentially others as well? Then auditing can't use it? Then
> > potential schema tracking solutions can't use it?
>
> Do you think Slony is really going to use this? I guess we can let
> the Slony guys speak for themselves, but I've been skeptical since day
> one that this is the best way to do DDL replication, and I still am.
> There are lots of ways that a replicated DDL statement can fail on the
> replicas, and what are you going to do then? It's too late to show
> the user the error message, so you can throw it in a log someplace and
> hope that somebody notices, but that's it. It makes a lot more sense
> to me to use some kind of a tool that applies the DDL in a coordinated
> fashion on all nodes - or even just do it manually, since it might
> very well be desirable to take the lock on different nodes at widely
> different times, separated by a switchover. I certainly think there's
> a use-case for what you're trying to do here, but I don't think it'll
> be right for everyone.
>
> Certainly, if the Slony guys - or some other team building an
> out-of-core replication solutions says, hey, we really want this in
> core, that would considerably strengthen the argument for putting it
> there. But I haven't heard anyone say that yet - unlike logical
> decoding, were we did have other people expressing clear interest in
> using it.
> > There've been people for a long while asking about triggers on catalogs> > We could just integrate those parts, and be done with it. But would that
> > actually be a good thing for the community? Then slony needs to do it
> > and potentially others as well? Then auditing can't use it? Then
> > potential schema tracking solutions can't use it?
>
> Do you think Slony is really going to use this? I guess we can let
> the Slony guys speak for themselves, but I've been skeptical since day
> one that this is the best way to do DDL replication, and I still am.
> There are lots of ways that a replicated DDL statement can fail on the
> replicas, and what are you going to do then? It's too late to show
> the user the error message, so you can throw it in a log someplace and
> hope that somebody notices, but that's it. It makes a lot more sense
> to me to use some kind of a tool that applies the DDL in a coordinated
> fashion on all nodes - or even just do it manually, since it might
> very well be desirable to take the lock on different nodes at widely
> different times, separated by a switchover. I certainly think there's
> a use-case for what you're trying to do here, but I don't think it'll
> be right for everyone.
>
> Certainly, if the Slony guys - or some other team building an
> out-of-core replication solutions says, hey, we really want this in
> core, that would considerably strengthen the argument for putting it
> there. But I haven't heard anyone say that yet - unlike logical
> decoding, were we did have other people expressing clear interest in
> using it.
> > for that purpose. IIRC Jan was one of them.
>
> My impression, based on something Christopher Brown said a few years
> ago, is that Slony's DDL trigger needs are largely satisfied by the
> existing event trigger stuff. It would be helpful to get confirmation
> as to whether that's the case.
I'm not sure that a replication system that intends to do partial replication
(e.g. - being selective of what objects are to be replicated) will necessarily
want to use the CREATE event triggers to capture creates.
Several cases pop up with different answers:
a) I certainly don't want to replicate temporary tables
b) I almost certainly don't want to replicate unlogged tables
c) For "more ordinary" tables, I'm not sure I want to extend Slony
a) I certainly don't want to replicate temporary tables
b) I almost certainly don't want to replicate unlogged tables
c) For "more ordinary" tables, I'm not sure I want to extend Slony
to detect them and add them automatically, because there
are annoying sub-cases
c.1) If I'm working on data conversion, I may create not totally
temporary tables that are nonetheless not worthy to replicate.
(I'm working on such right now)
Long and short: it seems likely that I'd frequently NOT want all new tables
added to replication, at least not all of them, all the time.
added to replication, at least not all of them, all the time.
What would seem valuable, to me, would be to have a CREATE event
trigger that lets me know the OID and/or fully qualified name of the new
object so that perhaps the replication system:
trigger that lets me know the OID and/or fully qualified name of the new
object so that perhaps the replication system:
a) Has some kind of rule system to detect if it wants to replicate it,
b) Logs the change so a human might know later that there's new stuff
that probably ought to be replicated
b) Logs the change so a human might know later that there's new stuff
that probably ought to be replicated
c) Perhaps a human might put replication into a new "suggestive"
mode, a bit akin to Slony's "EXECUTE SCRIPT", but where the human
mode, a bit akin to Slony's "EXECUTE SCRIPT", but where the human
essentially says, "Here, I'm running DDL against this connection for a
while, and I'd be grateful if Postgres told Slony to capture all the new
tables and sequences and replicated them."
while, and I'd be grateful if Postgres told Slony to capture all the new
tables and sequences and replicated them."
There are kind of two approaches:
a) Just capture the OIDs, and have replication go back later and grab
the table definition once the dust clears on the master
b) We need to capture ALL the DDL, whether CREATE or ALTER, and
b) We need to capture ALL the DDL, whether CREATE or ALTER, and
forward it, altered to have fully qualified names on everything so that
we don't need to duplicate all the "set search_path" requests and
such.
such.
I suppose there's also a third...
c) Have a capability to put an event trigger function in place that makes
DDL requests fail.
That's more useful than you'd think; if, by default, we make them fail,
c) Have a capability to put an event trigger function in place that makes
DDL requests fail.
That's more useful than you'd think; if, by default, we make them fail,
and with an error messages such as
"DDL request failed as it was not submitted using slonik DDL TOOL"
then we have protection against uncontrolled application of DDL.
DDL TOOL would switch off the "fail trigger", possibly trying to
capture the DDL, or perhaps just capturing the statements passed
to it so they get passed everywhere. (That heads back to a) and b);
capture the DDL, or perhaps just capturing the statements passed
to it so they get passed everywhere. (That heads back to a) and b);
what should get captured...)
I'm not sure that all of that is totally internally coherent, but I hope there
are some ideas worth thinking about.
are some ideas worth thinking about.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"
pgsql-hackers by date: