Re: Re: Proposal: Store "timestamptz" of database creation on "pg_database"
От | Christopher Browne |
---|---|
Тема | Re: Re: Proposal: Store "timestamptz" of database creation on "pg_database" |
Дата | |
Msg-id | CAFNqd5V-A4T94a5Ts=VuRG_1yx9qvVs0EwwZ0pEA06Evd86zmA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Re: Proposal: Store "timestamptz" of database creation on "pg_database" (Robert Haas <robertmhaas@gmail.com>) |
Ответы |
Re: Re: Proposal: Store "timestamptz" of database creation
on "pg_database"
(Robert Haas <robertmhaas@gmail.com>)
|
Список | pgsql-hackers |
On Thu, Jan 3, 2013 at 12:27 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Thu, Jan 3, 2013 at 11:15 AM, Hannu Krosing <hannu@2ndquadrant.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?"
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: pg_upgrade test script creates port conflicts in parallel testing