Thread: 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 GÜNDÜZ <devrim@gunduz.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 GÜNDÜZ <devrim@gunduz.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@sonic.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@sonic.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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
<p><font size="2">Andrew Dunstan wrote:<br /> <...><br /> ><br /> > Can someone please give a good, concreteuse case for this stuff? "Might<br /> > be nice to have" doesn't cut it, I'm afraid. In particular, I'd like to<br/> > know why logging statements won't do the trick here.<br /> ><br /><br /> Please pardon the kibbitzer intrusion...<br /><br /> Informix has this feature and I've often yearned for it in PostgreSQL (although it is low on mypersonal 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 similarlynamed tables or functions with the same structure but different row counts or variant codings. The logs have longsince been offlined to gosh knows where or lost -- we're talking 5 or more years of activity -- and even scripts maybe suspect (the checked in script might refer to an original table but the DBA made on the fly changes) or some otherDBA-like creature did things without proper procedures being followed.<br /><br /> Having that date has been criticalto resolving those issues of which table came in which order. It also gives a time window to use to go check oldemails, archives, etc. for more information.<br /><br /> Last update of data seems prohibitively expensive; if a userwants that a trigger and a 2nd table could well do that. Last DDL mod ... I could see the use but my old workhorse doesn'toffer it so it never occurred to me to want it. Until know. '-)<br /><br /> But this request is adding metadata, Iagree. But with my vague understandings adding a date or time stamp for table creation wouldn't be a large bloat and ifonly required at creation seems low overhead.<br /><br /> But maybe only bad DBAs need it. Or good DBAs who inherit systemsfrom bad ones ?<br /><br /> Sorry for the crufty posting -- my web client has recently deteriorated in terms of messageformatting.<br /><br /> Greg Williamson<br /> Senior DBA<br /> DigitalGlobe<br /><br /> Confidentiality Notice: Thise-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidentialand 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 byreply e-mail and destroy all copies of the original message.<br /><br /> (My corporate masters made me say this.)<br /><br/></font>
> 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@fetter.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@fetter.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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate