Skip site navigation (1) Skip section navigation (2)

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

From: Christopher Browne <cbbrowne(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Hannu Krosing <hannu(at)2ndquadrant(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, Andres Freund <andres(at)2ndquadrant(dot)com>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Re: Proposal: Store "timestamptz" of database creation on "pg_database"
Date: 2013-01-03 17:54:45
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers
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?"

In response to


pgsql-hackers by date

Next:From: Tom LaneDate: 2013-01-03 17:58:01
Subject: pg_upgrade test script creates port conflicts in parallel testing
Previous:From: Heikki LinnakangasDate: 2013-01-03 17:47:40
Subject: Re: PATCH: Split stats file per database WAS: autovacuum stress-testing our system

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group