Re: Add relcreated (timestamp) column to pg_class catalog to record the time an object was created - Mailing list pgsql-general
On Thu, Apr 21, 2016 at 10:08 AM, Karsten Hilbert <Karsten.Hilbert@gmx.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@postgresql.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."
"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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: