pl/pgsql debuging, was Re: debugging C functions

This post got me thinking, is there a similar procedure for PL/pgSQL functions?

---
David Gardner, IT
The Yucaipa Companies
(310) 228-2855

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Joe Conway
Sent: Friday, June 01, 2007 9:00 PM
To: Islam Hegazy
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] debugging C functions

Islam Hegazy wrote:
> I wrote a C function to call from PostgreSQL8.2.4 under Linux. The
> functions returns unexpected results. I did an extensive analysis to the
> function and it seems correct. I want to know if there is a way to debug
> C functions that are passed to PostgreSQL.

Yes. Something along these lines (where plr.so is an example shared
object library with a function called throw_notice installed in a
database called contrib_regression):

1. Build and install your function. Ensure both postgres and your
library are built with debug symbols (--enable-debug)

2. start a psql session in the database where your function has
been created

#psql contrib_regression

3. Load the shared object library in psql

contrib_regression=# load '$libdir/plr';
LOAD

4. Start another console and determine the PID for the backend
session (this will wrap poorly -- I'll do my best to make it
readable)

ps -ef | grep postgres

postgres 24496 1 0 18:23 ? 00:00:00
/usr/local/pgsql-dev/bin/postgres -D /opt/data/pgsql/data -p
65432 -i -F
postgres 24498 24496 0 18:23 ? 00:00:00
postgres: writer process
postgres 24499 24496 0 18:23 ? 00:00:00
postgres: stats collector process
postgres 24500 24496 0 18:23 ? 00:00:00
postgres: autovacuum launcher process
postgres 31233 24496 1 20:37 ? 00:00:00
postgres: postgres contrib_regression [local] idle

You want the PID associated with the idle session -- 31233

5. Run gdb and attach to the backend in question

gdb /usr/local/pgsql-dev/bin/postgres 31233

6. Set breakpoints, etc, and then continue the gdb session

[...]
Reading symbols from
/usr/lib64/R/library/stats/libs/stats.so...done.
Loaded symbols for /usr/lib64/R/library/stats/libs/stats.so
0x0000003fef4cdf45 in recv () from /lib64/libc.so.6
(gdb) break throw_notice
Breakpoint 1 at 0x636cb7: file plr.c, line 2908.
(gdb) continue
Continuing.

7. Return to the psql session, run your function

contrib_regression=# select throw_notice('hello');

8. Return to gdb session, debug away...

HTH,

Joe

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq