Re: Postmaster Uptime

Following on from Lee's mail I have written the following two functions to
return the uptime. I had a look at the other pl's and pl/sh seemed to be
the easiest to route to take. PostgreSQL's internal time/date routines do a
lot of the heavy lifting in re-arranging the date output from the ps command
into a timestamp.

CREATE FUNCTION postmasterstarttime() RETURNS timestamp AS '
#!/bin/bash
ps -o lstart,command | grep ''[p]ostmaster'' | cut -c 5-24
' LANGUAGE 'plsh';

CREATE FUNCTION uptime() RETURNS interval AS '
SELECT current_timestamp - postmasterstarttime();
' LANGUAGE 'sql';

A simple 'SELECT uptime();' should return

uptime
------------------------
2 days 04:40:24.016406
(1 row)

I have tried this on a Linux Mandrake 8.1 box and it seems to work fine. I'd
imagine that the output of ps may need to be tweaked for different platforms
depending on the version of ps used (I have procps version 2.0.7).

Best Regards,

Tim Knowles

-----Original Message-----
From: Lee Kindness [mailto:lkindness(at)csl(dot)co(dot)uk]
Sent: 25 September 2002 10:39
To: Tim Knowles
Cc: Lee Kindness; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Postmaster Uptime

Lee Kindness writes:
> As others have said it is certainly do-able, but you're going to have
> to do it! In the past you've got the uptime using ps - what you need
> to do is wrap this into a script and add it to a database as a
> function (using pg/tcl, pg/perlu, whatever). Or you could do it as a C
> function...

Actually pl/sh would be the obvious choice:

http://www.ca.postgresql.org/~petere/plsh.html

Lee.