Thread: table creation/modification datestamp
As a DBA, it would be extremely useful to know when tables have been created or modified. PostgreSQL doesn't keep track of this, does it? (Table modification, in my mind, includes INSERTs, DELETEs, UPDATEs, and changes performed via ALTER TABLE. It does not include physical changes that may occur on the table due to VAACUMing or CLUSTERing.) Obviously, I can't use the relfilenode attribute in pg_class to check for table modification and look up the file's timestamp, since the OS updates the timestamp whenever physical changes occur. Further, I don't think linux even tracks a file's creation timestamp. The stats collector already tracks when a table was last ANALYZED, it would be nice to expand this functionality to include table creation/modification times. (Ideally, table timestamp record keeping should be persistent over db dumps and restores.) Cheers, Kevin
On Mon, May 26, 2008 at 11:18:37AM -0700, Kevin Neufeld wrote: > As a DBA, it would be extremely useful to know when tables have been > created or modified. PostgreSQL doesn't keep track of this, does it? Nope. There's a project in pgfoundry, IIRC, to do it with triggers. You could also use Slony (or any of its work-similar friends) to do something similar. A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/
On Mon, 26 May 2008 16:39:58 -0400 Andrew Sullivan wrote: > On Mon, May 26, 2008 at 11:18:37AM -0700, Kevin Neufeld wrote: > > As a DBA, it would be extremely useful to know when tables have been > > created or modified. PostgreSQL doesn't keep track of this, does it? > > Nope. There's a project in pgfoundry, IIRC, to do it with triggers. > You could also use Slony (or any of its work-similar friends) to do > something similar. Maybe you mean: http://pgfoundry.org/projects/tablelog/ But that covers only data changes. There's no way to track DDL changes. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group
On Mon, May 26, 2008 at 11:25:56PM +0200, Andreas 'ads' Scherbaum wrote: > > But that covers only data changes. There's no way to track DDL changes. Ah, yes, you want DDL changes. No, the only way I know of doing that is by tracking the logs. A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/
That the conclusion I came too as well. Thanx. -- Kevin Andreas 'ads' Scherbaum wrote: ... > ... There's no way to track DDL changes. >