Postgres Pro
Downloads
Home   >   mailing lists

Re: Way to quickly detect if database tables/columns/etc. were modified? - Mailing list pgsql-general

From Melvin Davidson
Subject Re: Way to quickly detect if database tables/columns/etc. were modified?
Date
Msg-id CANu8Fiz0rBw3qs+isG+6WVRcQcQNbmJm3T+8ccD8FVAGF3EQ5A@mail.gmail.com
Whole thread Raw
In response to Re: Way to quickly detect if database tables/columns/etc. were modified?  (Evan Martin <postgresql2@realityexists.net>)
List pgsql-general
Tree view


On Mon, Oct 31, 2016 at 4:41 PM, Evan Martin <postgresql2@realityexists.net> wrote:
On 31/10/2016 8:26 PM, Melvin Davidson wrote:
I have tried using an event trigger to detect table creation (ie: tg_event_audit_all ) however, that does not parse the schema_name and objid
as does pg_event_trigger_dropped_objects(), so I am not sure that is a practical way to audit.

 Event triggers seem like the most promising suggestion so far (given that I only really need to know that something has changed, not necessarily what).

Still, I was hoping for a solution that doesn't rely on modifying the database at all, i.e. something built into Postgres, but it's pretty clear from everyone's answers nothing like this exists. (I wasn't looking for a creation date, exactly, because I'd want to know when something was modified, too).

Evan,

I did a little digging. Note that PostgreSQL 9.5 iand above s required for this solution, but it should provide most of what you need.
If you go to http://stackoverflow.com/questions/23488228/how-to-get-sql-text-from-postgres-event-trigger/35616026
and scroll toward the bottom, you will see an example of how to trap object mods.
Additional info as to what else can be captured is detailed in https://www.postgresql.org/docs/9.5/static/functions-event-triggers.html
You can also use now() or transaction_timestamp(), etc to record the date/time the change occurred.
Finally, although the schema_name is provided in pg_event_trigger_ddl_commands(), you will need to
SELECT relname FROM pg_class WHERE relnamespace IN (SELECT oid from pg_namespace WHERE nspname = schema_name) INTO tbl_var;just to get the table name. I'm not sure why they didn't just give the table name firectly, but hey, at least there is a solution
--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

pgsql-general by date:

Previous
From: Patrick B
Date:
Subject: Re: Turning slave into a master - PostgreSQL 9.2
Next
From: Samuel Williams
Date:
Subject: Re: initdb createuser commands