Re: Keeping creation time of objects

Hi,

AFAICS, PostgreSQL is not keeping info about when a table, database,
sequence, etc was created. We cannot get that info even from OS, since
CLUSTER or VACUUM FULL may change the metadata of corresponding
relfilenode.

Does anyone think that adding a timestamp column to pg_class would bring
an overhead? For me, it looks a bit easy to add that value while calling
CREATE XXX, but does anyone see a corner case?

Regards,
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= <devrim(at)gunduz(dot)org> writes:
> AFAICS, PostgreSQL is not keeping info about when a table, database,
> sequence, etc was created. We cannot get that info even from OS, since
> CLUSTER or VACUUM FULL may change the metadata of corresponding
> relfilenode.

> Does anyone think that adding a timestamp column to pg_class would bring
> an overhead?

There isn't sufficient support for such a "feature". In any case, why
would creation time (as opposed to any other time, eg last schema
modification, last data modification, yadda yadda) be especially
significant? Would you expect it to be preserved over dump/restore?
How about every other object type in the system?

regards, tom lane

On Tue, Sep 09, 2008 at 03:36:19PM -0400, Tom Lane wrote:
> Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= <devrim(at)gunduz(dot)org> writes:
> > AFAICS, PostgreSQL is not keeping info about when a table, database,
> > sequence, etc was created. We cannot get that info even from OS, since
> > CLUSTER or VACUUM FULL may change the metadata of corresponding
> > relfilenode.
>
> > Does anyone think that adding a timestamp column to pg_class would bring
> > an overhead?
>
> There isn't sufficient support for such a "feature". In any case, why
> would creation time (as opposed to any other time, eg last schema
> modification, last data modification, yadda yadda) be especially
> significant? Would you expect it to be preserved over dump/restore?
> How about every other object type in the system?

I'd be very interested in seeing a last schema modification time for pg_class
objects. I don't care about it being preserved over dump and restore as my
use case is more to find out when a table was created with a view to finding
out if it is still needed. So the question I'm looking to answer is "when did
that get here?"

-dg

--
David Gould daveg(at)sonic(dot)net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.

On Tue, 2008-09-09 at 15:36 -0400, Tom Lane wrote:
> why would creation time (as opposed to any other time, eg last schema
> modification, last data modification, yadda yadda) be especially
> significant?

Hmm, those would be cool, too.

Seriously, I believe we can get last data modification from filesystem
(if it is keeping of course), but we cannot get the creation time --
that's why I am talking about the creation time.

It would be useful when a DBA is not sure whether (s)he created the
object on a known time, or it was not restored from backups correctly or
not.

> Would you expect it to be preserved over dump/restore?

No. If we are talking about "creation time", then it means we should not
preserve it, IMHO.

> How about every other object type in the system?

I'm talking about every object.

Regards,
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

> There isn't sufficient support for such a "feature".

It sounds like a useful feature to me.

> In any case, why
> would creation time (as opposed to any other time, eg last schema
> modification, last data modification, yadda yadda) be especially
> significant?

Those would be nice to have too, but last data modification is
doubtless too expensive to compute and keep up to date.

> Would you expect it to be preserved over dump/restore?

Definitely not. Then it wouldn't really be the creation time, would it?

> How about every other object type in the system?

Good idea. I wouldn't bother for things that are intended to be
ephemeral, but having this for, say, functions, would be nice.

...Robert

On Tue, 2008-09-09 at 12:40 -0700, daveg wrote:

> I'd be very interested in seeing a last schema modification time for pg_class
> objects. I don't care about it being preserved over dump and restore as my
> use case is more to find out when a table was created with a view to finding
> out if it is still needed.

Isn't it easier to find out if it is still needed by looking if it is
still used, say from pg_stat_user_tables ?

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

On Tue, Sep 09, 2008 at 11:03:56PM +0300, Hannu Krosing wrote:
> On Tue, 2008-09-09 at 12:40 -0700, daveg wrote:
>
> > I'd be very interested in seeing a last schema modification time for pg_class
> > objects. I don't care about it being preserved over dump and restore as my
> > use case is more to find out when a table was created with a view to finding
> > out if it is still needed.
>
> Isn't it easier to find out if it is still needed by looking if it is
> still used, say from pg_stat_user_tables ?

Except that pg_dump will access it and make it look used. Also, this does
not work for functions, views etc.

It seems to me to be pretty simple to put an abstime or timestamp column
on the major catalog tables and update it when the row is updated. A mod
time is more useful probably than a create time.

-dg

--
David Gould daveg(at)sonic(dot)net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.

daveg wrote:
> On Tue, Sep 09, 2008 at 11:03:56PM +0300, Hannu Krosing wrote:
>
>> On Tue, 2008-09-09 at 12:40 -0700, daveg wrote:
>>
>>
>>> I'd be very interested in seeing a last schema modification time for pg_class
>>> objects. I don't care about it being preserved over dump and restore as my
>>> use case is more to find out when a table was created with a view to finding
>>> out if it is still needed.
>>>
>> Isn't it easier to find out if it is still needed by looking if it is
>> still used, say from pg_stat_user_tables ?
>>
>
> Except that pg_dump will access it and make it look used. Also, this does
> not work for functions, views etc.
>
> It seems to me to be pretty simple to put an abstime or timestamp column
> on the major catalog tables and update it when the row is updated. A mod
> time is more useful probably than a create time.
>
>
>

I must say I'm suspicious of this whole proposal. It looks a whole lot
like data creeping into metadata.

We already have the ability to log just DDL statements, although that's
somewhat incomplete in that it doesn't track DDL performed by functions.

Can someone please give a good, concrete use case for this stuff? "Might
be nice to have" doesn't cut it, I'm afraid. In particular, I'd like to
know why logging statements won't do the trick here.

cheers

andrew

Hi Andrew,

On Tue, 2008-09-09 at 16:22 -0400, Andrew Dunstan wrote:
> I'd like to know why logging statements won't do the trick here.

It is not on by default, logs are rotated, and may be lost, etc.

Regards,
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

On Tue, Sep 09, 2008 at 10:20:00PM +0300, Devrim GUNDUZ wrote:
> Hi,
>
> AFAICS, PostgreSQL is not keeping info about when a table, database,
> sequence, etc was created. We cannot get that info even from OS,
> since CLUSTER or VACUUM FULL may change the metadata of
> corresponding relfilenode.

When people aren't keeping track of their DDL, that is very strictly a
process problem on their end. When people are shooting themselves in
the foot, it's a great disservice to market Kevlar shoes to them.

> Does anyone think that adding a timestamp column to pg_class would
> bring an overhead? For me, it looks a bit easy to add that value
> while calling CREATE XXX, but does anyone see a corner case?

As above, I am making a case for never attempting any such a thing,
and instead helping people understand that a casual attitude about
their DDL will result in cascading--usually catastrophic--failures.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Andrew Dunstan wrote:
<...>
>
> Can someone please give a good, concrete use case for this stuff? "Might
> be nice to have" doesn't cut it, I'm afraid. In particular, I'd like to
> know why logging statements won't do the trick here.
>

Please pardon the kibbitzer intrusion ...

Informix has this feature and I've often yearned for it in PostgreSQL (although it is low on my personal priorities). Typical use case I've run into is working on legacy databases where the original DBA is gone or senile (deprecating self-reference not to applied to any one on this list) and I need to make sense of a muddle of similarly named tables or functions with the same structure but different row counts or variant codings. The logs have long since been offlined to gosh knows where or lost -- we're talking 5 or more years of activity -- and even scripts may be suspect (the checked in script might refer to an original table but the DBA made on the fly changes) or some other DBA-like creature did things without proper procedures being followed.

Having that date has been critical to resolving those issues of which table came in which order. It also gives a time window to use to go check old emails, archives, etc. for more information.

Last update of data seems prohibitively expensive; if a user wants that a trigger and a 2nd table could well do that. Last DDL mod ... I could see the use but my old workhorse doesn't offer it so it never occurred to me to want it. Until know. '-)

But this request is adding metadata, I agree. But with my vague understandings adding a date or time stamp for table creation wouldn't be a large bloat and if only required at creation seems low overhead.

But maybe only bad DBAs need it. Or good DBAs who inherit systems from bad ones ?

Sorry for the crufty posting -- my web client has recently deteriorated in terms of message formatting.

Greg Williamson
Senior DBA
DigitalGlobe

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)

> When people aren't keeping track of their DDL, that is very strictly a
> process problem on their end. When people are shooting themselves in
> the foot, it's a great disservice to market Kevlar shoes to them.

I can't believe anyone is going to stop tracking their DDL because,
ooh goody, now we have pg_class.creation_time. They will look at and
say either "oh, this is nice" or "oh, this is useless" and go on about
their business.

I try pretty hard not to shoot myself in the foot. But if someone
comes up to me and offers me some shoes that are have the same cost,
appearance, comfort-level, and durability as regular shoes but are
slightly more bullet resistant, should I refuse them on principle?
Why?

...Robert

Robert Haas wrote:
> I try pretty hard not to shoot myself in the foot. But if someone
> comes up to me and offers me some shoes that are have the same cost,
> appearance, comfort-level, and durability as regular shoes but are
> slightly more bullet resistant, should I refuse them on principle?
> Why?
>
>
>

The premise is false. Nothing is cost free. Every feature adds to code
complexity, and has to be maintained.

I am still quite unconvinced by any of the justifications advanced so
far for this "feature".

cheers

andrew

On Tue, 9 Sep 2008, David Fetter <david(at)fetter(dot)org> writes:
>> AFAICS, PostgreSQL is not keeping info about when a table, database,
>> sequence, etc was created. We cannot get that info even from OS,
>> since CLUSTER or VACUUM FULL may change the metadata of
>> corresponding relfilenode.
>
> When people aren't keeping track of their DDL, that is very strictly a
> process problem on their end. When people are shooting themselves in
> the foot, it's a great disservice to market Kevlar shoes to them.

Word. In the company I'm currently working at we store database schema
in a VCS repository with minor and major version taggings. And there is
a current_foo_soft_version() function that returns the revision of the
related database schema. If there is no control over the database schema
changes in a company working scheme, the most logging-feature-rich
PostgreSQL release will provide an insignificant benefit compared the
mess needs to get fixed.

Regards.

Hi,

Devrim GÜNDÜZ wrote:
> On Tue, 2008-09-09 at 15:36 -0400, Tom Lane wrote:
>> why would creation time (as opposed to any other time, eg last schema
>> modification, last data modification, yadda yadda) be especially
>> significant?
> Hmm, those would be cool, too.

maybe except last data modification.

But for audit reasons its really helpful so see if
someone has had hands on objects since they have
been created. So if it would not cost us arm and leg
I'm all for having created/changed timestamps for
all objects.

Regards
Tino

On Wed, 2008-09-10 at 09:27 +0300, Volkan YAZICI wrote:
> On Tue, 9 Sep 2008, David Fetter <david(at)fetter(dot)org> writes:
> >> AFAICS, PostgreSQL is not keeping info about when a table, database,
> >> sequence, etc was created. We cannot get that info even from OS,
> >> since CLUSTER or VACUUM FULL may change the metadata of
> >> corresponding relfilenode.
> >
> > When people aren't keeping track of their DDL, that is very strictly a
> > process problem on their end. When people are shooting themselves in
> > the foot, it's a great disservice to market Kevlar shoes to them.
>
> Word. In the company I'm currently working at we store database schema
> in a VCS repository with minor and major version taggings. And there is
> a current_foo_soft_version() function that returns the revision of the
> related database schema. If there is no control over the database schema
> changes in a company working scheme, the most logging-feature-rich
> PostgreSQL release will provide an insignificant benefit compared the
> mess needs to get fixed.

Timestamps should rather be considered a forensic tool.

You may have the best VCS system, but if somebody bypasses it, you may
still need to find out, when it was done.

Until we have some enforcable audit facilities for DDL in place _inside_
the database, having at least timestamps often helps.

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

Hannu Krosing wrote:

> Timestamps should rather be considered a forensic tool.
>
> You may have the best VCS system, but if somebody bypasses it, you may
> still need to find out, when it was done.

So you're arguing for modification time, which is not was Devrim is
proposing -- he's proposing creation time.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Hi,

On Tue, 2008-09-09 at 23:14 -0400, Andrew Dunstan wrote:
> Nothing is cost free. Every feature adds to code complexity, and has
> to be maintained.

With full respect to you: I'm only talking about creation time. How much
overhead and complexity are you expecting?

Cheers,
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

On Tue, Sep 09, 2008 at 10:50:57PM -0400, Robert Haas wrote:
> > When people aren't keeping track of their DDL, that is very
> > strictly a process problem on their end. When people are shooting
> > themselves in the foot, it's a great disservice to market Kevlar
> > shoes to them.
>
> I can't believe anyone is going to stop tracking their DDL because,
> ooh goody, now we have pg_class.creation_time. They will look at
> and say either "oh, this is nice" or "oh, this is useless" and go on
> about their business.

I can easily believe that a "feature" like this might "help" them make
the decision not to start out of a false sense of security.

> I try pretty hard not to shoot myself in the foot. But if someone
> comes up to me and offers me some shoes that are have the same cost,

Not the same. This is extra code, so it will provide both new places
for bugs and extra maintenance costs.

> appearance,

Clearly not the same.

> comfort-level,

False comfort is bad. Putting an anesthetic instead of support in a
shoe billed as orthopedic may make customers "comfortable," but when
they continue to damage their foot with it, it's not a feature.

> and durability as regular shoes but are slightly more bullet
> resistant, should I refuse them on principle?

See above.

> Why?

See above.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate