Re: How to watch for schema changes

On 07/11/2018 08:46 PM, Igor Korot wrote:

Hi, guys,


On Mon, Jul 9, 2018 at 5:38 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 07/09/2018 01:49 PM, Igor Korot wrote:

Hi, Adrian

On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 07/03/2018 11:15 AM, Igor Korot wrote:

Adrian,

On Tue, Jul 3, 2018 at 12:32 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 07/03/2018 10:21 AM, Igor Korot wrote:

Hi, ALL,
Is there any trigger or some other means I can do on the server
which will watch for CREATE/ALTER/DROP TABLE command and after
successful
execution of those will issue a NOTIFY statement?

https://www.postgresql.org/docs/10/static/event-triggers.html

According to the documentation the lowest version it supports is 9.3.
Anything prior to that?

I'm working with OX 10.8 and it has 9.1 installed.

9.1 went EOL almost two years ago. The oldest supported version is 9.3,
though it will go EOL this September:

https://www.postgresql.org/support/versioning/

Are you forced to work with 9.1 or can you use something from here:

https://www.postgresql.org/download/macosx/

to get a newer version? FYI that will be a major upgrade so will require
a
dump/restore or use of pg_upgrade.

Just a thought...
Is it possible to create a trigger for a system table? Or this
operation is restricted

Easy enough to test. As postgres super user:

test_(postgres)# create trigger info_test before insert on pg_class execute
procedure ts_update();

ERROR:  permission denied: "pg_class" is a system catalog

But

draft=# CREATE TRIGGER info_test AFTER INSERT ON
information_schema.tables EXECUTE PROCEDURE test();
ERROR:  function test() does not exist

So it looks like this should be possible?

No, see:

https://www.postgresql.org/docs/10/static/sql-createtrigger.html

AFTER trigger on views are STATEMENT level only.

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

"NEW

    Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is unassigned in statement-level triggers and for DELETE operations.
OLD

    Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is unassigned in statement-level triggers and for INSERT operations.
"

So you won't know what was INSERTed in row.

Thank you.

for when the server is actually being set-up?

Successful "CREATE TABLE..." statement creates a row inside the
information_schema.tables
so if I can create a trigger after this record is inserted or deleted
that should be fine.

Thank you.

And a second question - how do I work with it?
I presume that function will have to be compiled in its own module
(either dll, so or dylib).
But then from the libpq interface how do I call it?

It can use functions written in PL languages. See below:

https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html

for an example written in plpgsql.

Thank you.

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com