Re: Re: Proposal: Store "timestamptz" of database creation on "pg_database"

On Thu, Jan 3, 2013 at 12:27 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Jan 3, 2013 at 11:15 AM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
>> This is what I did with my sample pl/python function ;)
>
> Yeah, except that the "c" in "ctime" does not stand for create, and
> therefore the function isn't necessarily reliable. The problem is
> even worse for tables, where a rewrite may remove the old file and
> create a new one. I mean, I'm not stupid about this: when I need to
> figure this kind of stuff out, I do in fact look at the file times -
> mtime, ctime, atime, whatever there is. Sometimes that turns out to
> be helpful, and sometimes it doesn't. An obvious example of the
> latter is when you're looking at a bunch of files that have just been
> untarred from a backup device.

Yep, and I think that the behaviour of tar pretty nicely characterizes
what's troublesome here. It is quite likely that a tar run will *capture*
the creation time of a file, but if you pull data from a tar archive, it is
by no means obvious that the filesystem can or will accept that date
and apply it to the extracted copy.

I'd contrast pg_dump with tar in that the former is intended as more of
a "logical" dump than the latter, so that, in keeping with Greg Stark's
comments, these timestamps Should Not be captured or carried forward
by pg_dump.

The interaction with streaming replication is pretty analogous to the
interaction one might expect to get out of filesystem snapshot
technologies like DRBD, zfs, btrfs, LVM. If we put a creation time
into pg_database or pg_class, then streaming replication will, as a
"physical" replication mechanism, carry the timestamp forward into
replicas, in pretty much exactly the same fashion that timestamps
would be carried onto clones/snapshots by the filesystem
snapshotting systems.

And in contrast, I'd expect Andres Freund's logical replication
infrastructure *NOT* to carry these dates over, but rather to establish
fresh new creation dates on a replica. (And from a forensic perspective,
that's a perfectly fine thing.)

I imagine that we should be careful to put these forensic timestamps
onto things with some care.

- Putting them on pg_database seems like a fine idea.
- Putting them on pg_attribute seems mighty dodgy; I don't expect I'd
often care, and this change increases the size of an extremely heavily
accessed system table
- I am equivocal about putting them on pg_class. That increases the
size of a pretty big, heavily accessed system table.
- Perhaps there are other relevant tables (pg_event_trigger,
pg_extension, FDW tables, pg_language, pg_proc, pg_tablespace); I
don't feel so strongly about them, but if you're puzzling over what
went wrong with an extension, event trigger, or FDW, time of creation
seems like it might be useful.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"