Re: Event Triggers: adding information

On 2013-01-18 09:58:53 -0500, Robert Haas wrote:
> On Fri, Jan 18, 2013 at 9:07 AM, Andres Freund <andres@2ndquadrant.com> wrote:
> > I don't have a problem reusing the object access infrastructure at all. I just
> > don't think its providing even remotely enough. You have (co-)written that
> > stuff, so you probably know more than I do, but could you explain to me how it
> > could be reused to replicate a CREATE TABLE?
> >
> > Problems I see:
> > - afaics for CREATE TABLE the only hook is in ATExecAddColumn
>
> No, there's one also in heap_create_with_catalog.  Took me a minute to
> find it, as it does not use InvokeObjectAccessHook.  The idea is that
> OAT_POST_CREATE fires once per object creation, regardless of the
> object type - table, column, whatever.

Ah. Chose the wrong term to grep for. I am tempted to suggest adding a
comment explaining why we InvokeObjectAccessHook isn't used just for
enhanced grepability.

> > - No way to regenerate the table definition for execution on the remote system
> >   without creating libpqdump.
>
> IMHO, that is one of the really ugly problems that we haven't come up
> with a good solution for yet.  If you want to replicate DDL, you have
> basically three choices:
>
> 1. copy over the statement text that was used on the origin server and
> hope none of the corner cases bite you
> 2. come up with some way of reconstituting a DDL statement based on
> (a) the parse tree or (b) what the server actually decided to do
> 3. reconstitute the state of the object from the catalogs after the
> command has run
>
> (2a) differs from (2b) for things like CREATE INDEX, where the index
> name might be left for the server to determine, but when replicating
> you'd like to get the same name out.  (3) is workable for CREATE but
> not ALTER or DROP.
>
> The basic problem here is that (1) and (3) are not very
> reliable/complete and (2) is a lot of work and introduces a huge code
> maintenance burden.

I agree with that analysis.

> But it's unfair to pin that on the object-access
> hook mechanism

I agree. I don't think anybody has pinned it on it though.

> - any reverse-parsing or catalog-deconstruction
> solution for DDL is going to have that problem.  The decision we have
> to make as a community is whether we're prepared to support and
> maintain that code for the indefinite future.  Although I think it's
> easy to say "yes, because DDL replication would be really cool" - and
> I sure agree with that - I think it needs to be thought through a bit
> more deeply than that.
>
> I have been involved in PostgreSQL development for about 4.5 years
> now.  This is less time than many people here, but it's still long
> enough to say a whole lot of people ask for some variant of this idea,
> and yet I have yet to see anybody produce a complete, working version
> of this functionality and maintain it outside of the PostgreSQL tree
> for one release cycle (did I miss something?).

I don't really think thats a fair argument.

For one, I didn't really ask for a libpgdump - I said that I don't see
any way to generate re-executable SQL without it if we don't get the
parse-tree but only the oid of the created object. Not to speak of the
complexities of supporting ALTER that way (which is, as you note,
basically not the way to do this).

Also, even though I don't think its the right way *for this*, I think
pg_dump and pgadmin pretty much prove that it's possible?  The latter
even is out-of-core and has existed for multiple years.

Its also not really fair to compare out-of-tree effort of maintaining
such a library to in-core support. pg_dump *needs* to be maintained, so
the additional maintenance overhead once the initial development is done
shouldn't really be higher than now. Lower if anything, due to getting
rid of a good bit of ugly code ;). There's no such effect out of core.

If youre also considering parsetree->SQL transformation under the
libpgdump umbrella its even less fair. The backend already has a *lot*
of infrastructure to regenerate sql from trees, even if its mostly
centered arround around DQL. A noticeable amount of that code is
unavailable to extensions (i.e. many are static funcs).
Imo its completely unreasonable to expose that code to the outside and
expect it to have a stable interface. We *will* need to rewrite parts of
that regularly.
For those reasons I think the only reasonable way to create textual DDL
is in the backend trying to allow outside code to do that will impose
far greater limitations.

Whats the way you suggest to go on about this?

> There's a totally legitimate debate to be had here, but my feeling
> about what you're calling libpqdump is:

> - It's completely separate from event triggers.
> - It's completely separate from object access hooks.

Well, it is one of the major use-cases (or even *the* major use case)
for event triggers that I heard of. So it seems a valid point in a
dicussion on how to design the whole mechanism, doesn't it?

Some version of the event trigger patch contained partial support for
regenerating the DDL so it seems like a justified point there. Your
complained that suggestions about reusing object access hooks were
ignored, so mentioning that they currently don't provide *enough* (they
*do* provide a part, but it doesn't seem to be the most critical one)
also seems justifyable.

> [ BTW: Sorry our IM session got cut off.  It started erroring out
> every time I messaged you.  But no problem, anyway. ]

NP, its good to keep the technical stuff here anyway... Stupid
technology. In which business are we in again?

Greetings,

Andres Freund

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