On 10/9/12 2:57 AM, Dimitri Fontaine wrote:
Jim Nasby <j...@nasby.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 j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
