Re: Event Triggers: adding information

On Sun, Jan 27, 2013 at 12:08 PM, Steve Singer <ssinger(at)ca(dot)afilias(dot)info> wrote:
> On 13-01-26 11:11 PM, Robert Haas wrote:
>> On Fri, Jan 25, 2013 at 11:58 AM, Dimitri Fontaine
>> <dimitri(at)2ndquadrant(dot)fr> wrote:
>>>
>>> My understanding is that if the command string we give to event triggers
>>> is ambiguous (sub-object names, schema qualifications, etc), it comes
>>> useless for logical replication use. I'll leave it to the consumers of
>>> that to speak up now.
>>
>> Yeah, that's probably true. I think it might be useful for other
>> purposes, but I think we need a bunch of infrastructure we don't have
>> yet to make logical replication of DDL a reality.
>
> I agree. Does anyone have a specific use case other than DDL replication
> where an ambiguous command string would be useful? Even for use cases like
> automatically removing a table from replication when it is dropped, I would
> want to be able to determine which table is being dropped unambiguously.
> Could I determine that from an oid? I suspect so, but parsing a command
> string and then trying to figure out the table from the search_path doesn't
> sound very appealing.

I was thinking about logging or auditing applications - e.g. log all
command strings whose first word is "drop". We do get customers
coming to us with that sort of request from time to time.

>> Well, the point is that if you have a function that maps a parse tree
>> onto an object name, any API or ABI changes can be reflected in an
>> updated definition for that function. So suppose I have the command
>> "CREATE TABLE public.foo (a int)". And we have a call
>> pg_target_object_namespace(), which will return "public" given the
>> parse tree for the foregoing command. And we have a call
>> pg_target_object_name(), which will return "foo". We can whack around
>> the underlying parse tree representation all we want and still not
>> break anything - because any imaginable parse tree representation will
>> allow the object name and object namespace to be extracted. Were that
>> not possible it could scarcely be called a parse tree any longer.
>
> How do you get the fully qualified type of the first column?
> col1=pg_target_get_column(x, 0)
> pg_target_get_type(col1);
>
> or something similar.

Or maybe return all the data for all the columns as an array of records...

> I think that could work but we would be adding a lot of API functions to get
> all the various bits of info one would want the API to expose.

Yeah, that's the principle disadvantage of this method, AFAICS. OTOH,
I am not sure anything else we might do is any better. You can avoid
the complexity of defining and specifying all that interface by just
taking the parse tree and running nodeToString() on it and lobbing it
over the fence, but that is basically abdicating the responsibility of
defining a sane interface in favor of just chucking whatever you
happen to have handy over the wall and hoping the user is OK with
calling that an interface. Assuming you think that's a bad idea (and
Tom and I do, at least), you've got to put in the effort to design
something, and that thing, however constituted, is going to be fairly
extensive.

> I also
> suspect executing triggers that had to make lots of function calls to walk a
> tree would be much slower than an extension that could just walk the
> parse-tree or some other abstract tree like structure.

I am kind of doubtful that this is a real problem - why should it be
any slower than, say, parsing a JSON object into an AST?

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