Re: How to watch for schema changes

2018-07-12 6:12 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 07/11/2018 08:46 PM, Igor Korot wrote:
>
>> Hi, guys,
>>
>>
>> On Mon, Jul 9, 2018 at 5:38 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(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
>>>
>>
>> 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?
>>
>
> No, see:
>
> https://www.postgresql.org/docs/10/static/sql-createtrigger.html
>
> AFTER trigger on views are STATEMENT level only.
>
> https://www.postgresql.org/docs/10/static/plpgsql-trigger.html
>
> "NEW
>
> Data type RECORD; variable holding the new database row for
> INSERT/UPDATE operations in row-level triggers. This variable is unassigned
> in statement-level triggers and for DELETE operations.
> OLD
>
> Data type RECORD; variable holding the old database row for
> UPDATE/DELETE operations in row-level triggers. This variable is unassigned
> in statement-level triggers and for INSERT operations.
> "
>
> So you won't know what was INSERTed in row.
>
>
Moreover, there is nothing inserted into this view. It's inserted into
pg_class, and an access to information_schema.tables just reads that
catalog.

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

--
Guillaume.