Re: Deparsing DDL command strings

On Fri, Oct 12, 2012 at 12:55 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> The user of that command string still has to know what to look for and
> maybe should include a proper SQL parser, but at least it doesn't need
> to do much guesswork about how the serial attached sequence will get
> named by the system and such oddities.

IMHO, it should be our explicit goal for clients not to need to parse
any SQL at all. I think that the communication between the server and
event triggers should be accomplished using magic variables. If the
data is too complex to be structured that way, then I think we should
use an established serialization format such as XML or (my personal
preference) JSON for which many parsers already exist. If we pass
sanitized SQL, then everyone who wants to do anything complicated will
need a parser for
sanitized-SQL-as-generated-by-the-PostgreSQL-event-trigger-mechanism,
and I think that's likely to be strictly worse than using one of the
serialization methods that already exists and is well understood.

Consider a hypothetical new feature where a table can be located in
either Paris or Pittsburgh. We add two new keywords to the grammer:
PARIS and PITTSBURGH. The syntax is extended to CREATE { PARIS |
PITTSBURGH } {schemaname}.{tablename} etc. Now, if we use
sanitized-SQL as a way of passing data to triggers, they all
immediately break, because the new key word is in exactly the location
where the table name used to be. If we use magic variables or JSON or
XML, we'll just add in another magic variable, or another field in the
JSON or XML object, and well-written triggers will ignore it and keep
working. Now, it may seem like I've chosen this example somewhat
unfairly since most syntax changes are a bit less obtrusive than that,
but we did do something not dissimilar to the above in 9.1, with
UNLOGGED.

There's a careful line to be walked here, because in order for event
triggers to be useful, we're going to have to pass them information -
and the way we do that becomes part of the API, and might get broken
by future changes. That sucks, but there's nothing we can do to
completely prevent it except not have event triggers at all, and I
want event triggers, and so do many other people. What we can and I
think must do is minimize the difficulty of writing and maintaining
event triggers, and that means making the API as clean as possible.
Like Tom, I'm very skeptical that this is the right horse to bet on.

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