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

> On 30 Oct 2016, at 10:45, Evan Martin <postgresql2(at)realityexists(dot)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 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).

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 said script 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.