Thread: 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