Re: How to watch for schema changes

On 07/11/2018 08:46 PM, Igor Korot wrote:
> 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?

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.

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com