Date: Tue, 28 Aug 2018 06:24:55 -0700 (MST)
From: pavan95 <pavan.postgresdba@gmail.com>
To: pgsql-admin@postgresql.org
Message-ID: <1535462695414-0.post@n3.nabble.com>
In-Reply-To: <394C9189-C119-4845-9D60-906C9018604C@elevated-dev.com>
References: <1535435281653-0.post@n3.nabble.com>
 <7f636d45-36db-d87b-171a-519e21690574@anayrat.info>
 <1535446386936-0.post@n3.nabble.com>
 <394C9189-C119-4845-9D60-906C9018604C@elevated-dev.com>
Subject: Re: How to get alerted automatically whenever a table structure is
 changed between Publisher and Subscriber in Logical Replication?
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

>>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