How to monitor the progress of a stored procedure?
(Sorry for asking so many questions!)
What techniques can one use to monitor the progress of a stored procedure?
Specifically, how can I get the procedure to print a progress indicator
message to the screen every once in a while?
I have a stored procedure that has been running for a very long time.
Before doing the current "real" run, I tested the procedure on a toy case
and confirmed that it produced the right result. So I know that when it
finishes it will be OK, but I can't tell whether it will finish in an hour
or in a century... I have no idea of how far along it is. (In case it
matters, the procedure is written in PL/perl. I tried to use Perl's print
statement, unbuffered, to print out a message periodically from within the
procedure, but I see no output.)
Thanks in advance,
Kynn
On Feb 27, 2008, at 5:25 PM, Kynn Jones wrote:
(Sorry for asking so many questions!)
What techniques can one use to monitor the progress of a stored
procedure?Specifically, how can I get the procedure to print a progress
indicator message to the screen every once in a while?I have a stored procedure that has been running for a very long
time. Before doing the current "real" run, I tested the procedure
on a toy case and confirmed that it produced the right result. So
I know that when it finishes it will be OK, but I can't tell
whether it will finish in an hour or in a century... I have no
idea of how far along it is. (In case it matters, the procedure is
written in PL/perl. I tried to use Perl's print statement,
unbuffered, to print out a message periodically from within the
procedure, but I see no output.)Thanks in advance,
Read up on this chapter: http://www.postgresql.org/docs/8.2/
interactive/plpgsql-errors-and-messages.html
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
Kynn Jones wrote:
I have a stored procedure that has been running for a very long time.
Before doing the current "real" run, I tested the procedure on a toy
case and confirmed that it produced the right result. So I know that
when it finishes it will be OK, but I can't tell whether it will finish
in an hour or in a century... I have no idea of how far along it is.
(In case it matters, the procedure is written in PL/perl. I tried to
use Perl's print statement, unbuffered, to print out a message
periodically from within the procedure, but I see no output.)
When I print() from PL/Perl, the messages show up in the PostgreSQL log
file. It's not an obvious place, but it makes sense because PL/Perl
functions aren't always called from interactive terminals. Did you look
there?
Colin
Kynn Jones wrote:
(Sorry for asking so many questions!)
What techniques can one use to monitor the progress of a stored
procedure?Specifically, how can I get the procedure to print a progress
indicator message to the screen every once in a while?[...](In case it matters, the procedure is written in PL/perl. I
tried to use Perl's print statement, unbuffered, to print out a
message periodically from within the procedure, but I see no output.)
elog? (see example in
http://www.postgresql.org/docs/8.3/interactive/plperl-database.html)
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au