Re: [GENERAL] event triggers in 9.3.4
Hello 2014-07-25 20:46 GMT+02:00 Alvaro Herrera : > 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. > I wish some similar interface for PL/pgSQL - json based interface is not PL/pgSQL friendly. But it can live in contrib as extension over your deparse interface - maybe as relatively simple SQL functions. Regards Pavel > > -- > Álvaro Herrerahttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
Re: [GENERAL] event triggers in 9.3.4
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 Herrerahttp://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
Re: [GENERAL] event triggers in 9.3.4
Hello
I found a interesting extension
http://code.malloclabs.com/pg_schema_triggers
Regards
Pavel
2014-07-25 20:01 GMT+02:00 Alvaro Herrera :
> 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.gr18...@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/2014011505.gb29...@eldon.alvh.no-ip.org
>
> Thanks,
>
> --
> Álvaro Herrerahttp://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
>
Re: [GENERAL] event triggers in 9.3.4
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.gr18...@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/2014011505.gb29...@eldon.alvh.no-ip.org
Thanks,
--
Álvaro Herrerahttp://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
Re: [GENERAL] event triggers in 9.3.4
>> 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
Re: [GENERAL] event triggers in 9.3.4
On Thu, Jul 24, 2014 at 7:13 PM, Vasudevan, Ramya < ramya.vasude...@classmates.com> 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' > 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. depesz
Re: [GENERAL] event triggers in 9.3.4
>> 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
Re: [GENERAL] event triggers in 9.3.4
On Thu, Jul 24, 2014 at 2:22 AM, Vasudevan, Ramya <
ramya.vasude...@classmates.com> wrote:
> 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;
>
Why are you using dynamic query and not just run insert normally? And even
for dynamic query it shows basically a worst possible way to do it, that is
prone to sql injection. Of course the problem is unlikely now given that
the values come from pg itself, and have pretty well defined values, but
why do it unsafely even in such simple case?!
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/
depesz
Re: [GENERAL] event triggers in 9.3.4
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.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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
