Re: How to watch for schema changes

On 07/09/2018 01:49 PM, Igor Korot wrote:
> Hi, Adrian
>
> On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)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

> 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(dot)klaver(at)aklaver(dot)com
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com