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?

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.

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

On Tue, Jul 3, 2018 at 10:21 AM, Igor Korot <ikorot01@gmail.com> 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 weeks ago...why are you starting a new
thread instead of continuing that one?

​David J.

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.

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.

David J.

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

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?

Hi Melvin

On Tue, Jul 3, 2018, 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?

That going to be not that simple.
I'm writing a client in C++ with libpq. So I will have to do a lot of
polling .

Thank you.

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!

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.

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.

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 & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

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.

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.

On Thu, Jul 5, 2018 at 10:07 AM, Igor Korot <ikorot01@gmail.com> wrote:

On Thu, Jul 5, 2018 at 11:49 AM, David G. Johnston
<david.g.johnston@gmail.com> 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" 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?

If you care about performance here I'd suggest you measure it. The absence
of catalog bloat is the more meaningful benefit IMO.

If the function doesn't exist CREATE FUNCTION likely wins hands-down. If
the function name/signature does exist you are starting to compare apples
and oranges.

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.

On Mon, Jul 9, 2018 at 1:49 PM, Igor Korot <ikorot01@gmail.com> 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 inside the
information_schema.tables

​Given that information_schema.tables is a view, not a table, it doesn't
make a valid hook point for the trigger regardless of the previous point.

David J.