Re: Deparsing DDL command strings

Jim Nasby <jim(at)nasby(dot)net> writes:
> We need to deal with questions like "If we rename a table, what do we have
> to do in londiste to accommodate that?" Except we're dealing with more than
> just londiste. We have internal code that does things like track "seed
> tables", which are tables that we need to dump data for when we dump schema.
> We have other systems that care about the schema that's defined in a
> database, and changes that happen to that schema.

Interesting use case, I hope we're offering you good progress.

>> - command tag is already commited
>> - object id, can be null
>> - schema name, can be null
>> - object name
>> - operation either ALTER, CREATE or DROP, …
>> - object type TABLE, VIEW, FUNCTION, …
>> - normalized command string
>>
>> The proposal I want to make here is to introduce a generic event (or an
>> event alias) named ddl_command_trace that the system provides at the
>> right spot where you have the information. That's useful when you don't
>> intend to divert the execution of the DDL and need to know all about it.
>
> In some cases we may need to divert or reject DDL, but that's a
> secondary concern.

Reject is already in, just RAISE ERROR from the trigger code. Divert is
another sell entirely, we currently miss that capability. I'm interested
into it for some DDLs. Which commands do you want to divert, and at
exactly what point in their execution? (think about privilege checks,
lock aquisition, etc).

>> You would have most of what you're asking. I think that looking into the
>> normalized string to get the information you need when you already know
>> you're looking at an ALTER TABLE statement and you already have the
>> object references (schema, name, oid) is going to make things easier.
>
> Possibly. We certainly have cases where we need to know what's
> happening *inside* the DDL.

In that cases you would probably need to resort to coding the trigger in
C so that you can abuse the parsetree. At least the fact that you're
doing funny things with some commands is easy to get at when doing \dy
from a psql prompt, an information that's completely lost when using the
standard process utility hook directly.

I don't see a way to pass down the parse tree in a format easy to use in
PLpgSQL anyway, but maybe we'll get there at some point. I want to say
that having to resort to C in some complex cases is good enough for a
first version of the feature.

Regards,
--
Dimitri Fontaine 06 63 07 10 78
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support