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

If I have a query that reads from system tables like pg_class,
pg_namespace, pg_attribute, pg_type, etc. and I'd like to cache the
results in my application is there any fast way to detect when any
changes have been made to these system catalogs? I don't  need to know
exactly what has changed. Some kind of a global "database version" would
do, just so I know that I need to invalidate my cache (the database
definition is rarely modified in practice).

> On 30 Oct 2016, at 10:45, Evan Martin <postgresql2@realityexists.net> wrote:
>
> If I have a query that reads from system tables like pg_class, pg_namespace, pg_attribute, pg_type, etc. and I'd like
tocache the results in my application is there any fast way to detect when any changes have been made to these system
catalogs?I don't  need to know exactly what has changed. Some kind of a global "database version" would do, just so I
knowthat I need to invalidate my cache (the database definition is rarely modified in practice). 

I think the usual practice for such situations is to do database changes through SQL scripts[1] that are under version
control.Since they are under VC, you can automatically write the version[2] into the SQL script on commit of changes to
saidscript through a commit hook. 
That version in the SQL script can then be used in an UPDATE statement to some database-global settings table[3].

And there you have your database version.

Ad 1. Never do changes directly in the database when you go this route!
Ad 2. Those are often hashes these days.
Ad 3. You could even have the UPDATE statement be automatically added by the commit hook of your VC of choice.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

> On Oct 30, 2016, at 4:45 AM, Evan Martin <postgresql2@realityexists.net> wrote:
>
> If I have a query that reads from system tables like pg_class, pg_namespace, pg_attribute, pg_type, etc. and I'd like
tocache the results in my application is there any fast way to detect when any changes have been made to these system
catalogs?I don't  need to know exactly what has changed. Some kind of a global "database version" would do, just so I
knowthat I need to invalidate my cache (the database definition is rarely modified in practice). 

Maybe create an event trigger that updates a simple table with the last modification time or sends a notification?

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

John DeSoi, Ph.D.

On Mon, Oct 31, 2016 at 09:14:07AM -0400, Melvin Davidson wrote:

>> Maybe create an event trigger that updates a simple table with the last
>> modification time or sends a notification?
...
> That would certainly work, but
> the problem is, that trigger would have to be created for every table in
> the database. When you have more than a couple dozen tables, as in
> hundreds, it becsmes a huge undertaking.*--

Well, it could be generated.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On 10/31/2016 07:17 AM, Melvin Davidson wrote:
>
>
> On Mon, Oct 31, 2016 at 9:48 AM, Karsten Hilbert
> <Karsten.Hilbert@gmx.net <mailto:Karsten.Hilbert@gmx.net>> wrote:
>
>     On Mon, Oct 31, 2016 at 09:14:07AM -0400, Melvin Davidson wrote:
>
>     >> Maybe create an event trigger that updates a simple table with the last
>     >> modification time or sends a notification?
>     ...
>     > That would certainly work, but
>     > the problem is, that trigger would have to be created for every table in
>     > the database. When you have more than a couple dozen tables, as in
>     > hundreds, it becsmes a huge undertaking.*--
>
>     Well, it could be generated.
>
>     Karsten
>     --
>     GPG key ID E4071346 @ eu.pool.sks-keyservers.net
>     <http://eu.pool.sks-keyservers.net>
>     E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>     <http://www.postgresql.org/mailpref/pgsql-general>
>
>
>>Well, it could be generated.
> True, but it still is more code to maintain, whereas I maintain the
> addition addition of one column in pg_class and pg_attribute with a
> default of now()
> would make more sense. That being said, there were so many naysayers
> grasping at corner cases the last time I brought this up I have given up
> pursuing it.

For those that want to see the previous discussions and the non-trivial
issues that where raised:

https://www.postgresql.org/message-id/CANu8FizWg8GMSmm_d8X0B6uYi7o535f3vqMD3nqFXqrrYB6huA%40mail.gmail.com

https://www.postgresql.org/message-id/mbrynjrrq6ckoxap20n2gqx4.1461185428476%40email.android.com

>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com

> On Oct 31, 2016, at 8:14 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
>
> That would certainly work, but the problem is, that trigger would have to be created for every table in the database.

> When you have more than a couple dozen tables, as in hundreds, it becsmes a huge undertaking.

Unless I'm misunderstanding the documentation, you create the trigger on the "ddl event" not a  table. The events are
ddl_command_start,ddl_command_end, table_rewrite and sql_drop. I have not used this feature, but it seems like you
wouldjust need one function. 

https://www.postgresql.org/docs/current/static/event-trigger-definition.html

John DeSoi, Ph.D.

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).