Re: How to watch for schema changes

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.

> 
> 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?
> 
>>
>>>
>>> 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