Thread: Object create date



On Mon, Dec 29, 2008 at 11:23 AM, Fernando Hevia <fhevia@ip-tel.com.ar> wrote:
> Hi list,
>
> I'm having a hard time trying to find out if the latest patches have been
> applied to my application (uses lots of pgplsql functions).
> Does Postgres store creation date and/or modification date for tables,
> functions and other objects?
> It would help me a lot if I could query each object when it was created. Is
> this information available on 8.3? Where should I look?

PostreSQL doesn't track this kind of thing for you.  An easy method to
implement yourself is to create a table to track such changes, and add
a line to insert data into that table.

create table change_track (version numeric(12,2) primary key, title
text, summary text);

Then in a script, always update like so:

begin;
insert into change_track(10.2, 'plpgsql - add / remove','New plpgsql
stored procedure to add and remove users.  adduser(uid,''username''),
deluser(uid)');

create function....

commit;

That way, if some part of the update fails it all fails and you don't
have any of it in your db.

Then you can just check change_track to see what stuff is in your db.
Plus you can check the scripts into svn for management.

Fernando Hevia escribió:

Hi,

> I'm having a hard time trying to find out if the latest patches have
> been applied to my application (uses lots of pgplsql functions).
> Does Postgres store creation date and/or modification date for tables,
> functions and other objects?

No.

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

1. not exactly what you were looking for, but i answer this partially by putting a commented-out CVS expansion tag
(e.g.$Id:) in the body of the function so that it gets into the catalog and can be searched:
 
 CREATE OR REPLACE FUNCTION foo () RETURNS void AS $BODY$ -- $Id: foo.sql,v 1.6 2008/12/23 00:06:52 gpavlov Exp $ BEGIN
...

and query it by something like this:
 select   routine_name,   substring(routine_definition from E'%#\042-- #\044Id: % Exp #\044#\042%' for '#') as cvs_id
frominformation_schema.routines ;
 

2. you can also make some inference about the relative timing of object creation based on the OIDs (query
pg_catalog.pg_procrather than information_schema.routines for proc OIDs).
 



> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of Fernando Hevia
> Sent: Monday, December 29, 2008 10:23 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Object create date
> 
> Hi list,
> 
> I'm having a hard time trying to find out if the latest patches have
> been applied to my application (uses lots of pgplsql functions).
> Does Postgres store creation date and/or modification date for tables,
> functions and other objects?
> It would help me a lot if I could query each object when it was
> created. Is this information available on 8.3? Where should I look?
> 
> Thanks,
> Fernando
> 

> -----Mensaje original-----
> De: pgsql-sql-owner@postgresql.org 
> [mailto:pgsql-sql-owner@postgresql.org] En nombre de Scott Marlowe
> 
> On Mon, Dec 29, 2008 at 11:23 AM, Fernando Hevia 
> <fhevia@ip-tel.com.ar> wrote:
> > Hi list,
> >
> > I'm having a hard time trying to find out if the latest 
> patches have 
> > been applied to my application (uses lots of pgplsql functions).
> > Does Postgres store creation date and/or modification date 
> for tables, 
> > functions and other objects?
> > It would help me a lot if I could query each object when it was 
> > created. Is this information available on 8.3? Where should I look?
> 
> PostreSQL doesn't track this kind of thing for you.  

Too bad it doesn't. I think it would be quite useful that the database saved
the creation time of at least some objects.


> An easy method to implement yourself is to create a table to track 
> such changes, and add a line to insert data into that table.
> 
> create table change_track (version numeric(12,2) primary key, 
> title text, summary text);
> 
> Then in a script, always update like so:
> 
> begin;
> insert into change_track(10.2, 'plpgsql - add / remove','New 
> plpgsql stored procedure to add and remove users.  
> adduser(uid,''username''), deluser(uid)');
> 
> create function....
> 
> commit;
> 

Although it's not a solution for an already messed-up database it is an
interesting solution to consider for the future.
Thanks Scott.

Regards,
Fernando.

Thanks Pavlov for your response.

> -----Mensaje original-----
> De: George Pavlov [mailto:gpavlov@mynewplace.com]
> <fhevia@ip-tel.com.ar> wrote:
> > Hi list,
> >
> > I'm having a hard time trying to find out if the latest
> patches have
> > been applied to my application (uses lots of pgplsql functions).
> > Does Postgres store creation date and/or modification date
> for tables,
> > functions and other objects?
> > It would help me a lot if I could query each object when it was
> > created. Is this information available on 8.3? Where should I look?
>
> 1. not exactly what you were looking for, but i answer this
> partially by putting a commented-out CVS expansion tag (e.g.
> $Id:) in the body of the function so that it gets into the
> catalog and can be searched:
>
>   CREATE OR REPLACE FUNCTION foo ()
>   RETURNS void AS
>   $BODY$
>   -- $Id: foo.sql,v 1.6 2008/12/23 00:06:52 gpavlov Exp $
>   BEGIN
>   ...
>

I am already doing this. Sadly I've found it to be very fragile in face of a careless programmer who forgets to update
thetags. Myself being the prime suspect. :) 


> and query it by something like this:
>
>   select
>     routine_name,
>     substring(routine_definition from E'%#\042-- #\044Id: %
> Exp #\044#\042%' for '#') as cvs_id
>   from information_schema.routines
>   ;

This query is very helpful.


>
> 2. you can also make some inference about the relative timing
> of object creation based on the OIDs (query
> pg_catalog.pg_proc rather than information_schema.routines
> for proc OIDs).
>

I am not sure this would be helpful since different databases are involved (same product on several installations).
I think that with the above query I will be able to sort things out.
Thank you.

Regards,
Fernando.

Fernando Hevia escribió:
> Thanks Pavlov for your response.

> >   CREATE OR REPLACE FUNCTION foo ()
> >   RETURNS void AS
> >   $BODY$
> >   -- $Id: foo.sql,v 1.6 2008/12/23 00:06:52 gpavlov Exp $
> >   BEGIN
> >   ...
> > 
> 
> I am already doing this. Sadly I've found it to be very fragile in
> face of a careless programmer who forgets to update the tags. Myself
> being the prime suspect. :)

You don't update the tags.  They are updated automatically by CVS (or
Subversion, whatever you use)

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

> -----Mensaje original-----
> De: Alvaro Herrera [mailto:alvherre@commandprompt.com]
> Enviado el: Lunes, 29 de Diciembre de 2008 19:39
> Para: Fernando Hevia
> CC: 'George Pavlov'; pgsql-sql@postgresql.org
> Asunto: Re: [SQL] Object create date
>
> Fernando Hevia escribió:
> > Thanks Pavlov for your response.
>
> > >   CREATE OR REPLACE FUNCTION foo ()
> > >   RETURNS void AS
> > >   $BODY$
> > >   -- $Id: foo.sql,v 1.6 2008/12/23 00:06:52 gpavlov Exp $
> > >   BEGIN
> > >   ...
> > >
> >
> > I am already doing this. Sadly I've found it to be very fragile in
> > face of a careless programmer who forgets to update the
> tags. Myself
> > being the prime suspect. :)
>
> You don't update the tags.  They are updated automatically by
> CVS (or Subversion, whatever you use)
>

Hmm, I'm using source-safe. Just went through the manual and it does support
auto expandable tags, something which I hadn't used before. Thanks for the
tip.

On Dec 29, 2008, at 12:30 PM, George Pavlov wrote:

> 1. not exactly what you were looking for, but i answer this  
> partially by putting a commented-out CVS expansion tag (e.g. $Id:)  
> in the body of the function so that it gets into the catalog and can  
> be searched:
>
>  CREATE OR REPLACE FUNCTION foo ()
>  RETURNS void AS
>  $BODY$
>  -- $Id: foo.sql,v 1.6 2008/12/23 00:06:52 gpavlov Exp $
>  BEGIN
>  ...
>
> and query it by something like this:
>
>  select
>    routine_name,
>    substring(routine_definition from E'%#\042-- #\044Id: % Exp #\044# 
> \042%' for '#') as cvs_id
>  from information_schema.routines
>  ;
>
> 2. you can also make some inference about the relative timing of  
> object creation based on the OIDs (query pg_catalog.pg_proc rather  
> than information_schema.routines for proc OIDs).

Hmm...  It seems to me that since object creation time, being  
metadata, would be better served being placed in a COMMENT for the  
object.  That would have the added bonus of being able to search in  
one place (pg_description) across all objects of all types for a given  
creation/modification date.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

Hi

About three years ago we started doing nightly schema dumps of all our databases and then updating these into CVS. That has proven to be pretty useful when there is need to determine when some change was done in database.

regards,
Asko