Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tgUGP-00B71j-7N for pgsql-general@arkaria.postgresql.org; Fri, 07 Feb 2025 19:43:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tgUGN-0050xj-0r for pgsql-general@arkaria.postgresql.org; Fri, 07 Feb 2025 19:43:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tgUGM-0050tD-KT for pgsql-general@lists.postgresql.org; Fri, 07 Feb 2025 19:43:42 +0000 Received: from mail-yb1-xb29.google.com ([2607:f8b0:4864:20::b29]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tgUGJ-004Q5I-1V for pgsql-general@lists.postgresql.org; Fri, 07 Feb 2025 19:43:42 +0000 Received: by mail-yb1-xb29.google.com with SMTP id 3f1490d57ef6-e5b29779d74so2277090276.2 for ; Fri, 07 Feb 2025 11:43:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738957418; x=1739562218; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=xTPzWZzPwmfIwuyWlSP02Cq9wNIlF/Y1fsjAmsI6q7s=; b=TlsSwHrKRvJmS6TKkFeIV9KSo7bPmLp47PBRCiv2JmoBUwKvLTc9SCvAsMkdMFdmLY Q1vSC3VfAP84JaUYLKprDju4nkNwTB+IbSmO9EIy/2hyMX/zio7qRVUK5XoBDLSzhEZ2 Z3P4hiFSV3ZrFKtmOussISI85Xi9bQmZ03ufDZi75zlVo4qKePGhw0V8LKv9plwERnuZ GyOJPtkT3EQRT6qwN7iLXiAjnwm32PNvodydztnf5qU7989rgr7mWp+90yIOpMAxg/8M gdoGrZ59Q1+3LbRHithVfaUk2AThEppFpYlnwx8VD1FeXE43HBvtRaWm6yXZq5JcXfe2 0V/g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738957418; x=1739562218; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=xTPzWZzPwmfIwuyWlSP02Cq9wNIlF/Y1fsjAmsI6q7s=; b=Xeqr83K6NhcagmkCXqPlwu4MiDkUFgtm+7z6h9fFp9C5wAoby/rV0HRY8QG3jdnJlL J4zI7wYftbzFqBNHlMEPoBmRhdxtdKa0+r/Ri4VT+adygMuLYK6E75ve/7znJ08IveIJ oNnefgas/XQx4k0VBdBoxz62bncXU0+YTczrEu/TKaIHnBZID1ohr8Wy4+htF9SvQlST 8gRsoQHkcUSGICTyVOGHIXaUw0+wGQeOuPshqbYD6EVs/sI4SOyxvaJ9ONRAVLf4TSU4 jE4jdp11KP2fujtr3+LfP0HflROgb9eoiWcGrhM1zLFFL2CEbmAedumkCRLCGF+sc3Z4 X1yQ== X-Gm-Message-State: AOJu0YyBZaA7R96ctG/scDBxkmJ1rg6nlAOZSQjEdpIbrq4nxbheNxLH uH9XRcLXhrVirG9jcz+Nof0C03jXBSXUUp8gfxumj9QOJwaencWRp4I9+nG/SRFItULSH9asIEj DRWqf8+ccW8tH1whclfJjr0ZC3DeUaTJT X-Gm-Gg: ASbGncuI9lglOUwmu2IRaz5v/ZXTKTW3d8Z25Kcsx3ZHg0n6SFV/x78OKZMes9upLc4 GalqeThvLcEG6duoEkJUgDoE4ynyJf40qm7L8JcUPnruGpL9t2hSb7VdEB7bp/m4NzPE3lGZ4KC HTnRX+CIUWJk1lnMVTdRX3TFveIR3qU90= X-Google-Smtp-Source: AGHT+IFh2/aEfIGeMUan8R0lF6v9ZnZn1Rb0qxJroSAF7a8kTAnRoo+2LexcNDFSNHmrQUDaI5Y8kkH+Ibl1NQDf7Zs= X-Received: by 2002:a05:6902:240e:b0:e5b:2e2f:e4e6 with SMTP id 3f1490d57ef6-e5b461c2fe2mr4100417276.29.1738957417828; Fri, 07 Feb 2025 11:43:37 -0800 (PST) MIME-Version: 1.0 References: <5949efc2-64a0-4bb3-b837-2c7b90c3745b@aklaver.com> In-Reply-To: <5949efc2-64a0-4bb3-b837-2c7b90c3745b@aklaver.com> From: Igor Korot Date: Fri, 7 Feb 2025 13:43:24 -0600 X-Gm-Features: AWEUYZm98IZQCqHEyLqZ2_h3DwuJ787s8E81hxnYGpk4T6Zzm-VE1zc_LoDGJN0 Message-ID: Subject: Re: How to get a notification To: Adrian Klaver Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000002b58f1062d929373" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002b58f1062d929373 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, Adrian, On Fri, Feb 7, 2025, 1:13=E2=80=AFPM Adrian Klaver wrote: > > > On 2/7/25 11:02 AM, Igor Korot wrote: > > Hi, Adrian, > > > > On Fri, Feb 7, 2025 at 11:07=E2=80=AFAM Adrian Klaver > wrote: > >> > >> On 2/5/25 23:21, Igor Korot wrote: > >>> Hi, ALL, > >>> In my code I'm running following: > >>> > >>> queries.push_back( L"CREATE FUNCTION > >>> __watch_schema_changes() RETURNS event_trigger LANGUAGE plpgsql AS $$ > >>> BEGIN NOTIFY tg_tag; END; $$;" ); > >>> queries.push_back( L"CREATE EVENT TRIGGER > >>> schema_change_notify ON ddl_command_end WHEN TAG IN(\'CREATE TABLE\', > >>> \'ALTER TABLE\', \'DROP TABLE\', \'CREATE INDEX\', \'DROP INDEX\') > >>> EXECUTE PROCEDURE __watch_schema_changes();" ); > >>> > >>> My questions are: > >>> 1 Is there a better way to get notification about CREATE/ALTER/DROP > TABLE? > >> > >> An alternate solution: > >> > >> https://www.pgaudit.org/ > >> > >> Whether it is better or not is something you will need to decide. > >> > >>> 2. How do I receive notification abut the event with the object name? > >> > >> Use information from here?: > >> > >> > https://www.postgresql.org/docs/current/functions-event-triggers.html#PG-= EVENT-TRIGGER-DDL-COMMAND-END-FUNCTIONS > > > > Thx for the link > > So basically all I need to do is to call > > > > SELECT pg_event_trigger_ddl_commands () > > And then unpack the information it returns. > > > > > right? > > > > In both ODBC amd libpq interfaces? > > It is not about the interface it is about, from link above: > > "pg_event_trigger_ddl_commands returns a list of DDL commands executed > by each user action, when invoked in a function attached to a > ddl_command_end event trigger." > Is there a sample code i can look at? Also what is PostgreSQL version this function is introduced? Thank you. > > > > > > Thank you. > > > > > > > >> > >>> > >>> Thank you. > >>> > >>> > >> > >> -- > >> Adrian Klaver > >> adrian.klaver@aklaver.com > >> > > -- > Adrian Klaver > adrian.klaver@aklaver.com > --0000000000002b58f1062d929373 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi, Adrian,



On Fri, Feb 7, 2025, 1:13=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver.com> wrote:


On 2/7/25 11:02 AM, Igor Korot wrote:
> Hi, Adrian,
>
> On Fri, Feb 7, 2025 at 11:07=E2=80=AFAM Adrian Klaver <adria= n.klaver@aklaver.com> wrote:
>>
>> On 2/5/25 23:21, Igor Korot wrote:
>>> Hi, ALL,
>>> In my code I'm running following:
>>>
>>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0queries.= push_back( L"CREATE FUNCTION
>>> __watch_schema_changes() RETURNS event_trigger LANGUAGE plpgsq= l AS $$
>>> BEGIN NOTIFY tg_tag; END; $$;" );
>>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0queries.= push_back( L"CREATE EVENT TRIGGER
>>> schema_change_notify ON ddl_command_end WHEN TAG IN(\'CREA= TE TABLE\',
>>> \'ALTER TABLE\', \'DROP TABLE\', \'CREATE = INDEX\', \'DROP INDEX\')
>>> EXECUTE PROCEDURE __watch_schema_changes();" );
>>>
>>> My questions are:
>>> 1 Is there a better way to get notification about CREATE/ALTER= /DROP TABLE?
>>
>> An alternate solution:
>>
>> https://www.pgaudit.org/
>>
>> Whether it is better or not is something you will need to decide.<= br> >>
>>> 2. How do I receive notification abut the event with the objec= t name?
>>
>> Use information from here?:
>>
>> https://www.postgresql.org/docs/current/fun= ctions-event-triggers.html#PG-EVENT-TRIGGER-DDL-COMMAND-END-FUNCTIONS >
> Thx for the link
> So basically all I need to do is to call
>
> SELECT pg_event_trigger_ddl_commands ()

And then unpack the information it returns.

>
> right?
>
> In both ODBC amd libpq interfaces?

It is not about the interface it is about, from link above:

"pg_event_trigger_ddl_commands returns a list of DDL commands executed=
by each user action, when invoked in a function attached to a
ddl_command_end event trigger."

Is there a sample code i can look at?=
Also what is PostgreSQL version this function is in= troduced?

Thank you.




>
> Thank you.
>
>
>
>>
>>>
>>> Thank you.
>>>
>>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>

--
Adrian Klaver
adrian.klaver@aklaver.com
--0000000000002b58f1062d929373--