Re: How to watch for schema changes - Mailing list pgsql-general

On Mon, Dec 3, 2018 at 11:29 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 12/3/18 8:16 AM, Igor Korot wrote:
> > Hi, Adrian,
> >
> > On Mon, Dec 3, 2018 at 9:17 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> >>
> >> On 12/2/18 5:24 AM, Igor Korot wrote:
> >>> Hi, Adrian,
> >>> Sorry for the delay to come back to this. I was busy doing other things.
> >>>
> >>> On Tue, Jul 3, 2018 at 12:32 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> >>>>
> >>>> On 07/03/2018 10:21 AM, Igor Korot wrote:
> >>>>> Hi, ALL,
> >>>>> Is there any trigger or some other means I can do on the server
> >>>>> which will watch for CREATE/ALTER/DROP TABLE command and after successful
> >>>>> execution of those will issue a NOTIFY statement?
> >>>>
> >>>> https://www.postgresql.org/docs/10/static/event-triggers.html
> >>>
> >>> So if I understand correctly, I should write the trigger for the event
> >>> I am interested in.
> >>> And in this trigger I write a little SQL that will write the DDL
> >>> command in some temporary table.
> >>>
> >>> I'm just looking for a way to execute this trigger and a function from
> >>> my C++ code
> >>> on the connection (either ODBC or thru the libpq).
> >>
> >> Event triggers are fired by database events not by external prompting,
> >> so you do not need to have your code execute the trigger. You do have
> >> the option of disabling/enabling then though:
> >>
> >> https://www.postgresql.org/docs/10/sql-altereventtrigger.html
> > .
> > Yes, but the code to the event triogger needs to be written and then executed on
> > connection, right?
> >
> > So, this is what I'm thingking:
> >
> > [code]
> > ret = SQLExecute( m_hstmt, L"CREATE FUNCTION schema_watch_check()
> > RETURNS event_trigger AS schema_watch LANGUAGE C", SQL_NTS );
> > if( ret == SQL_SUCCESS || ret== SQL_SUCCESS_WITH_INFO )
> > {
> >      ret = SQLExecute( m_hstmt, L"CREATE EVENT TRIGGER schema_watch ON
> > ddl_command_end EXECUTE PROCEDURE schema_watch_check()", SQL_NTS );
> > }
> > [/code]
> >
> > and something to that extent on the libpq connection.
> >
> > Am I missing something here?
>
> Yes this:
>
> https://www.postgresql.org/docs/9.6/sql-createeventtrigger.html
>
> "CREATE EVENT TRIGGER creates a new event trigger. Whenever the
> designated event occurs and the WHEN condition associated with the
> trigger, if any, is satisfied, the trigger function will be executed.
> For a general introduction to event triggers, see Chapter 38. The user
> who creates an event trigger becomes its owner."
>
> So event triggers are associated with
> events(www.postgresql.org/docs/9.6/event-trigger-matrix.html) in a
> particular database. A rough description is that they are triggers on
> changes to the system catalogs.
> You could, I guess, create and drop them for each connection. To me it
> would seem more efficient to create them once. You then have the choice
> of leaving them running or using the ALTER command I posted previously
> to ENABLE/DISABLE them.

OK, so how do I do it?
There is no "CREATE EVENT TRIGGER IF NOT EXIST".

As I say - I'm trying to make it work from both ODBC and libpq
connection (one at a time).

>
> >
> > Now the other question is - how to make it work?
> > I can write the function code, compile it and place in the folder
> > where my executable is (or it should be in some postgreSQL dir?) and
> > then executing above code
> > will be enough. Is this correct?

Also - what about this?

And why did you say that saving the SQL commend is not a good idea.

What's better?

Thank you.

> >
> >>
> >>>
> >>> And then in my C++ code I will continuously query this temporary table.
> >>
> >> Why a temporary table? They are session specific and if the session
> >> aborts prematurely you will lose the information.
> >
> > Is there a better alternative?
> >
> > Thank you.
> >
> >>
> >>>
> >>> Or there is a better alternative on the algorithm?
> >>>
> >>> Thank you.
> >>>
> >>>>>>
> >>>>> Thank you.
> >>>>>
> >>>>
> >>>>
> >>>> --
> >>>> Adrian Klaver
> >>>> adrian.klaver@aklaver.com
> >>>
> >>
> >>
> >> --
> >> Adrian Klaver
> >> adrian.klaver@aklaver.com
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com