From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Frost <sfrost@snowman.net>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Edson F. Lidorio <edson@openmailbox.org>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Get the date of creation of objects in the database
Date: Mon, 22 Feb 2016 23:19:44 -0500
Message-ID: <2862.1456201184@sss.pgh.pa.us> (raw)
In-Reply-To: <20160223035018.GB3127@tamriel.snowman.net>
References: <56CB84F7.20803@openmailbox.org>
<CAKFQuwaW4vnYbR5aVhG_YFExqMAkZzKpRLnBaZ-BqONvZPFNEA@mail.gmail.com>
<20160223035018.GB3127@tamriel.snowman.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
Stephen Frost <sfrost@snowman.net> writes:
> At least on a first blush look through the threads linked from such a
> search, I'm unimpressed by the arguments against and note that there are
> quite a few arguments for.
I think you missed the worries around what dump/reload semantics would be.
> We could provide a function for 'last data modification time' which
> simply uses the filesystem modification time.
As far as tables go, the filesystem mod time would not be anything of
great use to users. Consider that (1) there might be committed but
unwritten data sitting in shared buffers, so the filesystem mod time could
be too old by as much as the max checkpoint interval; while (2) writes for
hint bit setting or xid freezing could happen long after the last data
write, so the filesystem mod time could be almost arbitrarily later than
what the user thinks is the last mod time. Not to mention whether
physical rewrites such as CLUSTER ought to count as data mods.
But I thought this request was about DDL timestamps, not data timestamps.
The filesystem will help you not at all there, because at best it would
know about the last mod time on the relevant system catalog, not any
individual object.
Anyway, my main objection to this idea is that it would be a sinkhole for
arguments over what the detailed semantics would be. Should dump/reload
result in a new DDL timestamp? (If not, the only way to prevent it would
be to invent a new "ALTER object SET TIMESTAMP" family of DDL, which would
not merely be a lot of work but would mean that the timestamps would have
exactly 0 value for any sort of forensic purposes.) Should, eg, COMMENT
ON cause a DDL timestamp update on the referenced object? How about
REINDEX or VACUUM or ANALYZE? How about something like creating a foreign
key reference to a table? I think that you could make credible arguments
either way on each of these issues, depending on what you assume the true
use-case is for having the timestamps; which means that trying to support
them is a mug's game. We won't satisfy anybody, least of all the users
who don't care and don't need the additional overhead.
Lastly, even if we had a DDL timestamp, it wouldn't tell you anything
about what that last change was. So I think logging/auditing DDL
operations is a far better path to pursue.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, sfrost@snowman.net, david.g.johnston@gmail.com, edson@openmailbox.org
Subject: Re: Get the date of creation of objects in the database
In-Reply-To: <2862.1456201184@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox