RE: How to watch for schema changes

-Original Message- From: Igor Korot [mailto:ikoro...@gmail.com] Sent: Tuesday, December 04, 2018 11:07 AM To: Igor Neyman Cc: Adrian Klaver ; pgsql-general Subject: Re: How to watch for schema changes Igor, On Tue, Dec 4, 2018 at 8:20 AM Igor Neyman wrote: > > >

Re: How to watch for schema changes

Igor, On Tue, Dec 4, 2018 at 8:20 AM Igor Neyman wrote: > > > -Original Message- > From: Igor Korot [mailto:ikoro...@gmail.com] > Sent: Monday, December 03, 2018 8:29 AM > To: Adrian Klaver > Cc: pgsql-general > Subject: Re: How to

RE: How to watch for schema changes

-Original Message- From: Igor Korot [mailto:ikoro...@gmail.com] Sent: Monday, December 03, 2018 8:29 AM To: Adrian Klaver Cc: pgsql-general Subject: Re: How to watch for schema changes ... And executing LISTEN will also work for ODBC connection, right? Thank you

Re: How to watch for schema changes

On 12/3/18 5:29 AM, Igor Korot wrote: Hi, Adrian, On Mon, Dec 3, 2018 at 5:15 PM Adrian Klaver 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

Re: How to watch for schema changes

Hi, Adrian, On Mon, Dec 3, 2018 at 5:15 PM Adrian Klaver wrote: > > On 12/3/18 3:00 PM, Igor Korot wrote: > > Hi, Adrian, > > > > On Mon, Dec 3, 2018 at 4:10 PM Adrian Klaver > > wrote: > >> > >> On 12/3/18 1:53 PM, Igor Korot wrote: > >>> Hi, Adrian, > >> > Why? Just create the trigger

Re: How to watch for schema changes

On 12/3/18 3:00 PM, Igor Korot wrote: Hi, Adrian, On Mon, Dec 3, 2018 at 4:10 PM Adrian Klaver 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.

Re: How to watch for schema changes

Hi, Adrian, On Mon, Dec 3, 2018 at 4:10 PM Adrian Klaver 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

Re: How to watch for schema changes

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

Re: How to watch for schema changes

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

Re: How to watch for schema changes

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

Re: How to watch for schema changes

Hi, Adrian, On Mon, Dec 3, 2018 at 11:59 AM Adrian Klaver 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

Re: How to watch for schema changes

On Mon, Dec 3, 2018 at 10:59 AM Adrian Klaver 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' -

Re: How to watch for schema changes

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

Re: How to watch for schema changes

On Mon, Dec 3, 2018 at 11:29 AM Adrian Klaver wrote: > > On 12/3/18 8:16 AM, Igor Korot wrote: > > Hi, Adrian, > > > > On Mon, Dec 3, 2018 at 9:17 AM Adrian Klaver > > wrote: > >> > >> On 12/2/18 5:24 AM, Igor Korot wrote: > >>> Hi, Adrian, > >>> Sorry for the delay to come back to this. I was

Re: How to watch for schema changes

On 12/3/18 8:16 AM, Igor Korot wrote: Hi, Adrian, On Mon, Dec 3, 2018 at 9:17 AM Adrian Klaver 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 wrote: On

Re: How to watch for schema changes

Hi, Adrian, On Mon, Dec 3, 2018 at 9:17 AM Adrian Klaver 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 > > wrote: > >> > >> On 07/03/2018 10:21

Re: How to watch for schema changes

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

Re: How to watch for schema changes

Hi, On Mon, Sep 17, 2018 at 9:19 PM Christophe Pettus wrote: > > > > On Sep 17, 2018, at 07:09, Igor Korot 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

Re: How to watch for schema changes

> On Sep 17, 2018, at 07:09, Igor Korot 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 x...@thebuild.com

Re: How to watch for schema changes

Hi, Melvin, On Tue, Jul 3, 2018 at 2:00 PM Melvin Davidson 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

Re: How to watch for schema changes

On 07/12/2018 06:19 PM, Igor Korot wrote: Hi, On Thu, Jul 12, 2018 at 7:45 PM, Rob Sargent 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

Re: How to watch for schema changes

Hi, On Thu, Jul 12, 2018 at 7:45 PM, Rob Sargent 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

Re: How to watch for schema changes

On 07/12/2018 05:28 PM, Igor Korot wrote: Hi, On Thu, Jul 12, 2018 at 12:16 PM, David G. Johnston wrote: On Thu, Jul 12, 2018 at 8:50 AM, Igor Korot wrote: No, see: https://www.postgresql.org/docs/10/static/sql-createtrigger.html AFTER trigger on views are STATEMENT level only. But I

Re: How to watch for schema changes

Hi, On Thu, Jul 12, 2018 at 12:16 PM, David G. Johnston wrote: > On Thu, Jul 12, 2018 at 8:50 AM, Igor Korot 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

Re: How to watch for schema changes

On 07/12/2018 09:16 AM, David G. Johnston wrote: On Thu, Jul 12, 2018 at 8:50 AM, Igor Korot >wrote: > No, see: > > https://www.postgresql.org/docs/10/static/sql-createtrigger.html

Re: How to watch for schema changes

On Thu, Jul 12, 2018 at 8:50 AM, Igor Korot 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

Re: How to watch for schema changes

Hi, Adrian, On Wed, Jul 11, 2018 at 11:12 PM, Adrian Klaver wrote: > On 07/11/2018 08:46 PM, Igor Korot wrote: >> >> Hi, guys, >> >> >> On Mon, Jul 9, 2018 at 5:38 PM, Adrian Klaver >> wrote: >>> >>> On 07/09/2018 01:49 PM, Igor Korot wrote: Hi, Adrian On Tue, Jul 3,

Re: How to watch for schema changes

2018-07-12 6:12 GMT+02:00 Adrian Klaver : > On 07/11/2018 08:46 PM, Igor Korot wrote: > >> Hi, guys, >> >> >> On Mon, Jul 9, 2018 at 5:38 PM, Adrian Klaver >> wrote: >> >>> On 07/09/2018 01:49 PM, Igor Korot wrote: >>> Hi, Adrian On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver

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 wrote: On 07/09/2018 01:49 PM, Igor Korot wrote: Hi, Adrian On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver wrote: On 07/03/2018 11:15 AM, Igor Korot wrote: Adrian, On Tue, Jul 3, 2018

Re: How to watch for schema changes

Hi, guys, On Mon, Jul 9, 2018 at 5:38 PM, Adrian Klaver wrote: > On 07/09/2018 01:49 PM, Igor Korot wrote: >> >> Hi, Adrian >> >> On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver >> wrote: >>> >>> On 07/03/2018 11:15 AM, Igor Korot wrote: Adrian, On Tue, Jul 3, 2018 at

Re: How to watch for schema changes

On Mon, Jul 9, 2018 at 1:49 PM, Igor Korot wrote: > Just a thought... > Is it possible to create a trigger for a system table? > Not sure, and doesn't seem documented either way, but seems easy enough to try on a test cluster... ​[...]​ > Successful "CREATE TABLE..." statement creates a row

Re: How to watch for schema changes

Hi, Adrian On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver wrote: > On 07/03/2018 11:15 AM, Igor Korot wrote: >> >> Adrian, >> >> On Tue, Jul 3, 2018 at 12:32 PM, Adrian Klaver >> wrote: >>> >>> On 07/03/2018 10:21 AM, Igor Korot wrote: Hi, ALL, Is there any trigger or some

Re: How to watch for schema changes

On Thu, Jul 5, 2018 at 10:07 AM, Igor Korot wrote: > On Thu, Jul 5, 2018 at 11:49 AM, David G. Johnston > wrote: > > > 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"

Re: How to watch for schema changes

On 07/05/2018 08:40 AM, Igor Korot wrote: Hi, David, On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston wrote: On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot wrote: I presume threre is a query which check for the function/trigger existence? Something like: IF NOT EXIST(SELECT * FROM ) CREATE

Re: How to watch for schema changes

On Thu, Jul 5, 2018 at 8:40 AM, Igor Korot wrote: > Hi, David, > > On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston > wrote: > > On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot wrote: > >> > >> > >> I presume threre is a query which check for the function/trigger > >> existence? Something like: >

Re: How to watch for schema changes

>As I said I'm writing the client application with libpq/ODBC. How will I get >the results? Igor, You DO NOT need libpq/ODBC . Just use the count option of grep (-c). If it is greater than zero, then send a mail to yourself and/or attach the log for review. -- *Melvin Davidson* *Maj. Database &

Re: How to watch for schema changes

Hi, David, On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston wrote: > On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot 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; > >

Re: How to watch for schema changes

Hi, Melvin, On Tue, Jul 3, 2018 at 6:48 PM, Melvin Davidson 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

Re: How to watch for schema changes

>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? -- *Melvin Davidson* *Maj. Database & Exploration Specialist* *Universe Exploration Command – UXC* Employment by invitation only!

Re: How to watch for schema changes

Hi Melvin On Tue, Jul 3, 2018, 2:00 PM Melvin Davidson 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? > That going to be not that simple. I'm writing a

Re: How to watch for schema changes

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

Re: How to watch for schema changes

On 07/03/2018 11:41 AM, Igor Korot wrote: Adrian, On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver 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

Re: How to watch for schema changes

On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot 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

Re: How to watch for schema changes

Adrian, On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver wrote: > On 07/03/2018 11:15 AM, Igor Korot wrote: >> >> Adrian, >> >> On Tue, Jul 3, 2018 at 12:32 PM, Adrian Klaver >> wrote: >>> >>> On 07/03/2018 10:21 AM, Igor Korot wrote: Hi, ALL, Is there any trigger or some other

Re: How to watch for schema changes

On Tue, Jul 3, 2018 at 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? > ​You just asked this question two

Re: How to watch for schema changes

On 07/03/2018 11:15 AM, Igor Korot wrote: Adrian, On Tue, Jul 3, 2018 at 12:32 PM, Adrian Klaver 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

Re: How to watch for schema changes

Adrian, On Tue, Jul 3, 2018 at 12:32 PM, Adrian Klaver 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

Re: How to watch for schema changes

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?