Re: How to watch for schema changes

Hi, guys,


On Mon, Jul 9, 2018 at 5:38 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 07/09/2018 01:49 PM, Igor Korot wrote:
>>
>> Hi, Adrian
>>
>> On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver <adrian.klaver@aklaver.com>
>> wrote:
>>>
>>> On 07/03/2018 11:15 AM, Igor Korot wrote:
>>>>
>>>>
>>>> Adrian,
>>>>
>>>> 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
>>>>
>>>>
>>>>
>>>> According to the documentation the lowest version it supports is 9.3.
>>>> Anything prior to that?
>>>>
>>>> I'm working with OX 10.8 and it has 9.1 installed.
>>>
>>>
>>> 9.1 went EOL almost two years ago. The oldest supported version is 9.3,
>>> though it will go EOL this September:
>>>
>>> https://www.postgresql.org/support/versioning/
>>>
>>> Are you forced to work with 9.1 or can you use something from here:
>>>
>>> https://www.postgresql.org/download/macosx/
>>>
>>> to get a newer version? FYI that will be a major upgrade so will require
>>> a
>>> dump/restore or use of pg_upgrade.
>>
>>
>> Just a thought...
>> Is it possible to create a trigger for a system table? Or this
>> operation is restricted
>
>
> Easy enough to test. As postgres super user:
>
> test_(postgres)# create trigger info_test before insert on pg_class execute
> procedure ts_update();
>
> ERROR:  permission denied: "pg_class" is a system catalog

But

draft=# CREATE TRIGGER info_test AFTER INSERT ON
information_schema.tables EXECUTE PROCEDURE test();
ERROR:  function test() does not exist

So it looks like this should be possible?

Thank you.

>
>
>> for when the server is actually being set-up?
>>
>> Successful "CREATE TABLE..." statement creates a row inside the
>> information_schema.tables
>> so if I can create a trigger after this record is inserted or deleted
>> that should be fine.
>>
>> Thank you.
>>
>>>
>>>>
>>>> And a second question - how do I work with it?
>>>> I presume that function will have to be compiled in its own module
>>>> (either dll, so or dylib).
>>>> But then from the libpq interface how do I call it?
>>>
>>>
>>>
>>> It can use functions written in PL languages. See below:
>>>
>>> https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
>>>
>>> for an example written in plpgsql.
>>>
>>>
>>>
>>>
>>>
>>>>
>>>> Thank you.
>>>>
>>>>>
>>>>>>
>>>>>> Thank you.
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Adrian Klaver
>>>>> adrian.klaver@aklaver.com
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.klaver@aklaver.com
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com