Обсуждение: [HACKERS] register creation date of table
Hi, We have several users working on a 8.4 database, using it as a back-end for several related apps and transfering data to and from it. The database tends to get a bit messy, so i've made a little table to provide an overview. This table is truncated and refilled daily, it shows all tables and views in the database and : * the owner * number of records (estimation) * it's size on disk * the description There's a view on the table that shows the size as pg_size_pretty When you edit the description in the table (or the view, but no support in pgAdmin), the comment in the system tables is updated also. I attatched my code, hope some people find it handy, sorry for the names and comments being in dutch. Now, i would like to improve this thing and add a creation date for the table. I have some questions about that. 1. I think that there is no such information in the system tables. is that correct? I am planning to change the mechanism, so that the table is not truncated, but new tables are inserted in the overview and dropped tables are deleted from it. I need to do that in 2 steps (delete and insert). Then i can add a creation-date column which i will fill with 'today'. 2. i would like to go back in time. I think that i will just look up the creation date for the files in the data directory and translate their oid's to the object names and then update their dates. This would of course only work from the last restore. Is that a good way to do it? Thanks, WBL -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
Вложения
Hi, On Fri, 2011-10-14 at 12:20 +0200, Willy-Bas Loos wrote: > [...] > We have several users working on a 8.4 database, using it as a > back-end for several related apps and transfering data to and from it. > The database tends to get a bit messy, so i've made a little table to > provide an overview. > This table is truncated and refilled daily, it shows all tables and > views in the database and : > * the owner > * number of records (estimation) > * it's size on disk > * the description > > There's a view on the table that shows the size as pg_size_pretty > When you edit the description in the table (or the view, but no > support in pgAdmin), the comment in the system tables is updated also. > I attatched my code, hope some people find it handy, sorry for the > names and comments being in dutch. > I'm not sure I understand your comment: "no support in pgAdmin". No support for what? > Now, i would like to improve this thing and add a creation date for the table. > I have some questions about that. > 1. I think that there is no such information in the system tables. is > that correct? Yes. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Fri, Oct 14, 2011 at 2:51 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote: >> When you edit the description in the table (or the view, but no >> support in pgAdmin), the comment in the system tables is updated also. > I'm not sure I understand your comment: "no support in pgAdmin". No > support for what? Editable views. That's the reason that i made a table out of it instead of a view. The "pretty" version is a view, but you can't edit the comment. Cheers, WBL -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
On Fri, 2011-10-14 at 14:55 +0200, Willy-Bas Loos wrote: > On Fri, Oct 14, 2011 at 2:51 PM, Guillaume Lelarge > <guillaume@lelarge.info> wrote: > >> When you edit the description in the table (or the view, but no > >> support in pgAdmin), the comment in the system tables is updated also. > > > I'm not sure I understand your comment: "no support in pgAdmin". No > > support for what? > > Editable views. > That's the reason that i made a table out of it instead of a view. > The "pretty" version is a view, but you can't edit the comment. > Not sure which pgAdmin release you use, but 1.14 can edit comments on an already existing views. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Fri, Oct 14, 2011 at 3:38 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > Not sure which pgAdmin release you use, but 1.14 can edit comments on an > already existing views. Of course it supports editting comments on the view itself, but that's not what i mean. I have a view that shows the comments on all the tables in the database. There is an _UPDATE rule on the view that would update the comment of the other tables, but pgAdmin will not let me update any data in that view. Cheers, WBL -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
On Fri, 2011-10-14 at 15:59 +0200, Willy-Bas Loos wrote: > On Fri, Oct 14, 2011 at 3:38 PM, Guillaume Lelarge > <guillaume@lelarge.info> wrote: > > Not sure which pgAdmin release you use, but 1.14 can edit comments on an > > already existing views. > > Of course it supports editting comments on the view itself, but that's > not what i mean. > I have a view that shows the comments on all the tables in the database. > There is an _UPDATE rule on the view that would update the comment of > the other tables, but pgAdmin will not let me update any data in that > view. > Oh yes, that's right. Not much we can do about it. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Fri, Oct 14, 2011 at 6:20 AM, Willy-Bas Loos <willybas@gmail.com> wrote: > 1. I think that there is no such information in the system tables. is > that correct? Yes. It's been discussed before but some people (particularly, Tom, IIRC) are not convinced that it's useful enough to justify its existence. > 2. i would like to go back in time. I think that i will just look up > the creation date for the files in the data directory and translate > their oid's to the object names and then update their dates. This > would of course only work from the last restore. Is that a good way to > do it? Well, that timestamp will get bumped on TRUNCATE, CLUSTER, VACUUM FULL, and rewriting versions of ALTER TABLE. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company