Re: Event trigger information accessibility on plpgsql

On Mon, Jul 29, 2013 at 9:12 PM, Javier de la Torre
<jatorre(at)vizzuality(dot)com>wrote:

> Hi,
>
> I was looking at the new event triggers on 9.3 and was doing some testing.
> I have compiled PostgreSQL 9.3 Beta2
> It looks from the examples that the only info right now available on
> plpgsql when the triggers fire is tg_event and tg_tag.
>
> When developing on C you get access to more things. But would it be
> possible to do a plpgsql trigger where I have access to the table name and
> OID when I do an ALTER table to rename a column? Right now it feels i can
> only know that a table has been altered, but not which one
>

I recall that the event triggers functionality was quite large, and hence
was split across a series of patches. Only some of those patches were able
to be committed in time for 9.3, hence the lack of information accessible
from plpgsql functions. [The crux of the issue preventing the remaining
patches from landing was, I believe, disagreement over how to expose the
additional information in a consistent manner.]

Here is a crazy idea that might work: create an event trigger which is
fired on any CREATE or ALTER TABLE command (for any table), and in the
trigger function compare the current state of the catalog (pg_class and
pg_attribute) with a "snapshot" of the previous catalog state. If anything
has changed, perform the appropriate actions and update the "snapshot" with
the new state.

The downside is that this might be quite slow. But how often do you plan
to be creating and altering tables? The full scans of the catalog tables
will only happen when CREATE TABLE or ALTER TABLE commands are executed,
which might be acceptable. And when additional event trigger information
is presumably added in 9.4, you can simply treat it as a performance
optimization.

[I'm toying with the idea of an extension which (ab)uses event triggers in
precisely this manner. The goal is to provide built-in version control
that doesn't need any external tools to be run after changes have been made
to the schema.]

Regards,
Andrew Tipton