Re: Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Thu, Apr 21, 2016 at 10:08 AM, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
wrote:

> On Thu, Apr 21, 2016 at 09:40:18AM -0400, Melvin Davidson wrote:
>
> > "and what about user objects added to a database which is
> > then used as a template for creating another DB ?"
> >
> > This existence of objects that are part of the default schema is NOT a
> > problem. Developers and users should never have access to a template.
>
> Just one example of why that assertion does not hold:
>
> GNUmed stores medical records. There's no allowance for
> loosing data. One measure it takes to protect data is to
> execute (roughly) the following sequence when a database
> schema upgrade is needed (currently at major release 21
> thereof). Say, going from v20 to v21:
>
> - create database 'gnumed_v21' template 'gnumed_v20'
> - from this point on gnumed_v20 is NOT TOUCHED anymore
> - at this point gnumed_v21 is identical to gnumed_v20 as far as GNUmed is
> concerned
> - apply - to gnumed_v21 - those SQL fixups scripts intended to
> bring v20 up to the very latest minor release of v20
> - apply - to gnumed_v21 - the v20.latest->v21 upgrade SQL scripts
> - apply - to gnumed_v21 - the SQL fixup scripts intended to
> bring v21 up to the very latest minor release of v21
>
> Whatever goes wrong after having cloned gnumed_v20 into
> gnumed_v21 doesn't matter to the user because they can
> _always_ go back to using the gnumed_v20 database until a
> future upgrade run succeeds at which point they can switch
> over.
>
> Of course, this can also be done via dump v20 / restore into
> v21 but that's slightly more fragile (more things can go
> wrong).
>
> > The point is to be able to track down rogue objects created
> > by developers and users
>
> That is easy. Compare dumps of the current schema against the
> official schema.
>
> In fact, GNUmed does so. The upgrade does not even start if
> the template schema does not pass an md5 comparison and it
> does not consider success unless the upgraded schema passes
> another (target) md5 comparison.
>
> Furthermore, the client refuses to connect to a given
> database if it cannot verify that database's schema via
> expected md5 thereof.
>
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
>
> --
> 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
>

"Just one example of why that assertion does not hold:"

I fail to see your point

"That is easy. Compare dumps of the current schema against the
official schema."

So your solution is to do a dump and then grep for anomalies? How is that
faster than just querying for recently created objects, or objects created
at odd days/hours (weekends/early moring)?

You seem to be spending all of your time finding exemptions rather than
understanding the benefit. So what is your point? That it is not worthwhile
because there are a few cases where it might not work?

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