Re: Event trigger information accessibility on plpgsql

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

> You think it will be possible to, instead of comparing schemas, looking
> for the last modified OID on the DB to figure out where it happened?
>

Not really. When a row has been updated, you can only see the new values.
By comparing the row's xmin value to txid_current() you could tell that it
was updated during this transaction, but there is no way to access the old
row. And after a row has been deleted, there is absolutely no way for the
current transaction to see it any longer.

I thought that I would try my hand at writing an event trigger that was
able to capture ALTER TABLE .. RENAME and ALTER TABLE ... RENAME COLUMN.
Turns out to be far harder than it looks. After much hacking around, I
managed to come up with a solution. The attached script audit_ddl.sql does
the trick.

$ psql
psql (9.4devel)
Type "help" for help.

postgres=# create table foo(column_one text, column_two integer);
NOTICE: on_start: taking catalog snapshot...
NOTICE: on_commit: checking for DDL actions.
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# alter table foo rename column column_one to col1;
NOTICE: on_start: taking catalog snapshot...
ALTER TABLE
postgres=# alter table foo rename column column_two to col2;
ALTER TABLE
postgres=# commit;
NOTICE: on_commit: checking for DDL actions.
NOTICE: on_commit: table foo column column_one renamed to col1.
NOTICE: on_commit: table foo column column_two renamed to col2.
COMMIT

Disclaimer: this is a nasty and grotesque series of hacks. You've been
warned...

a) using pg_advisory_xact_lock_shared() as a session-scoped variable that
gets automatically reset at the end of the transaction.
b) mucking around in the pg_locks view to determine if that advisory lock
is already held.
c) creating a temporary table whose sole purpose is to cause a constraint
trigger to be fired on transaction commit. (oh yes, if you execute SET
CONSTRAINTS ... during the transaction, you will surely break this.)

Oh, and you have to remember to
ALTER EVENT TRIGGER audit_ddl_event_trigger DISABLE;
before attempting to DROP anything in the audit_ddl schema, or recursive
hilarity will ensue.

Regards,
Andrew Tipton