Skip site navigation (1) Skip section navigation (2)

Re: Last modification date for Triggers, functions, tables ....

From: Bob McConnell <rmcconne(at)lightlink(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Last modification date for Triggers, functions, tables ....
Date: 2010-05-01 11:40:39
Message-ID: 4BDC1337.7030203@lightlink.com (view raw or flat)
Thread:
Lists: pgsql-novice
Didier Gasser-Morlay wrote:
> On 30 April 2010 20:40, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 
>> Didier Gasser-Morlay <didiergm(at)gmail(dot)com> writes:
>>> Is there a way to determine the last modification date of the script of a
>>> function or trigger,
>>> Same question for relations and attributes  ?
>>> No, that's not tracked.  You could get a rough idea from the xmin of the
>>> relevant catalog rows, but we don't store actual timestamps.
>>>                       regards, tom lane
> 
> Too bad, I'll have to find another way then,

That's what Source Control Management and Content Management Systems 
were designed for. Don't run any SQL on a production system until it has 
been tested and checked into your SCM.

The other half of that is to add a table to the database which tracks 
the version, sub-version and patch level of the database. Updating it is 
the last step in any schema change, and one column contains the 
timestamp when the update was applied. You can also add pointers back to 
the version tracking in your SCM. I use variations of this:

-----8<----------------------------------------------
CREATE TABLE schema_changes(
    id serial PRIMARY KEY,
    majorrelease varchar(2) NOT NULL,
    minorrelease varchar(2) NOT NULL,
    pointrelease varchar(4) NOT NULL,
    patch varchar(4) NOT NULL,
    scriptname varchar(50) NOT NULL,
    dateapplied timestamp without time zone NOT NULL
);

-- The first baseline schema script should, as the last step, officially
-- install version 1.0 of the database:

INSERT INTO schema_changes (
        majorrelease
        ,minorrelease
        ,pointrelease
        ,patch
        ,scriptname
        ,dateapplied
        )
VALUES (
        '01'
        ,'00'
        ,'0000'
        ,'0000'
        ,'recipedb.pgsql'
        ,now()
        );
-----8<----------------------------------------------

Bob McConnell
N2SPP

In response to

pgsql-novice by date

Next:From: Leif Biberg KristensenDate: 2010-05-01 11:43:44
Subject: Re: returning more than one value from a function
Previous:From: Didier Gasser-MorlayDate: 2010-05-01 08:53:01
Subject: Re: Last modification date for Triggers, functions, tables ....

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group