Thread: event triggers in 9.3.4
I set up the following to log all DDLs executed in the database:
CREATE TABLE log_ddl_info(ddl_tag text, ddl_event text, ddl_time timestamp);
CREATE OR REPLACE FUNCTION log_ddl_execution()
RETURNS event_trigger AS $$
DECLARE
insertquery TEXT;
BEGIN
insertquery := 'INSERT INTO log_ddl_info VALUES (''' || tg_tag ||''', ''' || tg_event || ''', statement_timestamp())';
EXECUTE insertquery;
RAISE NOTICE 'Recorded execution of command % with event %', tg_tag, tg_event;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER log_ddl_info_start ON ddl_command_start EXECUTE PROCEDURE log_ddl_execution();
CREATE EVENT TRIGGER log_ddl_info_end ON ddl_command_end EXECUTE PROCEDURE log_ddl_execution();
Is there a way to log the object name (or the oid) in the function?
Thank you,
________________
Ramya Vasudevan
Database Administrator
CLASSMATES
333 Elliott Ave. West, Suite 500
Seattle, WA 98119
206.301.4933 o
On 07/23/2014 05:22 PM, Vasudevan, Ramya wrote:
> I set up the following to log all DDLs executed in the database:
>
> CREATE TABLE log_ddl_info(ddl_tag text, ddl_event text, ddl_time timestamp);
>
> CREATE OR REPLACE FUNCTION log_ddl_execution()
>
> RETURNS event_trigger AS $$
>
> DECLARE
>
> insertquery TEXT;
>
> BEGIN
>
> insertquery := 'INSERT INTO log_ddl_info VALUES (''' || tg_tag ||''',
> ''' || tg_event || ''', statement_timestamp())';
>
> EXECUTE insertquery;
>
> RAISE NOTICE 'Recorded execution of command % with event %', tg_tag,
> tg_event;
>
> END;
>
> $$ LANGUAGE plpgsql;
>
> CREATE EVENT TRIGGER log_ddl_info_start ON ddl_command_start EXECUTE
> PROCEDURE log_ddl_execution();
>
> CREATE EVENT TRIGGER log_ddl_info_end ON ddl_command_end EXECUTE
> PROCEDURE log_ddl_execution();
>
> Is there a way to log the object name (or the oid) in the function?
The only thing I see is for dropped objects:
http://www.postgresql.org/docs/9.4/static/functions-event-triggers.html
pg_event_trigger_dropped_objects()
>
> Thank you,
>
--
Adrian Klaver
adrian.klaver@aklaver.com
CREATE TABLE log_ddl_info(ddl_tag text, ddl_event text, ddl_time timestamp);
CREATE OR REPLACE FUNCTION log_ddl_execution()
RETURNS event_trigger AS $$
DECLARE
insertquery TEXT;
BEGIN
insertquery := 'INSERT INTO log_ddl_info VALUES (''' || tg_tag ||''', ''' || tg_event || ''', statement_timestamp())';
EXECUTE insertquery;
RAISE NOTICE 'Recorded execution of command % with event %', tg_tag, tg_event;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER log_ddl_info_start ON ddl_command_start EXECUTE PROCEDURE log_ddl_execution();
CREATE EVENT TRIGGER log_ddl_info_end ON ddl_command_end EXECUTE PROCEDURE log_ddl_execution();
Is there a way to log the object name (or the oid) in the function?
>> You could compare list of tables before (_start) and after (_end) the ddl. Doing it in plpgsql will be tricky, but if you'd use some other language - like plperl - it's relatively simple: http://www.depesz.com/2013/12/18/waiting-for-9-4-plperl-add-event-trigger-support/
Thank You Depesz. This will work for ‘CREATE’ and ‘DROP’ DDLs.
But, what about the scenario where I want to just have event triggers for operations like these? - 'ALTER TABLE','ALTER TRIGGER', 'ALTER FUNCTION'
CREATE EVENT TRIGGER log_ddl_info_start
ON
ddl_command_start
when
tag in
('ALTER TABLE','ALTER TRIGGER','CREATE FUNCTION','ALTER FUNCTION')
EXECUTE PROCEDURE
log_ddl_execution();
In this case, is there a way to capture the object that was altered?
Thank You
Ramya
>> You could compare list of tables before (_start) and after (_end) the ddl. Doing it in plpgsql will be tricky, but if you'd use some other language - like plperl - it's relatively simple: http://www.depesz.com/2013/12/18/waiting-for-9-4-plperl-add-event-trigger-support/
Thank You Depesz. This will work for ‘CREATE’ and ‘DROP’ DDLs.
But, what about the scenario where I want to just have event triggers for operations like these? - 'ALTER TABLE','ALTER TRIGGER', 'ALTER FUNCTION'
In any way - if you want to really use it - you'll have to write in C.
depesz
>> Sure - just check differences in appropriate catalogs. pg_attribute, pg_trigger, pg_proc.
>> In any way - if you want to really use it - you'll have to write in C.
Thank You Depesz. I think that answered my question.
Thank You
Ramya
Vasudevan, Ramya wrote: > >> You could compare list of tables before (_start) and after (_end) the ddl. Doing it in plpgsql will be tricky, but ifyou'd use some other language - like plperl - it's relatively simple: http://www.depesz.com/2013/12/18/waiting-for-9-4-plperl-add-event-trigger-support/ > Thank You Depesz. This will work for ‘CREATE’ and ‘DROP’ DDLs. > > But, what about the scenario where I want to just have event triggers for operations like these? - 'ALTER TABLE','ALTERTRIGGER', 'ALTER FUNCTION' > CREATE EVENT TRIGGER log_ddl_info_start > ON > ddl_command_start > when > tag in > ('ALTER TABLE','ALTER TRIGGER','CREATE FUNCTION','ALTER FUNCTION') > EXECUTE PROCEDURE > log_ddl_execution(); > > In this case, is there a way to capture the object that was altered? Not yet, but there's a patch in progress to help with this. If you're interested in the feature, please give the patch a try and see if the user interface it provides solves your use case. We want to ensure that the new feature we're creating is useful. You can find the patch here: http://www.postgresql.org/message-id/20140613203156.GR18688@eldon.alvh.no-ip.org (there are lots of small patches to ease review, so please grab them all and apply one by one) and a sample event trigger function (there are no docs yet) here: http://www.postgresql.org/message-id/20140115051111.GB29105@eldon.alvh.no-ip.org Thanks, -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Vasudevan, Ramya wrote:
> >> You could compare list of tables before (_start) and after (_end) the ddl. Doing it in plpgsql will be tricky, but if you'd use some other language - like plperl - it's relatively simple: http://www.depesz.com/2013/12/18/waiting-for-9-4-plperl-add-event-trigger-support/
> Thank You Depesz. This will work for ‘CREATE’ and ‘DROP’ DDLs.
>
> But, what about the scenario where I want to just have event triggers for operations like these? - 'ALTER TABLE','ALTER TRIGGER', 'ALTER FUNCTION'
> CREATE EVENT TRIGGER log_ddl_info_start
> ON
> ddl_command_start
> when
> tag in
> ('ALTER TABLE','ALTER TRIGGER','CREATE FUNCTION','ALTER FUNCTION')
> EXECUTE PROCEDURE
> log_ddl_execution();
>
> In this case, is there a way to capture the object that was altered?
Not yet, but there's a patch in progress to help with this. If you're
interested in the feature, please give the patch a try and see if the
user interface it provides solves your use case. We want to ensure that
the new feature we're creating is useful.
You can find the patch here:
http://www.postgresql.org/message-id/20140613203156.GR18688@eldon.alvh.no-ip.org
(there are lots of small patches to ease review, so please grab them all
and apply one by one) and a sample event trigger function (there are no
docs yet) here:
http://www.postgresql.org/message-id/20140115051111.GB29105@eldon.alvh.no-ip.org
Thanks,
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Pavel Stehule wrote: > Hello > > I found a interesting extension > http://code.malloclabs.com/pg_schema_triggers Ah, I remember that. I find that approach more cumbersome to use than mine. Note the ALTER cases can't tell you much about exactly how the relation has changed; you have to run comparisons of the pg_class rows manually, which is not very nice. I see this extension as a stopgap measure until we have real support for this in 9.5, per my patch. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Pavel Stehule wrote:Ah, I remember that. I find that approach more cumbersome to use than
> Hello
>
> I found a interesting extension
> http://code.malloclabs.com/pg_schema_triggers
mine. Note the ALTER cases can't tell you much about exactly how the
relation has changed; you have to run comparisons of the pg_class rows
manually, which is not very nice. I see this extension as a stopgap
measure until we have real support for this in 9.5, per my patch.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services