pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedtable creation/modification datestamp
5+ messages / 3 participants
[nested] [flat]
* table creation/modification datestamp
@ 2008-05-26 18:18 Kevin Neufeld <kneufeld@refractions.net>
2008-05-26 20:39 ` Re: table creation/modification datestamp Andrew Sullivan <ajs@commandprompt.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Neufeld @ 2008-05-26 18:18 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: table creation/modification datestamp
2008-05-26 18:18 table creation/modification datestamp Kevin Neufeld <kneufeld@refractions.net>
@ 2008-05-26 20:39 ` Andrew Sullivan <ajs@commandprompt.com>
2008-05-26 21:25 ` Re: table creation/modification datestamp Andreas 'ads' Scherbaum <adsmail@wars-nicht.de>
0 siblings, 1 reply; 5+ messages in thread
From: Andrew Sullivan @ 2008-05-26 20:39 UTC (permalink / raw)
To: pgsql-admin
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: table creation/modification datestamp
2008-05-26 18:18 table creation/modification datestamp Kevin Neufeld <kneufeld@refractions.net>
2008-05-26 20:39 ` Re: table creation/modification datestamp Andrew Sullivan <ajs@commandprompt.com>
@ 2008-05-26 21:25 ` Andreas 'ads' Scherbaum <adsmail@wars-nicht.de>
2008-05-26 21:37 ` Re: table creation/modification datestamp Andrew Sullivan <ajs@commandprompt.com>
2008-05-26 21:43 ` Re: table creation/modification datestamp Kevin Neufeld <kneufeld@refractions.net>
0 siblings, 2 replies; 5+ messages in thread
From: Andreas 'ads' Scherbaum @ 2008-05-26 21:25 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: table creation/modification datestamp
2008-05-26 18:18 table creation/modification datestamp Kevin Neufeld <kneufeld@refractions.net>
2008-05-26 20:39 ` Re: table creation/modification datestamp Andrew Sullivan <ajs@commandprompt.com>
2008-05-26 21:25 ` Re: table creation/modification datestamp Andreas 'ads' Scherbaum <adsmail@wars-nicht.de>
@ 2008-05-26 21:37 ` Andrew Sullivan <ajs@commandprompt.com>
1 sibling, 0 replies; 5+ messages in thread
From: Andrew Sullivan @ 2008-05-26 21:37 UTC (permalink / raw)
To: pgsql-admin
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: table creation/modification datestamp
2008-05-26 18:18 table creation/modification datestamp Kevin Neufeld <kneufeld@refractions.net>
2008-05-26 20:39 ` Re: table creation/modification datestamp Andrew Sullivan <ajs@commandprompt.com>
2008-05-26 21:25 ` Re: table creation/modification datestamp Andreas 'ads' Scherbaum <adsmail@wars-nicht.de>
@ 2008-05-26 21:43 ` Kevin Neufeld <kneufeld@refractions.net>
1 sibling, 0 replies; 5+ messages in thread
From: Kevin Neufeld @ 2008-05-26 21:43 UTC (permalink / raw)
To: pgsql-admin
That the conclusion I came too as well. Thanx.
-- Kevin
Andreas 'ads' Scherbaum wrote:
...
> ... There's no way to track DDL changes.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-05-26 21:43 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-26 18:18 table creation/modification datestamp Kevin Neufeld <kneufeld@refractions.net>
2008-05-26 20:39 ` Andrew Sullivan <ajs@commandprompt.com>
2008-05-26 21:25 ` Andreas 'ads' Scherbaum <adsmail@wars-nicht.de>
2008-05-26 21:37 ` Andrew Sullivan <ajs@commandprompt.com>
2008-05-26 21:43 ` Kevin Neufeld <kneufeld@refractions.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox