From: | pavan95 <pavan(dot)postgresdba(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: How to get alerted automatically whenever a table structure is changed between Publisher and Subscriber in Logical Replication? |
Date: | 2018-08-28 13:24:55 |
Message-ID: | 1535462695414-0.post@n3.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
>>You could also use event triggers:
https://www.postgresql.org/docs/current/static/event-triggers.html
Yes, But can I get the exact text so executed into a table. Please consider
the below example which clearly explains my requirement:
Step1: Created a table like below:
CREATE TABLE log_ddl_info(ddl_tag text, ddl_event text, ddl_time timestamp);
Step2: Created a function like below:
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;
Step3: Created event triggers as below:
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();
Step4: Triggered a DDL statement as:
create table aa (a int);
Finally issued select on the ddl history table:
select * from log_ddl_info;
ddl_tag | ddl_event | ddl_time
--------------+-------------------+-------------------------
CREATE TABLE | ddl_command_start | 2018-08-28 18:47:55.745
CREATE TABLE | ddl_command_end | 2018-08-28 18:47:55.745
But from the above output I also need the exact DDL statement text and which
db user triggered it. Is it possible??
Looking forward to hear from you!
Regards,
Pavan
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
From | Date | Subject | |
---|---|---|---|
Next Message | 066ce286 | 2018-08-28 13:34:50 | Re: tuple concurrently updated |
Previous Message | wambacher | 2018-08-28 13:19:28 | Re: tuple concurrently updated |