Re: Get the date of creation of objects in the database

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> 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.

No, I just wasn't impressed by those concerns.  That's about like asking
"what should rsync do with filesystem timestamps?"  It's really not a
hugely difficult issue to address.

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

It's not an auditing system, no, it'd be a "this is the last time we
wrote to this relation."  We'd have to caveat that accordingly, to point
out that we might have data in shared buffers, etc, but this is a
definitional concern for the function, not an argument that such a
capability wouldn't be useful to some people.

> But I thought this request was about DDL timestamps, not data timestamps.

This specific one, the question about data modification was brought up
on at least the thread which I was reading.

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

Agreed, which is why I only suggested a function that looks at the
filesystem for the "last data modification time" case.  The other cases,
which addressed the question brought up on this thread but you didn't
quote into your response, would depend on new fields in the relevant
system catalogs.

> 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.)

Yes, we would need such timestamps, no, I don't agree that having such
makes it useless (are the timestamps on unix filesystems similairly
"useless"?  I'd say no.)

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

We're back to the argument about the additional overhead and I simply
don't buy into that.  Yes, we'd have to come up with reasonable answers
to the above questions, but I don't see that as beyond our capabilities
or impossible to have reasonable answers which most users will
appreciate (COMMENT => yes, REINDEX => no, VACUUM => no, ANALYZE => no,
ALTER TABLE => yes, CREATE POLICY => yes, FOREIGN KEY REFERENCE => no;
note that this list is, essentially, "would a schema-only pg_dump for
*this* table be different?").

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

I certainly don't mean to imply that this is more valuable or important
than a proper in-core auditing solution.  I don't believe our current
logging implementation is a credible answer to this request.  Given that
we don't have an in-core auditing solution, it's hard to say if it would
be easy to handle such a request.

Ultimately, I don't see what's been done by the other RDBM systems as
being particularly novel or difficult for us to provide and, in this
case, I don't believe they're just providing a feature which no one uses
or understands but rather are responding to entirely reasonable user
requests and have a reasonable solution that's continued to be useful
over time.

Even so, I'm not so enamored with this request that I'd spend resources
on it, but I wouldn't throw away a patch which implemented it out of
hand either.

Thanks!

Stephen