Is there a way to be notified on the CREATE TABLE execution?

On Tuesday, June 19, 2018, Igor Korot <ikorot01@gmail.com> wrote:

Hi, ALL,
Consider a scenario:

1. A software that uses libpq is executing.
2. Someone opens up a terminal and creates a table.
3. A software needs to know about this new table.

I'd start here:

https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html

Your scenario suggests you may wish to avail yourself of the Listen and
Notify commands as well.

David J.

I believe you could use an event trigger in postgres to capture the fact that a table was created: https://www.postgresql.org/docs/current/static/event-triggers.html
In the trigger you would then have to code whatever is needed to notify the external software (via a REST call or by posting something in a messaging bus, ...)

Regards, Pierre

On Wednesday, June 20, 2018, 12:08:48 AM GMT+2, Igor Korot <ikorot01@gmail.com> wrote:

Hi, ALL,
Consider a scenario:

1. A software that uses libpq is executing.
2. Someone opens up a terminal and creates a table.
3. A software needs to know about this new table.

I presume this is a DBMS-specific...

Thank you.

Hi, David,

On Tue, Jun 19, 2018 at 5:13 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Tuesday, June 19, 2018, Igor Korot <ikorot01@gmail.com> wrote:

Hi, ALL,
Consider a scenario:

1. A software that uses libpq is executing.
2. Someone opens up a terminal and creates a table.
3. A software needs to know about this new table.

I'd start here:

https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html

Your scenario suggests you may wish to avail yourself of the Listen and
Notify commands as well.

I did look at the Listen/Notify.
Unfortunately the listening is done on the channel versus listening
for the specific event.

I also looked at the
https://www.postgresql.org/docs/9.1/static/libpq-example.html#LIBPQ-EXAMPLE-2,
but am not sure how to create an appropriate event.

Thank you.

On Wed, Jun 20, 2018 at 5:20 PM, Igor Korot <ikorot01@gmail.com> wrote:

https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
Your scenario suggests you may wish to avail yourself of the Listen and
Notify commands as well.

I did look at the Listen/Notify.
Unfortunately the listening is done on the channel versus listening
for the specific event.

Channels are cheap. You just listen on "whatever" and in the event
trigger you notify "whatever", payload is optional.

The event trigger is the one which takes care of filtering the event
and notifying selectively.

You can use a channel per event.

Francisco Olarte.

Is there a way to be notified on the CREATE TABLE execution?

Here is sample code that will notify for a CREATE or DROP table:

CREATE TABLE public.tbl_create_log
(
tbl_cl_key bigint NOT NULL DEFAULT
nextval('tbl_create_log_tbl_cl_key_seq'::regclass),
tbl_cre8_time timestamp without time zone DEFAULT now(),
log_table_schema name,
log_table_name name,
log_session_user name,
CONSTRAINT tbl_create_log_pk PRIMARY KEY (tbl_cl_key)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.tbl_create_log
OWNER TO postgres;
GRANT ALL ON TABLE public.tbl_create_log TO postgres;

CREATE OR REPLACE FUNCTION public.fn_notify_ddl()
RETURNS event_trigger AS
$BODY$
DECLARE
obj record;
BEGIN
/*
RAISE INFO 'Type: %', TG_TAG;
RAISE INFO 'Command: %', current_query();
RAISE INFO 'DB Name: %', current_database();
RAISE INFO 'DB User: %', session_user;
RAISE INFO 'DB Port: %', inet_server_port();
RAISE INFO 'Server Host: %', inet_server_addr();
RAISE INFO 'Client Host: %', inet_client_addr();
*/
FOR obj IN SELECT *
FROM pg_event_trigger_ddl_commands() LOOP
IF obj.command_tag = 'CREATE TABLE'
OR obj.command_tag = 'DROP TABLE'THEN
-- RAISE INFO 'we got a % event for object "%"', obj.command_tag,
obj.object_identity;
INSERT INTO tbl_create_log
( log_table_schema,
log_table_name,
log_session_user
)
SELECT n.nspname,
c.relname,
session_user
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.oid = obj.objid
AND c.relkind = 'r';
END IF;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.fn_notify_ddl()
OWNER TO postgres;

CREATE EVENT TRIGGER table_created_dropped ON DDL_COMMAND_END
EXECUTE PROCEDURE public.fn_notify_ddl();

ALTER EVENT TRIGGER table_created_dropped
OWNER TO postgres;

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Thx, Francisco.
It makes sense now.

Just one more question:

This page https://www.postgresql.org/docs/current/static/xfunc-c.html#DFUNC
does not reference
Windows/MSVC/MinGW as a way to compile the code.

How should I do it?

Thx.

On Wed, Jun 20, 2018 at 11:44 AM, Francisco Olarte
<folarte@peoplecall.com> wrote:

Igor:

On Wed, Jun 20, 2018 at 7:07 PM, Igor Korot <ikorot01@gmail.com> wrote:

Just one more question:
This page https://www.postgresql.org/docs/current/static/xfunc-c.html#DFUNC
does not reference
Windows/MSVC/MinGW as a way to compile the code.

Sorry, I don't do windows.

You do not need C extension functions anyway, unless your usage
pattern is truly bizarre a triger for ddl could be written in any pl.

Francisco Olarte.

Melvin:

Maybe old eyes, but ...

On Wed, Jun 20, 2018 at 7:03 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

Is there a way to be notified on the CREATE TABLE execution?

Here is sample code that will notify for a CREATE or DROP table:

Doesn't this trigger just log the events? I think it's missing
something like "Notify create_or_drop" somewhere after the logging (
so a listener knows to look at the log table ).

( Normally I would use something like this, listen to a channel, do a
first scan from the saved last tbl_cl_key, and then do another scan
from the last each time listen fires, it seems the difficult work is
done but it misses the notify to signal listeners and avoid having to
rescan on a timer or a similar thing ).

( Maybe I missed the line, it would not be the first time, that's why I ask ).

Francisco Olarte.

On Wed, Jun 20, 2018 at 1:28 PM, Francisco Olarte <folarte@peoplecall.com>
wrote:

Melvin:

Maybe old eyes, but ...

On Wed, Jun 20, 2018 at 7:03 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:

Is there a way to be notified on the CREATE TABLE execution?

Here is sample code that will notify for a CREATE or DROP table:

Doesn't this trigger just log the events? I think it's missing
something like "Notify create_or_drop" somewhere after the logging (
so a listener knows to look at the log table ).

( Normally I would use something like this, listen to a channel, do a
first scan from the saved last tbl_cl_key, and then do another scan
from the last each time listen fires, it seems the difficult work is
done but it misses the notify to signal listeners and avoid having to
rescan on a timer or a similar thing ).

( Maybe I missed the line, it would not be the first time, that's why I
ask ).

Francisco Olarte.

Maybe old eyes, but ...
I think it's missing
something like "Notify create_or_drop" somewhere after the logging (
so a listener knows to look at the log table ).

Uh, I said it was a SAMPLE. It's not that difficult to add RAISE INFO... or
NOTIFY...
which, if you look, is commented out with generic information..
Of course, the exact message is left to the creator.

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Hi, Francisco,

On Wed, Jun 20, 2018 at 12:22 PM, Francisco Olarte
<folarte@peoplecall.com> wrote:

Igor:

On Wed, Jun 20, 2018 at 7:07 PM, Igor Korot <ikorot01@gmail.com> wrote:

Just one more question:
This page https://www.postgresql.org/docs/current/static/xfunc-c.html#DFUNC
does not reference
Windows/MSVC/MinGW as a way to compile the code.

Sorry, I don't do windows.

You do not need C extension functions anyway, unless your usage
pattern is truly bizarre a triger for ddl could be written in any pl.

From the https://www.postgresql.org/docs/current/static/event-trigger-definition.html:

[quote]
In order to create an event trigger, you must first create a function
with the special return type event_trigger. This function need not
(and may not) return a value; the return type serves merely as a
signal that the function is to be invoked as an event trigger.
[/quote]

So, the function has to be created and compiled.

Am I missing something?

Thank you.

On 2018-Jun-20, Igor Korot wrote:

[quote]
In order to create an event trigger, you must first create a function
with the special return type event_trigger. This function need not
(and may not) return a value; the return type serves merely as a
signal that the function is to be invoked as an event trigger.
[/quote]

So, the function has to be created and compiled.

Event trigger functions can be written in plpgsql. You can use
pg_event_trigger_ddl_commands() in the ddl_command_end event to obtain a
few tidbits about the DDL that was just executed.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Wed, Jun 20, 2018 at 1:02 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

On 2018-Jun-20, Igor Korot wrote:

[quote]
In order to create an event trigger, you must first create a function
with the special return type event_trigger. This function need not
(and may not) return a value; the return type serves merely as a
signal that the function is to be invoked as an event trigger.
[/quote]

So, the function has to be created and compiled.

​The section talking about C-language event triggers leads with:​

​"""
This section describes the low-level details of the interface to an event
trigger function. This information is only needed when writing event
trigger functions in C. If you are using a higher-level language then these
details are handled for you. In most cases you should consider using a
procedural language before writing your event triggers in C. The
documentation of each procedural language explains how to write an event
trigger in that language.
""" ​

​In short, I agree that cursory reading of the main event trigger chapter
could ​lead one to conclude that they are implemented in "C"; but all of
the relevant information is available in the docs and correctly pointed too
and accessible if one reads carefully or looks for it explicitly. I don't
see how it can be much improved simply and there doesn't seem to be enough
confusion (or use) to warrant significant effort in that area.

Event trigger functions can be written in plpgsql. You can use
pg_event_trigger_ddl_commands() in the ddl_command_end event to obtain a
few tidbits about the DDL that was just executed.

​There are a number of examples of a pl/pgsql function returning an
"event_trigger" ​pseudo-type: but I cannot locate an official statement
that doing so is valid. I was expecting a paragraph at [1]https://www.postgresql.org/docs/10/static/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS but it is not
there. Adding one and linking it to the overall event trigger chapter as
well as the event trigger section of the pl/pgsql chapter seems warranted.

[1]: https://www.postgresql.org/docs/10/static/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS
https://www.postgresql.org/docs/10/static/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS

David J.