Thread: How to watch for schema changes

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?

Thank you.

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

> 
> Thank you.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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.

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?

Thank you.

>
>>
>> Thank you.
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

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.

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

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.

Unfortunately I'm stuck with 9.1.
But I have a Linux machine which have a newer version so I can test
this solution.
And it would be nice to have both machine/versions working.

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

OK.
I presume threre is a query which check for the function/trigger
existence? Something like:

IF NOT EXIST(SELECT * FROM ) CREATE OR REPLACE FUNCTION....;

Thank you.

>
>
>
>
>
>>
>> Thank you.
>>
>>>
>>>>
>>>> Thank you.
>>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.klaver@aklaver.com
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

On 07/03/2018 11:41 AM, Igor Korot wrote:
> 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:

>>
>> 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.
> 
> Unfortunately I'm stuck with 9.1.
> But I have a Linux machine which have a newer version so I can test
> this solution.
> And it would be nice to have both machine/versions working.
> 

Well it won't be back ported so I am not seeing that working in 9.1 and 
I don't know of any other solution.

>> 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.
> 
> OK.
> I presume threre is a query which check for the function/trigger
> existence? Something like:
> 
> IF NOT EXIST(SELECT * FROM ) CREATE OR REPLACE FUNCTION....;

https://www.postgresql.org/docs/10/static/catalog-pg-event-trigger.html

> 
> Thank you.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi, Melvin,

On Tue, Jul 3, 2018 at 6:48 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
>
>>I'm writing a client in C++ with libpq. So I will have to do a lot of
>> polling .
> Can't you just run a cron job?

And what?
As I said I'm writing the client application with libpq/ODBC. How will I get
the results?

Thank you.

>
> --
> Melvin Davidson
> Maj. Database & Exploration Specialist
> Universe Exploration Command – UXC
> Employment by invitation only!

Hi, David,

On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot <ikorot01@gmail.com> wrote:
>>
>>
>> I presume threre is a query which check for the function/trigger
>> existence? Something like:
>>
>> IF NOT EXIST(SELECT * FROM ) CREATE OR REPLACE FUNCTION....;
>
>
> CREATE OR REPLACE is how you re-create a function that (whose
> name/signature) might already exist; CREATE already assumes one doesn't
> exist.

Why do I need to re-create a function with exactly the same name and body?
Can't I just check if such function exists?

Thank you.

>
> David J.
>

On 07/05/2018 08:40 AM, Igor Korot wrote:
> Hi, David,
> 
> On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot <ikorot01@gmail.com> wrote:
>>>
>>>
>>> I presume threre is a query which check for the function/trigger
>>> existence? Something like:
>>>
>>> IF NOT EXIST(SELECT * FROM ) CREATE OR REPLACE FUNCTION....;
>>
>>
>> CREATE OR REPLACE is how you re-create a function that (whose
>> name/signature) might already exist; CREATE already assumes one doesn't
>> exist.
> 
> Why do I need to re-create a function with exactly the same name and body

If you use CREATE OR REPLACE FUNCTION it will do just that each time you 
call it. Seems like overkill to me. See below for another way.

> Can't I just check if such function exists?

Something like this:

SELECT
     count(*)
FROM
     pg_proc AS proc
JOIN
     pg_namespace AS ns
ON
     proc.pronamespace = ns.oid
WHERE
     ns.nspname='public' -- Function schema
AND
     proname = 'tag_rcv_undo' -- Function name
;

> 
> Thank you.
> 
>>
>> David J.
>>


-- 
Adrian Klaver
adrian.klaver@aklaver.com

David,

On Thu, Jul 5, 2018 at 11:49 AM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Thu, Jul 5, 2018 at 8:40 AM, Igor Korot <ikorot01@gmail.com> wrote:
>>
>> Hi, David,
>>
>> On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston
>> <david.g.johnston@gmail.com> wrote:
>> > On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot <ikorot01@gmail.com> wrote:
>> >>
>> >>
>> >> I presume threre is a query which check for the function/trigger
>> >> existence? Something like:
>> >>
>> >> IF NOT EXIST(SELECT * FROM ) CREATE OR REPLACE FUNCTION....;
>> >
>> >
>> > CREATE OR REPLACE is how you re-create a function that (whose
>> > name/signature) might already exist; CREATE already assumes one doesn't
>> > exist.
>>
>> Why do I need to re-create a function with exactly the same name and body?
>> Can't I just check if such function exists?
>
>
> You can, and depending on how often you intend to execute said code, it is
> probably the better way.  It also requires pl/pgsql while CREATE OR REPLACE
> "just works" as a single SQL command.  It seems easier to give you the
> simple answer than to work out the details for the more complex one.

Is it the same from the DB server POV? Meaning it is also the same 1/2
hit depending on the existence? Also performance-wise querying and this
method is the same, right?

Thank you.

>
> David J.
>

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

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

> 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

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

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

Hi, Adrian,

On Wed, Jul 11, 2018 at 11:12 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> 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.

But I do have access to the STATEMENT right?

Thank you.

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

On 07/12/2018 09:16 AM, David G. Johnston wrote:
> On Thu, Jul 12, 2018 at 8:50 AM, Igor Korot <ikorot01@gmail.com 
> <mailto:ikorot01@gmail.com>>wrote:
> 
>      > No, see:
>      >
>      > https://www.postgresql.org/docs/10/static/sql-createtrigger.html
>     <https://www.postgresql.org/docs/10/static/sql-createtrigger.html>
>      >
>      > AFTER trigger on views are STATEMENT level only.
> 
>     But I do have access to the STATEMENT right?

Even if the below(David's explanation) did not hold there is nothing in:

https://www.postgresql.org/docs/10/static/plpgsql-trigger.html

that allows for capturing the statement.

> 
> 
> ​Yes, except nothing in the system actually attempts to directly target 
> information_schema views with updates so there will never be a 
> triggering event.​
> 
> A normal trigger will not work - which is a large reason why event 
> triggers were implemented.
> 
> All of the alternative ideas (which I think was just log file parsing) 
> you've decided are not viable for your need.  Thus you've seemingly 
> eliminated all viable options and you now need to make a business decision.
> 
> David J.
> 
> p.s. Please remove the excess quoted material before hitting send.  At 
> minimum anything after the last text that you write.​


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi,


On Thu, Jul 12, 2018 at 12:16 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Thu, Jul 12, 2018 at 8:50 AM, Igor Korot <ikorot01@gmail.com> wrote:
>>
>> > No, see:
>> >
>> > https://www.postgresql.org/docs/10/static/sql-createtrigger.html
>> >
>> > AFTER trigger on views are STATEMENT level only.
>>
>> But I do have access to the STATEMENT right?
>
>
> Yes, except nothing in the system actually attempts to directly target
> information_schema views with updates so there will never be a triggering
> event.
>
> A normal trigger will not work - which is a large reason why event triggers
> were implemented.
>
> All of the alternative ideas (which I think was just log file parsing)
> you've decided are not viable for your need.  Thus you've seemingly
> eliminated all viable options and you now need to make a business decision.

[code]
MyMac:/ igorkorot$ find . -name postgresql.conf
find: ./.DocumentRevisions-V100: Permission denied
find: ./.fseventsd: Permission denied
find: ./.Spotlight-V100: Permission denied
find: ./.Trashes: Permission denied
find: ./dev/fd/3: Not a directory
find: ./dev/fd/4: Not a directory
find: ./Library/Application Support/Apple/ParentalControls/Users:
Permission denied
find: ./Library/Caches/com.apple.Spotlight/schema.501.plist: Permission denied
find: ./Library/Caches/com.apple.Spotlight/schema.502.plist: Permission denied
find: ./Library/Caches/com.apple.Spotlight/schema.89.plist: Permission denied
find: ./Library/Caches/com.apple.Spotlight: Permission denied
find: ./Library/PostgreSQL/9.1/data: Permission denied
find: ./private/etc/cups/certs: Permission denied
find: ./private/etc/raddb/certs: Permission denied
find: ./private/etc/raddb/modules: Permission denied
find: ./private/etc/raddb/sites-available: Permission denied
find: ./private/etc/raddb/sites-enabled: Permission denied
find: ./private/etc/raddb/sql: Permission denied
find: ./private/tmp/launchd-158.ac7XMn: Permission denied
find: ./private/tmp/launchd-47725.RroMYY: Permission denied
find: ./private/tmp/launchd-49727.qQpnIz: Permission denied
find: ./private/var/agentx: Permission denied
find: ./private/var/at/tabs: Permission denied
find: ./private/var/at/tmp: Permission denied
find: ./private/var/audit: Permission denied
find: ./private/var/backups: Permission denied
find: ./private/var/db/ConfigurationProfiles/Setup: Permission denied
find: ./private/var/db/dhcpclient: Permission denied
find: ./private/var/db/dslocal/nodes/Default: Permission denied
find: ./private/var/db/geod: Permission denied
find: ./private/var/db/krb5kdc: Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.0: Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.200:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.202:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.212:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.502:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.88:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.89:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.92:
Permission denied
find: ./private/var/db/launchd.db/com.apple.launchd.peruser.97:
Permission denied
find: ./private/var/db/locationd: Permission denied
find: ./private/var/db/Spotlight: Permission denied
find: ./private/var/db/sudo: Permission denied
find: ./private/var/folders/r9/_xhmtynj7xj22mwt_dcfbxqr0000gp/C:
Permission denied
find: ./private/var/folders/r9/_xhmtynj7xj22mwt_dcfbxqr0000gp/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/0/com.apple.revisiond.temp:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/Cleanup
At Startup: Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n000003000000r/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000084000021/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000084000021/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n000008w000027/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n000008w000027/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000b000002r/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000b400002s/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000b400002s/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000bh00002w/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000bh00002w/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000c4000031/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000c4000031/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000s0000068/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000s0000068/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000s800006_/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000s800006_/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000sm00006d/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000sm00006d/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000th00006m/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000th00006m/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000tm00006n/T:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000vr00006y/C:
Permission denied
find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000vr00006y/T:
Permission denied
find: ./private/var/jabberd: Permission denied
find: ./private/var/lib/postfix: Permission denied
find: ./private/var/log/com.apple.launchd.peruser.0: Permission denied
find: ./private/var/log/com.apple.launchd.peruser.200: Permission denied
find: ./private/var/log/com.apple.launchd.peruser.202: Permission denied
find: ./private/var/log/com.apple.launchd.peruser.212: Permission denied
find: ./private/var/log/com.apple.launchd.peruser.502: Permission denied
find: ./private/var/log/com.apple.launchd.peruser.88: Permission denied
find: ./private/var/log/com.apple.launchd.peruser.89: Permission denied
find: ./private/var/log/com.apple.launchd.peruser.92: Permission denied
find: ./private/var/log/com.apple.launchd.peruser.97: Permission denied
find: ./private/var/log/krb5kdc: Permission denied
find: ./private/var/log/radius: Permission denied
find: ./private/var/pgsql_socket_alt: Permission denied
find: ./private/var/root: Permission denied
find: ./private/var/run/mds: Permission denied
find: ./private/var/spool/cups: Permission denied
find: ./private/var/spool/fax: Permission denied
find: ./private/var/spool/mqueue: Permission denied
find: ./private/var/spool/postfix/active: Permission denied
find: ./private/var/spool/postfix/bounce: Permission denied
find: ./private/var/spool/postfix/corrupt: Permission denied
find: ./private/var/spool/postfix/defer: Permission denied
find: ./private/var/spool/postfix/deferred: Permission denied
find: ./private/var/spool/postfix/flush: Permission denied
find: ./private/var/spool/postfix/hold: Permission denied
find: ./private/var/spool/postfix/incoming: Permission denied
find: ./private/var/spool/postfix/maildrop: Permission denied
find: ./private/var/spool/postfix/private: Permission denied
find: ./private/var/spool/postfix/public: Permission denied
find: ./private/var/spool/postfix/saved: Permission denied
find: ./private/var/spool/postfix/trace: Permission denied
find: ./private/var/tmp/launchd: Permission denied
find: ./System/Library/Caches/com.apple.coresymbolicationd: Permission denied
find: ./System/Library/DirectoryServices/DefaultLocalDB/Default:
Permission denied
find: ./System/Library/User Template: Permission denied
find: ./Users/igorkorot/Library/Saved Application
State/com.adobe.flashplayer.installmanager.savedState: Permission
denied
find: ./Users/igorkorot/Library/Saved Application
State/com.bitrock.appinstaller.savedState: Permission denied
find: ./usr/sbin/authserver: Permission denied
[/code]

It looks like I dont have a configuration file.
Where I should create one? This is on OSX 10.8.

Thank you.

>
> David J.
>
> p.s. Please remove the excess quoted material before hitting send.  At
> minimum anything after the last text that you write.

On 07/12/2018 05:28 PM, Igor Korot wrote:
> Hi,
>
>
> On Thu, Jul 12, 2018 at 12:16 PM, David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> On Thu, Jul 12, 2018 at 8:50 AM, Igor Korot <ikorot01@gmail.com> wrote:
>>>> No, see:
>>>>
>>>> https://www.postgresql.org/docs/10/static/sql-createtrigger.html
>>>>
>>>> AFTER trigger on views are STATEMENT level only.
>>> But I do have access to the STATEMENT right?
>>
>> Yes, except nothing in the system actually attempts to directly target
>> information_schema views with updates so there will never be a triggering
>> event.
>>
>> A normal trigger will not work - which is a large reason why event triggers
>> were implemented.
>>
>> All of the alternative ideas (which I think was just log file parsing)
>> you've decided are not viable for your need.  Thus you've seemingly
>> eliminated all viable options and you now need to make a business decision.
> [code]
> MyMac:/ igorkorot$ find . -name postgresql.conf
> find: ./.DocumentRevisions-V100: Permission denied
> find: ./.fseventsd: Permission denied
> find: ./.Spotlight-V100: Permission denied
> find: ./.Trashes: Permission denied
> find: ./dev/fd/3: Not a directory
> find: ./dev/fd/4: Not a directory
> find: ./Library/Application Support/Apple/ParentalControls/Users:
> Permission denied
> find: ./Library/Caches/com.apple.Spotlight/schema.501.plist: Permission denied
> find: ./Library/Caches/com.apple.Spotlight/schema.502.plist: Permission denied
> find: ./Library/Caches/com.apple.Spotlight/schema.89.plist: Permission denied
> find: ./Library/Caches/com.apple.Spotlight: Permission denied
> find: ./Library/PostgreSQL/9.1/data: Permission denied
> find: ./private/etc/cups/certs: Permission denied
> find: ./private/etc/raddb/certs: Permission denied
> find: ./private/etc/raddb/modules: Permission denied
> find: ./private/etc/raddb/sites-available: Permission denied
> find: ./private/etc/raddb/sites-enabled: Permission denied
> find: ./private/etc/raddb/sql: Permission denied
> find: ./private/tmp/launchd-158.ac7XMn: Permission denied
> find: ./private/tmp/launchd-47725.RroMYY: Permission denied
> find: ./private/tmp/launchd-49727.qQpnIz: Permission denied
> find: ./private/var/agentx: Permission denied
> find: ./private/var/at/tabs: Permission denied
> find: ./private/var/at/tmp: Permission denied
> find: ./private/var/audit: Permission denied
> find: ./private/var/backups: Permission denied
> find: ./private/var/db/ConfigurationProfiles/Setup: Permission denied
> find: ./private/var/db/dhcpclient: Permission denied
> find: ./private/var/db/dslocal/nodes/Default: Permission denied
> find: ./private/var/db/geod: Permission denied
> find: ./private/var/db/krb5kdc: Permission denied
> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.0: Permission denied
> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.200:
> Permission denied
> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.202:
> Permission denied
> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.212:
> Permission denied
> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.502:
> Permission denied
> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.88:
> Permission denied
> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.89:
> Permission denied
> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.92:
> Permission denied
> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.97:
> Permission denied
> find: ./private/var/db/locationd: Permission denied
> find: ./private/var/db/Spotlight: Permission denied
> find: ./private/var/db/sudo: Permission denied
> find: ./private/var/folders/r9/_xhmtynj7xj22mwt_dcfbxqr0000gp/C:
> Permission denied
> find: ./private/var/folders/r9/_xhmtynj7xj22mwt_dcfbxqr0000gp/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/0/com.apple.revisiond.temp:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/C:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/Cleanup
> At Startup: Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n000003000000r/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000084000021/C:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000084000021/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n000008w000027/C:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n000008w000027/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000b000002r/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000b400002s/C:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000b400002s/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000bh00002w/C:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000bh00002w/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000c4000031/C:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000c4000031/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000s0000068/C:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000s0000068/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000s800006_/C:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000s800006_/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000sm00006d/C:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000sm00006d/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000th00006m/C:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000th00006m/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000tm00006n/T:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000vr00006y/C:
> Permission denied
> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000vr00006y/T:
> Permission denied
> find: ./private/var/jabberd: Permission denied
> find: ./private/var/lib/postfix: Permission denied
> find: ./private/var/log/com.apple.launchd.peruser.0: Permission denied
> find: ./private/var/log/com.apple.launchd.peruser.200: Permission denied
> find: ./private/var/log/com.apple.launchd.peruser.202: Permission denied
> find: ./private/var/log/com.apple.launchd.peruser.212: Permission denied
> find: ./private/var/log/com.apple.launchd.peruser.502: Permission denied
> find: ./private/var/log/com.apple.launchd.peruser.88: Permission denied
> find: ./private/var/log/com.apple.launchd.peruser.89: Permission denied
> find: ./private/var/log/com.apple.launchd.peruser.92: Permission denied
> find: ./private/var/log/com.apple.launchd.peruser.97: Permission denied
> find: ./private/var/log/krb5kdc: Permission denied
> find: ./private/var/log/radius: Permission denied
> find: ./private/var/pgsql_socket_alt: Permission denied
> find: ./private/var/root: Permission denied
> find: ./private/var/run/mds: Permission denied
> find: ./private/var/spool/cups: Permission denied
> find: ./private/var/spool/fax: Permission denied
> find: ./private/var/spool/mqueue: Permission denied
> find: ./private/var/spool/postfix/active: Permission denied
> find: ./private/var/spool/postfix/bounce: Permission denied
> find: ./private/var/spool/postfix/corrupt: Permission denied
> find: ./private/var/spool/postfix/defer: Permission denied
> find: ./private/var/spool/postfix/deferred: Permission denied
> find: ./private/var/spool/postfix/flush: Permission denied
> find: ./private/var/spool/postfix/hold: Permission denied
> find: ./private/var/spool/postfix/incoming: Permission denied
> find: ./private/var/spool/postfix/maildrop: Permission denied
> find: ./private/var/spool/postfix/private: Permission denied
> find: ./private/var/spool/postfix/public: Permission denied
> find: ./private/var/spool/postfix/saved: Permission denied
> find: ./private/var/spool/postfix/trace: Permission denied
> find: ./private/var/tmp/launchd: Permission denied
> find: ./System/Library/Caches/com.apple.coresymbolicationd: Permission denied
> find: ./System/Library/DirectoryServices/DefaultLocalDB/Default:
> Permission denied
> find: ./System/Library/User Template: Permission denied
> find: ./Users/igorkorot/Library/Saved Application
> State/com.adobe.flashplayer.installmanager.savedState: Permission
> denied
> find: ./Users/igorkorot/Library/Saved Application
> State/com.bitrock.appinstaller.savedState: Permission denied
> find: ./usr/sbin/authserver: Permission denied
> [/code]
>
> It looks like I dont have a configuration file.
> Where I should create one? This is on OSX 10.8.
>
> Thank you.
>
>> David J.
>>
>> p.s. Please remove the excess quoted material before hitting send.  At
>> minimum anything after the last text that you write.
Can you try that again as root? Or with sudo?

Hi,


On Thu, Jul 12, 2018 at 7:45 PM, Rob Sargent <robjsargent@gmail.com> wrote:
>
>> [code]
>> MyMac:/ igorkorot$ find . -name postgresql.conf
>> find: ./.DocumentRevisions-V100: Permission denied
>> find: ./.fseventsd: Permission denied
>> find: ./.Spotlight-V100: Permission denied
>> find: ./.Trashes: Permission denied
>> find: ./dev/fd/3: Not a directory
>> find: ./dev/fd/4: Not a directory
>> find: ./Library/Application Support/Apple/ParentalControls/Users:
>> Permission denied
>> find: ./Library/Caches/com.apple.Spotlight/schema.501.plist: Permission
>> denied
>> find: ./Library/Caches/com.apple.Spotlight/schema.502.plist: Permission
>> denied
>> find: ./Library/Caches/com.apple.Spotlight/schema.89.plist: Permission
>> denied
>> find: ./Library/Caches/com.apple.Spotlight: Permission denied
>> find: ./Library/PostgreSQL/9.1/data: Permission denied
>> find: ./private/etc/cups/certs: Permission denied
>> find: ./private/etc/raddb/certs: Permission denied
>> find: ./private/etc/raddb/modules: Permission denied
>> find: ./private/etc/raddb/sites-available: Permission denied
>> find: ./private/etc/raddb/sites-enabled: Permission denied
>> find: ./private/etc/raddb/sql: Permission denied
>> find: ./private/tmp/launchd-158.ac7XMn: Permission denied
>> find: ./private/tmp/launchd-47725.RroMYY: Permission denied
>> find: ./private/tmp/launchd-49727.qQpnIz: Permission denied
>> find: ./private/var/agentx: Permission denied
>> find: ./private/var/at/tabs: Permission denied
>> find: ./private/var/at/tmp: Permission denied
>> find: ./private/var/audit: Permission denied
>> find: ./private/var/backups: Permission denied
>> find: ./private/var/db/ConfigurationProfiles/Setup: Permission denied
>> find: ./private/var/db/dhcpclient: Permission denied
>> find: ./private/var/db/dslocal/nodes/Default: Permission denied
>> find: ./private/var/db/geod: Permission denied
>> find: ./private/var/db/krb5kdc: Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.0: Permission
>> denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.200:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.202:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.212:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.502:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.88:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.89:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.92:
>> Permission denied
>> find: ./private/var/db/launchd.db/com.apple.launchd.peruser.97:
>> Permission denied
>> find: ./private/var/db/locationd: Permission denied
>> find: ./private/var/db/Spotlight: Permission denied
>> find: ./private/var/db/sudo: Permission denied
>> find: ./private/var/folders/r9/_xhmtynj7xj22mwt_dcfbxqr0000gp/C:
>> Permission denied
>> find: ./private/var/folders/r9/_xhmtynj7xj22mwt_dcfbxqr0000gp/T:
>> Permission denied
>> find:
>> ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/0/com.apple.revisiond.temp:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/Cleanup
>> At Startup: Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n000003000000r/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000084000021/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n0000084000021/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n000008w000027/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n000008w000027/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000b000002r/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000b400002s/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000b400002s/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000bh00002w/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000bh00002w/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000c4000031/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000c4000031/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000s0000068/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000s0000068/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000s800006_/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000s800006_/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000sm00006d/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000sm00006d/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000th00006m/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000th00006m/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000tm00006n/T:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000vr00006y/C:
>> Permission denied
>> find: ./private/var/folders/zz/zyxvpxvq6csfxvn_n00000vr00006y/T:
>> Permission denied
>> find: ./private/var/jabberd: Permission denied
>> find: ./private/var/lib/postfix: Permission denied
>> find: ./private/var/log/com.apple.launchd.peruser.0: Permission denied
>> find: ./private/var/log/com.apple.launchd.peruser.200: Permission denied
>> find: ./private/var/log/com.apple.launchd.peruser.202: Permission denied
>> find: ./private/var/log/com.apple.launchd.peruser.212: Permission denied
>> find: ./private/var/log/com.apple.launchd.peruser.502: Permission denied
>> find: ./private/var/log/com.apple.launchd.peruser.88: Permission denied
>> find: ./private/var/log/com.apple.launchd.peruser.89: Permission denied
>> find: ./private/var/log/com.apple.launchd.peruser.92: Permission denied
>> find: ./private/var/log/com.apple.launchd.peruser.97: Permission denied
>> find: ./private/var/log/krb5kdc: Permission denied
>> find: ./private/var/log/radius: Permission denied
>> find: ./private/var/pgsql_socket_alt: Permission denied
>> find: ./private/var/root: Permission denied
>> find: ./private/var/run/mds: Permission denied
>> find: ./private/var/spool/cups: Permission denied
>> find: ./private/var/spool/fax: Permission denied
>> find: ./private/var/spool/mqueue: Permission denied
>> find: ./private/var/spool/postfix/active: Permission denied
>> find: ./private/var/spool/postfix/bounce: Permission denied
>> find: ./private/var/spool/postfix/corrupt: Permission denied
>> find: ./private/var/spool/postfix/defer: Permission denied
>> find: ./private/var/spool/postfix/deferred: Permission denied
>> find: ./private/var/spool/postfix/flush: Permission denied
>> find: ./private/var/spool/postfix/hold: Permission denied
>> find: ./private/var/spool/postfix/incoming: Permission denied
>> find: ./private/var/spool/postfix/maildrop: Permission denied
>> find: ./private/var/spool/postfix/private: Permission denied
>> find: ./private/var/spool/postfix/public: Permission denied
>> find: ./private/var/spool/postfix/saved: Permission denied
>> find: ./private/var/spool/postfix/trace: Permission denied
>> find: ./private/var/tmp/launchd: Permission denied
>> find: ./System/Library/Caches/com.apple.coresymbolicationd: Permission
>> denied
>> find: ./System/Library/DirectoryServices/DefaultLocalDB/Default:
>> Permission denied
>> find: ./System/Library/User Template: Permission denied
>> find: ./Users/igorkorot/Library/Saved Application
>> State/com.adobe.flashplayer.installmanager.savedState: Permission
>> denied
>> find: ./Users/igorkorot/Library/Saved Application
>> State/com.bitrock.appinstaller.savedState: Permission denied
>> find: ./usr/sbin/authserver: Permission denied
>> [/code]
>>
>> It looks like I dont have a configuration file.
>> Where I should create one? This is on OSX 10.8.
>>
>> Thank you.
>>

> Can you try that again as root? Or with sudo?

Yes, it did show up.
But now it looks like I have 2 places to set the logging:

log_statement_stats which I presume is just for statistics and
log_statement which I presume I should change.

Am I right?
Also, I had to turn on logging - use csvlog, and enable the directory
and filename.
I presume that this will not require the restart of the server, correct?

Thank you.

>
>

On 07/12/2018 06:19 PM, Igor Korot wrote:
> Hi,
> 
> 
> On Thu, Jul 12, 2018 at 7:45 PM, Rob Sargent <robjsargent@gmail.com> wrote:

> 
>> Can you try that again as root? Or with sudo?
> 
> Yes, it did show up.
> But now it looks like I have 2 places to set the logging:
> 
> log_statement_stats which I presume is just for statistics and
> log_statement which I presume I should change.

All is answered here:

https://www.postgresql.org/docs/10/static/runtime-config-logging.html

> 
> Am I right?
> Also, I had to turn on logging - use csvlog, and enable the directory
> and filename.
> I presume that this will not require the restart of the server, correct?
> 
> Thank you.
> 
>>
>>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi, Melvin,

On Tue, Jul 3, 2018 at 2:00 PM Melvin Davidson <melvin6925@gmail.com> wrote:
>
>
>
> >Unfortunately I'm stuck with 9.1.
>
> Have you thought about just setting   log_statement = 'ddl' in postgresql.conf
> and just greping the log for CREATE and ALTER?

Is there a way to query a server for a place where the log file is?
Or I will have to hard-code it?

Thank you.

>

Hi,

On Mon, Sep 17, 2018 at 9:19 PM Christophe Pettus <xof@thebuild.com> wrote:
>
>
> > On Sep 17, 2018, at 07:09, Igor Korot <ikorot01@gmail.com> wrote:
> >
> > Is there a way to query a server for a place where the log file is?
>
> SHOW log_directory;
>
> It's either relative to the PGDATA directory, or an absolute path.

And I presume it depends on the string I put in the log file?

Thank you.

>
> --
> -- Christophe Pettus
>    xof@thebuild.com
>

> On Sep 17, 2018, at 07:09, Igor Korot <ikorot01@gmail.com> wrote:
> 
> Is there a way to query a server for a place where the log file is?

SHOW log_directory;

It's either relative to the PGDATA directory, or an absolute path.

--
-- Christophe Pettus
   xof@thebuild.com

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

And then in my C++ code I will continuously query this temporary table.

Or there is a better alternative on the algorithm?

Thank you.

>> >
> > Thank you.
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

Hi, Adrian,

On Mon, Dec 3, 2018 at 5:15 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 12/3/18 3:00 PM, Igor Korot wrote:
> > Hi, Adrian,
> >
> > On Mon, Dec 3, 2018 at 4:10 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> >>
> >> On 12/3/18 1:53 PM, Igor Korot wrote:
> >>> Hi, Adrian,
> >>
> >>>> Why? Just create the trigger once in a script. Event triggers are an
> >>>> attribute of the database and stay with it until they are dropped. If
> >>>> you want to turn then on and off use the ALTER  EVENT TRIGGER
> >>>> ENABLE/DISABLE. If you insist on recreating them on each connection then:
> >>>>
> >>>> https://www.postgresql.org/docs/10/sql-dropeventtrigger.html
> >>>> DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
> >>>> CREATE EVENT TRIGGER ...
> >>>
> >>> I was hoping to create a software which will be just "install-and-use".
> >>> No additional script running or database changes is required.
> >>
> >> After I hit reply my subconscious kicked in and pointed out something:)
> >>
> >> If there are no database changes why do you need to track schema changes?
> >
> > That was a bad word selection. ;-)
> >
> > What I mean to say was "no schema changes/server changes that comes
> > independently
> > of the program install". Or something to that extent.
>
> Which circles back around to the same question:
>
> If there are to be no schema changes after the install why track them on
> subsequent connections?
>
> Or maybe an explanation of what you are trying to achieve would make
> things clearer?

Ok, it probably will make things clearer.
So I install my program perform some queries and exit.
At the same time if the user will create or delete a table the program should
pick up those changes and act accordingly.

I was hoping to do the watching initialization dynamically, but it looks as it
is more pain and I can probably try to execute the script during the
installation.

So basically I will create the function in C, compile it and then
during the program installation
create a trigger with that function.
Then in my program I will execute "LISTEN" command and act accordingly.

Am I right?

And executing LISTEN will also work for ODBC connection, right?

Thank you.

>
>
>
> >
> > Sorry, ESL person here.
> >
> > Thank you.
> >
> >>
> >>>
> >>> But I will probably create it on every connection and delete on the
> >>> disconnect (see above).
> >>>
> >>
> >> --
> >> Adrian Klaver
> >> adrian.klaver@aklaver.com
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

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

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

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

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?

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

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

On Mon, Dec 3, 2018 at 11:29 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> 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.

OK, so how do I do it?
There is no "CREATE EVENT TRIGGER IF NOT EXIST".

As I say - I'm trying to make it work from both ODBC and libpq
connection (one at a time).

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

Also - what about this?

And why did you say that saving the SQL commend is not a good idea.

What's better?

Thank you.

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

On 12/3/18 9:53 AM, Igor Korot wrote:
>> 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.
> 
> OK, so how do I do it?
> There is no "CREATE EVENT TRIGGER IF NOT EXIST".
> 
> As I say - I'm trying to make it work from both ODBC and libpq
> connection (one at a time)

Why? Just create the trigger once in a script. Event triggers are an 
attribute of the database and stay with it until they are dropped. If 
you want to turn then on and off use the ALTER  EVENT TRIGGER 
ENABLE/DISABLE. If you insist on recreating them on each connection then:

https://www.postgresql.org/docs/10/sql-dropeventtrigger.html
DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
CREATE EVENT TRIGGER ...

> 
>>
>>>
>>> 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?
> 
> Also - what about this?
> 
> And why did you say that saving the SQL commend is not a good idea.
> 
> What's better?

See above.

> 
> Thank you.
> 
>>>
>>>>
>>>>>
>>>>> 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


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, Dec 3, 2018 at 10:59 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> > As I say - I'm trying to make it work from both ODBC and libpq
> > connection (one at a time)

IIUC what is being proposed is:

Once, on the server, the DBA issues:
CREATE EVENT TRIGGER (the function it runs issues NOTIFY
'channel-name' - even if no one is listening at any given point in
time)

Upon connection your client application executes:
LISTEN 'channel-name'
And then handles notification events from the channel normally.

The client itself never issues CREATE EVENT - nor, frankly, should it.
It shouldn't have sufficient permissions to do something like that.

David J.

Hi, Adrian,

On Mon, Dec 3, 2018 at 11:59 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 12/3/18 9:53 AM, Igor Korot wrote:
> >> 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.
> >
> > OK, so how do I do it?
> > There is no "CREATE EVENT TRIGGER IF NOT EXIST".
> >
> > As I say - I'm trying to make it work from both ODBC and libpq
> > connection (one at a time)
>
> Why? Just create the trigger once in a script. Event triggers are an
> attribute of the database and stay with it until they are dropped. If
> you want to turn then on and off use the ALTER  EVENT TRIGGER
> ENABLE/DISABLE. If you insist on recreating them on each connection then:
>
> https://www.postgresql.org/docs/10/sql-dropeventtrigger.html
> DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
> CREATE EVENT TRIGGER ...

I was hoping to create a software which will be just "install-and-use".
No additional script running or database changes is required.

But I will probably create it on every connection and delete on the
disconnect (see above).

>
> >
> >>
> >>>
> >>> 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?
> >
> > Also - what about this?
> >
> > And why did you say that saving the SQL commend is not a good idea.
> >
> > What's better?
>
> See above.
>
> >
> > Thank you.
> >
> >>>
> >>>>
> >>>>>
> >>>>> 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
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

On 2018-Dec-03, Igor Korot wrote:

> But I will probably create it on every connection and delete on the
> disconnect (see above).

This sounds certain to create a mess eventually, when a connection drops
unexpectedly. (Also, what will happens to connections that run
concurrently with yours?)

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 12/3/18 1:53 PM, Igor Korot wrote:
> Hi, Adrian,
> 

>> Why? Just create the trigger once in a script. Event triggers are an
>> attribute of the database and stay with it until they are dropped. If
>> you want to turn then on and off use the ALTER  EVENT TRIGGER
>> ENABLE/DISABLE. If you insist on recreating them on each connection then:
>>
>> https://www.postgresql.org/docs/10/sql-dropeventtrigger.html
>> DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
>> CREATE EVENT TRIGGER ...
> 
> I was hoping to create a software which will be just "install-and-use".
> No additional script running or database changes is required.

Out of curiosity more then anything else:

The database schema this is running against never changes?

The triggers cannot be included in the initial database setup?


> 
> But I will probably create it on every connection and delete on the
> disconnect (see above).
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 12/3/18 1:53 PM, Igor Korot wrote:
> Hi, Adrian,

>> Why? Just create the trigger once in a script. Event triggers are an
>> attribute of the database and stay with it until they are dropped. If
>> you want to turn then on and off use the ALTER  EVENT TRIGGER
>> ENABLE/DISABLE. If you insist on recreating them on each connection then:
>>
>> https://www.postgresql.org/docs/10/sql-dropeventtrigger.html
>> DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
>> CREATE EVENT TRIGGER ...
> 
> I was hoping to create a software which will be just "install-and-use".
> No additional script running or database changes is required.

After I hit reply my subconscious kicked in and pointed out something:)

If there are no database changes why do you need to track schema changes?

> 
> But I will probably create it on every connection and delete on the
> disconnect (see above).
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi, Adrian,

On Mon, Dec 3, 2018 at 4:10 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 12/3/18 1:53 PM, Igor Korot wrote:
> > Hi, Adrian,
>
> >> Why? Just create the trigger once in a script. Event triggers are an
> >> attribute of the database and stay with it until they are dropped. If
> >> you want to turn then on and off use the ALTER  EVENT TRIGGER
> >> ENABLE/DISABLE. If you insist on recreating them on each connection then:
> >>
> >> https://www.postgresql.org/docs/10/sql-dropeventtrigger.html
> >> DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
> >> CREATE EVENT TRIGGER ...
> >
> > I was hoping to create a software which will be just "install-and-use".
> > No additional script running or database changes is required.
>
> After I hit reply my subconscious kicked in and pointed out something:)
>
> If there are no database changes why do you need to track schema changes?

That was a bad word selection. ;-)

What I mean to say was "no schema changes/server changes that comes
independently
of the program install". Or something to that extent.

Sorry, ESL person here.

Thank you.

>
> >
> > But I will probably create it on every connection and delete on the
> > disconnect (see above).
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

On 12/3/18 3:00 PM, Igor Korot wrote:
> Hi, Adrian,
> 
> On Mon, Dec 3, 2018 at 4:10 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> On 12/3/18 1:53 PM, Igor Korot wrote:
>>> Hi, Adrian,
>>
>>>> Why? Just create the trigger once in a script. Event triggers are an
>>>> attribute of the database and stay with it until they are dropped. If
>>>> you want to turn then on and off use the ALTER  EVENT TRIGGER
>>>> ENABLE/DISABLE. If you insist on recreating them on each connection then:
>>>>
>>>> https://www.postgresql.org/docs/10/sql-dropeventtrigger.html
>>>> DROP EVENT TRIGGER [ IF EXISTS ] name [ CASCADE | RESTRICT ]
>>>> CREATE EVENT TRIGGER ...
>>>
>>> I was hoping to create a software which will be just "install-and-use".
>>> No additional script running or database changes is required.
>>
>> After I hit reply my subconscious kicked in and pointed out something:)
>>
>> If there are no database changes why do you need to track schema changes?
> 
> That was a bad word selection. ;-)
> 
> What I mean to say was "no schema changes/server changes that comes
> independently
> of the program install". Or something to that extent.

Which circles back around to the same question:

If there are to be no schema changes after the install why track them on 
subsequent connections?

Or maybe an explanation of what you are trying to achieve would make 
things clearer?



> 
> Sorry, ESL person here.
> 
> Thank you.
> 
>>
>>>
>>> But I will probably create it on every connection and delete on the
>>> disconnect (see above).
>>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 12/3/18 5:29 AM, Igor Korot wrote:
> Hi, Adrian,
> 
> On Mon, Dec 3, 2018 at 5:15 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> On 12/3/18 3:00 PM, Igor Korot wrote:
>>> Hi, Adrian,
>>>

>>>
>>> What I mean to say was "no schema changes/server changes that comes
>>> independently
>>> of the program install". Or something to that extent.
>>
>> Which circles back around to the same question:
>>
>> If there are to be no schema changes after the install why track them on
>> subsequent connections?
>>
>> Or maybe an explanation of what you are trying to achieve would make
>> things clearer?
> 
> Ok, it probably will make things clearer.
> So I install my program perform some queries and exit.
> At the same time if the user will create or delete a table the program should
> pick up those changes and act accordingly.

So do you want the user to change the schema?

Or could you use permissions to stop that?

I am just seeing a difficult problem trying to divine user intent from 
schema changes.

> 
> I was hoping to do the watching initialization dynamically, but it looks as it
> is more pain and I can probably try to execute the script during the
> installation.
> 
> So basically I will create the function in C, compile it and then
> during the program installation
> create a trigger with that function.
> Then in my program I will execute "LISTEN" command and act accordingly.
> 
> Am I right?

Not sure I have never tried LISTEN on event trigger output.

> 
> And executing LISTEN will also work for ODBC connection, right?

Again not something I am familiar with. Someone else on this list may be 
able to answer that. Or there is:

https://www.postgresql.org/list/pgsql-odbc/

> 
> Thank you.
> 
>>
>>
>>
>>>
>>> Sorry, ESL person here.
>>>
>>> Thank you.
>>>
>>>>
>>>>>
>>>>> But I will probably create it on every connection and delete on the
>>>>> disconnect (see above).
>>>>>
>>>>
>>>> --
>>>> Adrian Klaver
>>>> adrian.klaver@aklaver.com
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

-----Original Message-----
From: Igor Korot [mailto:ikorot01@gmail.com] 
Sent: Monday, December 03, 2018 8:29 AM
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: How to watch for schema changes

...................

And executing LISTEN will also work for ODBC connection, right?

Thank you.

_______________________________________________________________________________________________

It's been years since we dealt with this problem, so the details are fuzzy.

All applications in the package we develop connect to PG using ODBC, but one app that's using LISTEN is connecting to
PGthrough native interface libpq.dll, ODBC didn't work for that purpose, at least at the time.
 

Regards,
Igor Neyman

Igor,

On Tue, Dec 4, 2018 at 8:20 AM Igor Neyman <ineyman@perceptron.com> wrote:
>
>
> -----Original Message-----
> From: Igor Korot [mailto:ikorot01@gmail.com]
> Sent: Monday, December 03, 2018 8:29 AM
> To: Adrian Klaver <adrian.klaver@aklaver.com>
> Cc: pgsql-general <pgsql-general@postgresql.org>
> Subject: Re: How to watch for schema changes
>
> ...................
>
> And executing LISTEN will also work for ODBC connection, right?
>
> Thank you.
>
> _______________________________________________________________________________________________
>
> It's been years since we dealt with this problem, so the details are fuzzy.
>
> All applications in the package we develop connect to PG using ODBC, but one app that's using LISTEN is connecting to
PGthrough native interface libpq.dll, ODBC didn't work for that purpose, at least at the time.
 

I will try it and report back.
Out of curiosity - when was the last time you tried?

Thank you.

>
> Regards,
> Igor Neyman

-----Original Message-----
From: Igor Korot [mailto:ikorot01@gmail.com] 
Sent: Tuesday, December 04, 2018 11:07 AM
To: Igor Neyman <ineyman@perceptron.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general <pgsql-general@postgresql.org>
Subject: Re: How to watch for schema changes

Igor,

On Tue, Dec 4, 2018 at 8:20 AM Igor Neyman <ineyman@perceptron.com> wrote:
>
>
> -----Original Message-----
> From: Igor Korot [mailto:ikorot01@gmail.com]
> Sent: Monday, December 03, 2018 8:29 AM
> To: Adrian Klaver <adrian.klaver@aklaver.com>
> Cc: pgsql-general <pgsql-general@postgresql.org>
> Subject: Re: How to watch for schema changes
>
> ...................
>
> And executing LISTEN will also work for ODBC connection, right?
>
> Thank you.
>
> _______________________________________________________________________________________________
>
> It's been years since we dealt with this problem, so the details are fuzzy.
>
> All applications in the package we develop connect to PG using ODBC, but one app that's using LISTEN is connecting to
PGthrough native interface libpq.dll, ODBC didn't work for that purpose, at least at the time.
 

I will try it and report back.
Out of curiosity - when was the last time you tried?

Thank you.
_____________________________________________________________________________________________

PG release 8.4.