PostgreSQL 9.1.3 plpgsql debugger module

Today, a netizen asked PostgreSQL 9.0 how to debug functions in the group. I remember there was a plug-in named edb-debugger in 8.3 that could be used. there is no 9.0 database at hand, so I tested it and it could not be used at 9.1. The result is that the compilation failed.

Finally, the solution is found. The record is as follows.

There is an open-source edb-debugger plug-in pgfoundry that can be used to debug PostgreSQL PLPGSQL functions.

Http://pgfoundry.org/projects/edb-debugger/

However, this version is too old and cannot be compiled in PostgreSQL 9.1. The error is as follows.

Make

Makefile:63: warning: overriding commands for target `install'../../src/makefiles/pgxs.mk:120: warning: ignoring old commands for target `install'Makefile:77: warning: overriding commands for target `installdirs'../../src/makefiles/pgxs.mk:150: warning: ignoring old commands for target `installdirs'gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic  -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pldbgapi.o pldbgapi.cpldbgapi.c: In function ‘pldbg_attach_to_port’:pldbgapi.c:346: warning: implicit declaration of function ‘MAKE_OFFSET’pldbgapi.c: In function ‘pldbg_wait_for_target’:pldbgapi.c:474: warning: implicit declaration of function ‘SHM_OFFSET_VALID’pldbgapi.c:476: warning: implicit declaration of function ‘MAKE_PTR’pldbgapi.c:476: warning: cast to pointer from integer of different sizegcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic  -L../../src/port  -Wl,-rpath,'/opt/pgsql/lib',--enable-new-dtags  -shared -o pldbgapi.so pldbgapi.ogcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic  -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2   -c -o targetinfo.o targetinfo.ctargetinfo.c: In function ‘getTriggerFuncOid’:targetinfo.c:268: error: ‘SnapshotNow’ undeclared (first use in this function)targetinfo.c:268: error: (Each undeclared identifier is reported only oncetargetinfo.c:268: error: for each function it appears in.)targetinfo.c: In function ‘getProcOidBySig’:targetinfo.c:508: error: too few arguments to function ‘FuncnameGetCandidates’targetinfo.c: In function ‘getProcOidByName’:targetinfo.c:555: error: too few arguments to function ‘FuncnameGetCandidates’make: *** [targetinfo.o] Error 1rm pldbgapi.o

I don't know why I won't update it. The author is the two.

Korry Douglas (korry.douglas@enterprisedb.com)Dave Page (dave.page@enterprisedb.com)

The EDB-AS version released by EDB contains the debugger. it is not available to directly copy the so file to the open-source PostgreSQL version. for example, I copied the $ libdir/plugins/plugin_debugger.so file of EDB-AS 9.1.2.2 to the open source PostgreSQL $ PGHOME/lib/plugins/directory. configure postgresql. conf

shared_preload_libraries = '$libdir/plugins/plugin_debugger'

When the database is started, the following error is reported:

FATAL:  could not load library "/opt/pgsql/lib/plugins/plugin_debugger.so": /opt/pgsql/lib/plugins/plugin_debugger.so: undefined symbol: NonSPLFunctionContext

This NonSPLFunctionContext in PostgresPlus/9.1AS/include/server/utils/elog. h file. but copy it to the/opt/pgsql/include/server/utils/elog of the open-source PostgreSQL. h later.
Although only version 0.9.3 is available for download in pgfoundry, we are glad that the latest version is available in cvs. see the reference link. After downloading these files, put them in the contrib directory of the source code to create a new debugger directory. compilation process:

su - root. /home/postgres/.bash_profilecd /opt/soft_bak/postgresql-9.1.3/contrib/debugger/makemake install

Modify the database configuration file:

vi $PGDATA/postgresql.confshared_preload_libraries = '$libdir/plugins/plugin_debugger'

Restart the database and use the superuser to install functions and classes in the database to be debugged.

psql -h 127.0.0.1 digoal postgres -f /opt/pgsql/share/contrib/pldbgapi.sql CREATE TYPECREATE TYPECREATE TYPECREATE TYPECREATE TYPECREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTION

Create a test function:

create or replace function debugger_test (i int) returns int as $$declarev_result int;beginv_result := 0;if i<0 then  raise notice 'Please enter i >=0.';  raise exception '';end if;for x in 0..i loopv_result := v_result + x;end loop;return v_result;exceptionwhen others then  v_result := 0;  return v_result;end;$$ language plpgsql;

Use pgAdmin to log on to the database. When you right-click the function, the debugging option is available.
A debugging page: