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: CAFNqd5V-A4T94a5Ts=VuRG_1yx9qvVs0EwwZ0pEA06Evd86zmA@mail.gmail.com (view raw or flat)
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

Responses

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-2014 The PostgreSQL Global Development Group