--_000_2594739A5DA9FB439DA88E61EA12430114420EF80FDFW1MXM01RA CK_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
It would really be nice if pg_class were altered to add two new columns: re=
lcreatedat & relmoddat.
Both would be of type timestamp. relcreatedat would contain the timestamp t=
he object was created. Likewise, relmoddat would contain the last timestamp=
the object was altered / modified.
This would be very useful for user tables that are meant to exist for only =
a specific length of time. eg: 1 month.
Then a simple:
SELECT relname
FROM pg_class
WHERE current_timestamp::date - relcreatedat::date > 30
AND relname LIKE 'tmp_%';
Would show possible expired tables.
Likewise, relmoddat would be useful for determining when someone made chang=
es to a table, index, view, etc.
Melvin Davidson - DBA
501-3586
Confidentiality Notice: This e-mail message (including any attached or
embedded documents) is intended for the exclusive and confidential use of t=
he
individual or entity to which this message is addressed, and unless otherwi=
se
expressly indicated, is confidential and privileged information of Rackspac=
e.
Any dissemination, distribution or copying of the enclosed material is proh=
ibited.
If you receive this transmission in error, please notify us immediately by =
e-mail
at abuse [at] rackspace.com, and delete the original message.
Your cooperation is appreciated.
--_000_2594739A5DA9FB439DA88E61EA12430114420EF80FDFW1MXM01RA CK_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
[at] font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
[at] font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:black;}
..MsoChpDefault
{mso-style-type:export-only;}
[at] page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3DEN-US link=3Dblue vlink=3Dpurple>
<div class=3DSection1>
<p class=3DMsoNormal><span style=3D'color:black'>It would really be nice if
pg_class were altered to add two new columns: relcreatedat & relmoddat.=
<o:p></o:p></span></p>
<p class=3DMsoNormal><span style=3D'color:black'>Both would be of type time=
stamp.
relcreatedat would contain the timestamp the object was created. Likewise, =
relmoddat
would contain the last timestamp the object was altered / modified.<o:p></o=
:p></span></p>
<p class=3DMsoNormal><span style=3D'color:black'><o:p> </o:p></span></=
p>
<p class=3DMsoNormal><span style=3D'color:black'>This would be very useful =
for user
tables that are meant to exist for only a specific length of time. eg: 1&nb=
sp;
month.<o:p></o:p></span></p>
<p class=3DMsoNormal><span style=3D'color:black'>Then a simple:<o:p></o:p><=
/span></p>
<p class=3DMsoNormal><span style=3D'color:black'>SELECT relname<o:p></o:p><=
/span></p>
<p class=3DMsoNormal><span style=3D'color:black'> FROM pg_class <o:p><=
/o:p></span></p>
<p class=3DMsoNormal><span style=3D'color:black'>WHERE current_timestamp::d=
ate - relcreatedat::date
> 30<o:p></o:p></span></p>
<p class=3DMsoNormal><span style=3D'color:black'> A=
ND
relname LIKE ‘tmp_%’;<o:p></o:p></span></p>
<p class=3DMsoNormal><span style=3D'color:black'><o:p> </o:p></span></=
p>
<p class=3DMsoNormal><span style=3D'color:black'>Would show possible expired
tables.<o:p></o:p></span></p>
<p class=3DMsoNormal><span style=3D'color:black'><o:p> </o:p></span></=
p>
<p class=3DMsoNormal><span style=3D'color:black'>Likewise, relmoddat would =
be
useful for determining when someone made changes to a table, index, view, e=
tc.<o:p></o:p></span></p>
<p class=3DMsoNormal><span style=3D'color:black'><o:p> </o:p></span></=
p>
<p class=3DMsoNormal><b><span style=3D'font-size:10.5pt;font-family:Consola=
s;
color:blue'>Melvin Davidson – DBA<o:p></o:p></span></b></p>
<p class=3DMsoNormal><b><span style=3D'font-size:10.5pt;font-family:Consola=
s;
color:blue'>501-3586</span></b><b><span style=3D'color:blue'><o:p></o:p></s=
pan></b></p>
<p class=3DMsoNormal><o:p> </o:p></p>
</div>
<PRE>
Confidentiality Notice: This e-mail message (including any attached or
embedded documents) is intended for the exclusive and confidential use of t=
he
individual or entity to which this message is addressed, and unless otherwi=
se
expressly indicated, is confidential and privileged information of Rackspac=
e.
Any dissemination, distribution or copying of the enclosed material is proh=
ibited.
If you receive this transmission in error, please notify us immediately by =
e-mail
at abuse [at] rackspace.com, and delete the original message.
Your cooperation is appreciated.
</PRE></body>
</html>
--_000_2594739A5DA9FB439DA88E61EA12430114420EF80FDFW1MXM01RA CK_--
