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.klaver@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

On Thu, Jul 24, 2014 at 2:22 AM, Vasudevan, Ramya <
ramya.vasudevan@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

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

On Thu, Jul 24, 2014 at 7:13 PM, Vasudevan, Ramya <
ramya.vasudevan@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

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 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:
/messages/by-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:
/messages/by-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

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 <alvherre@2ndquadrant.com>:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello

2014-07-25 20:46 GMT+02:00 Alvaro Herrera <alvherre@2ndquadrant.com>:

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