Re: Deparsing DDL command strings

On 10/9/12 2:57 AM, Dimitri Fontaine wrote:
> Jim Nasby <jim(at)nasby(dot)net> writes:
>> I definitely want to be able to parse DDL commands to be able to
>> either enforce things or to drive other parts of the system based on
>> what's changing. Without the ability to capture (and parse) DDL
>> commands I'm stuck creating wrapper functions around anything I want
>> to capture and then trying to ensure that everyone uses the wrappers
>> and not the raw DDL commands.
>
> Are you mainly working on some Auditing system?

Definitely not.

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.

>> Event triggers that just spit out raw SQL give me the first part of
>> this, but not the second part: I'm still stuck trying to parse raw SQL
>> on my own. Having normalized SQL to parse should make that a bit
>> easier, but ideally I'd like to be able to pull specific elements out
>> of a command. I'd want to be able to do things like:
>
> The current design for event triggers is to spit out several things:
>
> - 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
>
> After some more thinking, it appears that in several case you want to
> have all those information filled in and you don't want to care if that
> means your trigger needs to run at ddl_command_start or ddl_command_end.
>
> 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.
>
> For a DROP operation, ddl_command_trace would be ddl_command_start, and
> for a CREATE operation, that would be ddl_command_end, so that the
> target object (still|already) exists when the trigger is fired.

In some cases we may need to divert or reject DDL, but that's a secondary concern.

>> Having said all that, an event system that spits back the raw SQL
>> would certainly be better than nothing. But realize that people would
>> still need to do parsing on it (ie: replication solutions will need to
>> know what table just got ALTER'd).
>
> 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.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net