Обсуждение: Event trigger information accessibility on plpgsql
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
Thanks in advance.
Javier de la Torre
Map, analyze and build applications with your data
On Mon, Jul 29, 2013 at 9:12 PM, Javier de la Torre <jatorre@vizzuality.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 Beta2It 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
Andrew Tipton
Thanks Andrew,
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?
On Jul 29, 2013, at 3:42 PM, Andrew Tipton wrote:
On Mon, Jul 29, 2013 at 9:12 PM, Javier de la Torre <jatorre@vizzuality.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 Beta2It 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 oneI 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
On Mon, Jul 29, 2013 at 10:08 PM, Javier de la Torre <jatorre@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.
$ psqlpsql (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
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.
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.
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
Andrew Tipton
Вложения
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:
<audit_ddl.sql>On Mon, Jul 29, 2013 at 10:08 PM, Javier de la Torre <jatorre@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.$ psqlpsql (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.
COMMITDisclaimer: 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