Re: Deparsing DDL command strings

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?

> 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.

> IF command is ALTER TABLE THEN

That's called TG_TAG, see

http://www.postgresql.org/docs/devel/static/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER

> FOR EACH subcommand
> IF subcommand IS DROP COLUMN THEN
> do something that needs to know what column is being dropped
> ELSE IF subcommand IS ADD COLUMN THEN
> do something that needs to know the definition of the column being added

We decided not to publish any notion of subcommand at this stage.

> I don't think every bit of that has to be dealt with by the event
> trigger code itself. For example, if you're adding a column to a table
> and the entries have already been made in the catalog, you could query
> to get the details of the column definition if you were given an OID
> into pg_attributes.

It's easy enough to provide the OID of the newly created main command
target object, it's harder to provide in a generic way all the OID of
the objects you might be interested into, because each command has its
own set of such.

DROP can target multiple objects, they all are the main target. ALTER
target only a single object, but can link to dependent objects. CREATE
an operator class or a cast and you're talking about a bunch of
operators and functions to tie together. It's not that easy.

> 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.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support