Re: Way to quickly detect if database tables/columns/etc. were modified?

On Mon, Oct 31, 2016 at 4:41 PM, Evan Martin <postgresql2(at)realityexists(dot)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
<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
<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.