Wow Andrew, Great work! Lot of ideas to pick from.
Now, like you said it looks rather complicated and fragile. We will take a look at creating a custom C trigger to see if we can have something easier. Pity there was no agreement to just expose the object that fired the trigger. Best, On Jul 29, 2013, at 9:02 PM, Andrew Tipton wrote: > On Mon, Jul 29, 2013 at 10:08 PM, Javier de la Torre <jato...@vizzuality.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 > <audit_ddl.sql>
