On 2014-11-10 17:37:40 -0500, Christopher Browne wrote:
> On 8 November 2014 17:49, Robert Haas <robertmh...@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
> > > 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
Those are quite easy to recognize and skip.
> 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)
Sure. you might not want to do it automatically all the time - but I
think it's a very useful default mode. Once you can replicate CREATEs
per se, it's easy to add logic (in a couple lines of plpgsql or
whatever) to only do so in a certain schema or similar.
But the main reason all this is interesting isn't so much CREATE
itself. But that it can be (and Alvaro has mostly done it!) for ALTER as
well. And there it imo becomes really interesting. Because you can quite
easily check whether the affected relation is being replicated you can
just emit the DDL when that's the case. And that makes DDL in a
logically replicated setup *much* easier.
> 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.
Agreed. That's quite possible with the design here - you get the
creation commands and can decide whether you want to do anything with
them. You're not forced to insert them into your replication queue or
whatever you're using for that.
> 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:
>
> a) Has some kind of rule system to detect if it wants to replicate it,
Sure.
> b) Logs the change so a human might know later that there's new stuff
> that probably ought to be replicated
Sure.
> c) Perhaps a human might put replication into a new "suggestive"
> 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."
Sure.
Some of that already is possible with the current event triggers - and
all of it would be possible with the suggested functionality here.
An old version of bdr, employing the functionality presented here, had
the following (simplified) event trigger:
CREATE OR REPLACE FUNCTION bdr.queue_commands()
RETURNS event_trigger
LANGUAGE plpgsql
AS $function$
DECLARE
r RECORD;
BEGIN
-- don't recursively log ddl commands
IF pg_replication_identifier_is_replaying() THEN
RETURN;
END IF;
IF current_setting('bdr.skip_ddl_replication')::boolean THEN
RETURN;
END IF;
FOR r IN SELECT * FROM pg_event_trigger_get_creation_commands()
LOOP
/* ignore temporary objects */
IF r.schema = 'pg_temp' THEN
CONTINUE;
END IF;
/* ignore objects that are part of an extension */
IF r.in_extension THEN
CONTINUE;
END IF;
INSERT INTO bdr.bdr_queued_commands(
lsn, queued_at,
command_tag, command, executed
)
VALUES (
pg_current_xlog_location(),
NOW(),
r.command_tag,
pg_catalog.pg_event_trigger_expand_command(r.command),
'false'
);
IF r.command_tag = 'CREATE TABLE' and r.object_type = 'table' THEN
EXECUTE 'CREATE TRIGGER truncate_trigger AFTER TRUNCATE ON ' ||
r.identity ||
' FOR EACH STATEMENT EXECUTE PROCEDURE bdr.queue_truncate()';
END IF;
END LOOP;
END;
$function$;
It seems to me that'd pretty much allow all of your wishes above?
> 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
That's problematic imo if there's further changes to the table
definition - not exactly a infrequent thing.
> 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.
That's essentially where this patch is going. The submission is only
CREATE, but once the design is agreed upon, ALTER is coming. The patch
we're currently using for BDR has a good chunk of ALTER support.
> 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 actually already quite possible.
CREATE OR REPLACE FUNCTION prevent_ddl_outside_slonik()
RETURNS event_trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF current_setting('slony.inside_slonik')::boolean THEN
RETURN;
END IF;
RAISE ERROR 'hey there, use slonik!!!';
END $function$;
What's missing is that you probably want to look into the object to see
whether it's temporary, unlogged, et al.
> 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);
> 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.
I think it's actually quite coherent - and at least partially mirrors
the thoughts that have gone into this...
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
