Обсуждение: Is there a way to be notified on the CREATE TABLE execution?
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.
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:
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. > > David J. > > >
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?
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;
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;
(
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;
--
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!
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: > 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.
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 ).
>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!
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. > > Francisco Olarte.
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 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] 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.
David J.