[HACKERS] register creation date of table

From: Willy-Bas Loos <willybas(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: [HACKERS] register creation date of table
Date: 2011-10-14 10:20:12
Message-ID: CAHnozTg4=j_F0ON4B7CmVwtjL-Jq4SOauDD5h93+fX4=OwdUBA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

Attachment Content-Type Size
tabellen_registreren_compleet01(20111014).sql text/x-sql 5.5 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kalai R 2011-10-14 10:25:04 dll files missing in postgrsql bin folder in Windows
Previous Message Janning Vygen 2011-10-14 10:01:45 Client hangs in socket read

Browse pgsql-hackers by date

  From Date Subject
Next Message Thom Brown 2011-10-14 11:12:53 VACUUM touching file but not updating relation
Previous Message Cédric Villemain 2011-10-14 08:44:20 Re: patch for new feature: Buffer Cache Hibernation