Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUlyg-0000mG-AR for pgsql-general@arkaria.postgresql.org; Wed, 22 Feb 2023 10:03:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pUlyf-0002NV-6x for pgsql-general@arkaria.postgresql.org; Wed, 22 Feb 2023 10:03:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUlye-0002NM-OV for pgsql-general@lists.postgresql.org; Wed, 22 Feb 2023 10:03:56 +0000 Received: from smtp33.i.mail.ru ([95.163.41.74]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUlyZ-00011N-PA for pgsql-general@postgresql.org; Wed, 22 Feb 2023 10:03:56 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=aeronavigator.ru; s=mailru; h=Content-Transfer-Encoding:Content-Type:MIME-Version:Message-ID:Date:Subject:In-Reply-To:References:To:From:From:Subject:Content-Type:Content-Transfer-Encoding:To:Cc; bh=fiWpXO53xXajfwrQjv9zXwHnq3jl/QTfVc9be438qrk=; t=1677060231;x=1677150231; b=j1dQqKkpeRXp3R96HfZyFamfGk2AgFUzR21iD7kNCVBYzUmzaPlMKLSHwZKhP6XRdn+GTUXUlh0ou1XDT9Ijp4dENv0pB8f0pV7OJWAvk6yCEdDzuw7/30EFagfyn4P+qFHCXrO/ghlk6w1W5nOfrlnHZTsyC94Vr+n5VL2n70Q=; Received: by smtp33.i.mail.ru with esmtpa (envelope-from ) id 1pUlyY-0010Kf-6D; Wed, 22 Feb 2023 13:03:50 +0300 From: To: "'Laurenz Albe'" , "'Neethu P'" , "'pgsql-general'" References: <714b89f449005ed4348e2114f6ce5f8aff18ea8a.camel@cybertec.at> In-Reply-To: <714b89f449005ed4348e2114f6ce5f8aff18ea8a.camel@cybertec.at> Subject: RE: Event Triggers unable to capture the DDL script executed Date: Wed, 22 Feb 2023 13:03:48 +0300 Message-ID: <007901d946a4$f5a6f200$e0f4d600$@aeronavigator.ru> MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Outlook 16.0 Content-Language: ru Thread-Index: AQJONmw9J2AJI1T5uJfZQ8WGSMp55wH1b2bireEvpcA= Authentication-Results: smtp33.i.mail.ru; auth=pass smtp.auth=n.kobzarev@aeronavigator.ru smtp.mailfrom=n.kobzarev@aeronavigator.ru X-Mailru-Src: smtp X-4EC0790: 10 X-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD9806C989EC2150E33845A783FBEA90D4AAFE6C1F93BD43520182A05F538085040DE671351951A24AA230574FC0DF3CAC44910A25DA9FB1201483AEAA326FBBB6D X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE76D4A2B7BAC4DEDB8EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F79006375D8840FA58F505298638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D82CDC72346363F6C53E03E05781C24714117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCF84B8A2F7AA0758EA471835C12D1D9774AD6D5ED66289B52BA9C0B312567BB23117882F4460429728776938767073520C65AC60A1F0286FE2CC0D3CB04F14752D2E47CDBA5A96583BA9C0B312567BB2376E601842F6C81A19E625A9149C048EE9647ADFADE5905B145B9AC499A3C791CD8FC6C240DEA7642DBF02ECDB25306B2B78CF848AE20165D0A6AB1C7CE11FEE3724336BCC0EE1BA8040F9FF01DFDA4A8C4224003CC836476E2F48590F00D11D6E2021AF6380DFAD1A18204E546F3947CB11811A4A51E3B096D1867E19FE1407959CC434672EE6371089D37D7C0E48F6C8AA50765F79006372C56F9BC9525AE6AEFF80C71ABB335746BA297DBC24807EABDAD6C7F3747799A X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D341ADA1A41A420E9B26D37D9E85C01E634BD8FA5A1B33DA8C4168B76E4221E4E03E98971790750A30A1D7E09C32AA3244CF0CA4E2E5EF6D3AA2A67E96823A5F3FE725D5B54B2FE4575FACE5A9C96DEB163 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojTRJ114TsKSONOqsWHTBvUA== X-Mailru-Sender: 657AADBC28A10CF59345A964B813C8DD6313CCAECB369118C08A6DC4A91DAE67C268E26D821F76D93A9D95EB0D5C971CBC7555A253F5B200131C2098FCBFB874F5AA4D3603535D2C19FD46FE8BB01CE2B4A721A3011E896F X-Mras: Ok List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk >>-----=D0=98=D1=81=D1=85=D0=BE=D0=B4=D0=BD=D0=BE=D0=B5 = =D1=81=D0=BE=D0=BE=D0=B1=D1=89=D0=B5=D0=BD=D0=B8=D0=B5----- >>=D0=9E=D1=82: Laurenz Albe =20 >>=D0=9E=D1=82=D0=BF=D1=80=D0=B0=D0=B2=D0=BB=D0=B5=D0=BD=D0=BE: 22 = =D1=84=D0=B5=D0=B2=D1=80=D0=B0=D0=BB=D1=8F 2023 =D0=B3. 12:52 >>=D0=9A=D0=BE=D0=BC=D1=83: Neethu P ; = pgsql-general >>=D0=A2=D0=B5=D0=BC=D0=B0: Re: Event Triggers unable to capture the DDL = script executed >>On Wed, 2023-02-22 at 07:57 +0000, Neethu P wrote: >>> We are using event triggers to capture the DDL changes in a postgres = database. >>> However, we are unable to get the column information & the actual = DDL=20 >>> script executed, while a table is altered. >>> Also, in the postgres documentation for = pg_event_trigger_ddl_commands ()- it is mentioned as below. >>> pg_ddl_command A complete representation of the command, in internal >>> format. Thiscannot be output directly, but it can be passed to other = >>> functions to obtain different pieces of information about the = command. >>>=20 >>> Is it possible to access pg_ddl_command in postgresql? Or is there = any=20 >>> scripts which can help to get theactual Alter DDL statement that was = executed by the user? >>That is simple if you write the event trigger in C. I would say that = that is the only way to get at the actual statement. >>Yours, >>Laurenz Albe In MSSQL there is a brilliant possibility to have a server wide trigger = to monitor commands. We are using It to have a history for all DDL = operations. Please try this (on new empty database) and give a feedback. CREATE OR REPLACE FUNCTION public.notice_ddl() RETURNS event_trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF AS $BODY$ DECLARE r RECORD; begin raise info '%', session_user || ' ran '||tg_tag||' = '||current_query(); FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP RAISE NOTICE 'we got a % event for object " %"',=20 r.command_tag, r.object_identity; END LOOP; end; $BODY$; CREATE OR REPLACE FUNCTION public.notice_ddl_drop() RETURNS event_trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF AS $BODY$ DECLARE r RECORD; begin raise info '%', session_user || ' ran '||tg_tag||' = '||current_query(); FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP RAISE NOTICE 'dropped: type "%" identity %', r.object_type, r.object_identity; END LOOP; end; $BODY$; CREATE EVENT TRIGGER etg ON DDL_COMMAND_END EXECUTE PROCEDURE public.notice_ddl(); CREATE EVENT TRIGGER etg_drop ON SQL_DROP EXECUTE PROCEDURE public.notice_ddl_drop();