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

On Sun, Oct 30, 2016 at 8:04 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:

>
> > 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.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Evan,

FWIW, a long time ago I made a request in Customer Feedback
https://postgresql.uservoice.com/forums/21853-general
to add the creation time for all objects.
https://postgresql.uservoice.com/forums/21853-general/suggestions/5587129-add-relcreated-timestamp-column-to-pg-class-cata

I made the same request in this forum.
Unfortunately, few people agree that it would be worthwhile, despite the
fact that the creation times are available in Oracle & MS SQL..
What you are asking would require a similar mod to pg_attribute, but based
on my request, that seems unlikely. So the current solution
is to implement version control software. However, that does not solve the
problem of gremlins (developers) that like to play and make
changes while bypassing CVS.

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