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

Hi,

Is there a way to determine the last modification date of the script of a function or trigger,
Same question for relations and attributes  ?

My purpose would be to be able to list all DDL changes having occurred since a certain date, that would be very useful to me during application development with a fast evolving db schema.

Thanks

Didier

Didier Gasser-Morlay <didiergm@gmail.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,

thanks

regards, Didier

On 2010-04-30, Didier Gasser-Morlay <didiergm@gmail.com> wrote:
> --001636833a443f05eb0485777373
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi,
>
> Is there a way to determine the last modification date of the script of a
> function or trigger,
> Same question for relations and attributes  ?

AFAIK no built-in way.

> My purpose would be to be able to list all DDL changes having occurred since
> a certain date, that would be very useful to me during application
> development with a fast evolving db schema.

if you do nightly --schema-only  snapshots using pg_dump and store them over
each other in source code managemnt system (git, subversion, CVS, RCS,
codesafe - what-ever)

Then you can pull a diff from that and see the changed.

Didier Gasser-Morlay wrote:
> On 30 April 2010 20:40, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Didier Gasser-Morlay <didiergm@gmail.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

Jasen Betts wrote:
> On 2010-04-30, Didier Gasser-Morlay <didiergm@gmail.com> wrote:
>
>> --001636833a443f05eb0485777373
>> Content-Type: text/plain; charset=ISO-8859-1
>>
>> Hi,
>>
>> Is there a way to determine the last modification date of the script of a
>> function or trigger,
>> Same question for relations and attributes  ?
>>
>
> AFAIK no built-in way.
>
>
>> My purpose would be to be able to list all DDL changes having occurred since
>> a certain date, that would be very useful to me during application
>> development with a fast evolving db schema.
>>
>
> if you do nightly --schema-only  snapshots using pg_dump and store them over
> each other in source code managemnt system (git, subversion, CVS, RCS,
> codesafe - what-ever)
>
> Then you can pull a diff from that and see the changed.
>
>
Actually, there is an open source application called 'post facto' which
takes two postgres schemas and produces an SQL change set of operations
which will transform one into the other.  You might want to run this
nightly and incorporate it into your version control system.

Mary

Jasen, Mary

Thanks for the ideas and pointers, I'll check 'Post Facto' out

Didier