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

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

From: Hannu Krosing <hannu(at)2ndQuadrant(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Stephen Frost <sfrost(at)snowman(dot)net>, 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: Proposal: Store "timestamptz" of database creation on "pg_database"
Date: 2013-01-03 10:54:03
Message-ID: 50E5634B.6050803@2ndQuadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 01/03/2013 05:04 AM, Robert Haas wrote:
> O
> Yeah, I don't think this is really a problem.  I would expect the psql
> support for this feature to be not a whole lot more complicated than
> that.  Sure, it might be more than 5 lines of raw code if it requires
> an additional version of some query for which we're currently using
> the same version for both PG93 and PG92, but it's hardly fair to cite
> that as an argument for not doing this.  Such changes are almost
> entirely boilerplate.
Here is a pl/python function which gives you "the real" database 
creation time.

CREATE OR REPLACE FUNCTION database_create_ts(INOUT dbname text, OUT 
ctime timestamp)
RETURNS SETOF RECORD
LANGUAGE plpythonu AS
$$
import os, time
res = plpy.execute("""select datname,
                              current_setting('data_directory') ddir,
                              oid as dboid
                         from pg_database where datname like '%s';""" % 
dbname)
for row in res:
     dbpath = '%(ddir)s/base/%(dboid)s' % row
     stat = os.stat(dbpath)
     yield row['datname'], '%04d-%02d-%02d %02d:%02d:%02d+00' % 
time.gmtime(stat.st_ctime)[:6]
$$;

SELECT * FROM database_create_ts('template%');

------------------
Hannu





In response to

pgsql-hackers by date

Next:From: Guillaume LelargeDate: 2013-01-03 10:56:31
Subject: Re: Behaviour of bgworker with SIGHUP
Previous:From: Bernd HelmleDate: 2013-01-03 10:28:15
Subject: Re: Proposal: Store "timestamptz" of database creation on "pg_database"

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