How to watch for schema changes

classic Classic list List threaded Threaded
52 messages Options
123
Reply | Threaded
Open this post in threaded view
|

How to watch for schema changes

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

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Adrian Klaver-4
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Igor Korot
Adrian,

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

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Adrian Klaver-4
On 07/03/2018 11:15 AM, Igor Korot wrote:

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


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

David G Johnston
In reply to this post by Igor Korot
On Tue, Jul 3, 2018 at 10:21 AM, Igor Korot <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Igor Korot
In reply to this post by Adrian Klaver-4
Adrian,

On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver <[hidden email]> wrote:

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

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

David G Johnston
On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Adrian Klaver-4
In reply to this post by Igor Korot
On 07/03/2018 11:41 AM, Igor Korot wrote:
> Adrian,
>
> On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver <[hidden email]> 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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Melvin Davidson-5


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

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Igor Korot
Hi Melvin



On Tue, Jul 3, 2018, 2:00 PM Melvin Davidson <[hidden email]> 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.


Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Melvin Davidson-5

>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!
Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Igor Korot
Hi, Melvin,

On Tue, Jul 3, 2018 at 6:48 PM, Melvin Davidson <[hidden email]> 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!

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Igor Korot
In reply to this post by David G Johnston
Hi, David,

On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston
<[hidden email]> wrote:

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

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Melvin Davidson-5
>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!
Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

David G Johnston
In reply to this post by Igor Korot
On Thu, Jul 5, 2018 at 8:40 AM, Igor Korot <[hidden email]> wrote:
Hi, David,

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

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Adrian Klaver-4
In reply to this post by Igor Korot
On 07/05/2018 08:40 AM, Igor Korot wrote:

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

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Igor Korot
In reply to this post by David G Johnston
David,

On Thu, Jul 5, 2018 at 11:49 AM, David G. Johnston
<[hidden email]> wrote:

> On Thu, Jul 5, 2018 at 8:40 AM, Igor Korot <[hidden email]> wrote:
>>
>> Hi, David,
>>
>> On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston
>> <[hidden email]> wrote:
>> > On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot <[hidden email]> 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.
>

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

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

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

Igor Korot
In reply to this post by Adrian Klaver-4
Hi, Adrian

On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver <[hidden email]> wrote:

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

Reply | Threaded
Open this post in threaded view
|

Re: How to watch for schema changes

David G Johnston
On Mon, Jul 9, 2018 at 1:49 PM, Igor Korot <[hidden email]> 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.

123