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

On Thu, Jan 3, 2013 at 12:54 PM, Christopher Browne <cbbrowne(at)gmail(dot)com> wrote:
> 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 agree all around.

And to take a step back and speak a bit more broadly about this, I
believe that, more and more, we can't rely on the operating system to
do things for us any more. Five or ten years ago, maybe people were
running Linux, and PostgreSQL was a part of that. Now, more and more,
people are running PostgreSQL, and Linux (or Windows, or some other
OS) is a way to make that happen. At least when I talk to customers,
places where the OS behavior bleeds into what the database server does
are not viewed as features. Telling people that we use the OS
collation facilities, or that we use the OS buffer cache, or that we
don't provide a scheduler because Linux has cron and Windows has
scheduled tasks, or that people should examine file timestamps to try
to work out when a relation was created results in bemusement, or
sometimes incredulity. Many people are understanding of the idea that
we don't have the manpower to implement everything ourselves, but very
few customers I've spoken with think that planning to rely on the OS
facilities is a sound design principle. It's true, as we've often
said here, that leveraging the OS facilities means that we get the
benefit of improving OS facilities "for free" - but it also means that
we never exceed what the OS facilities are able to provide. And
frankly, as in this case, the OS facilities are often poorly suited to
what users actually want. We obviously do not want to go bonkers and
take over everything from the OS, but I don't think we should be
afraid to rotate the knob a little bit in that direction. The fact
that people are pushing us to go there is a sign of our success. We
are the ecosystem.

I do have a concern about catalog bloat. I think it would be easy to
add so many knobs that we end up slowing the system down and bloating
the size of an otherwise-empty database, or one with lots of SQL
objects. Let's not do that. But let's not do nothing, either.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company