pl/pgsql debuging, was Re: debugging C functions - Mailing list pgsql-general

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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joe Conway
Sent: Friday, June 01, 2007 9:00 PM
To: Islam Hegazy
Cc: pgsql-general@postgresql.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