Has anyone tried out the PL/pgSQL debugger?

Hi Korry,

On Tue, 2007-09-04 at 10:07 -0400, korry.douglas wrote:

Now that we've "announced"

Could you please define "we"? Is it "EDB" or "PostgreSQL" ? I'm asking
this per a thread @ -advocacy list.

Cheers,
--
Devrim GÜNDÜZ
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/

korry.douglas a �crit :

Now that we've "announced" (see
http://www.informationweek.com/news/showArticle.jhtml?articleID=201803375&subSection=News)
that 8.3 will include a debugger (don't worry, it's a PL/pgSQL debugger
:-), has anyone actually tried it yet (other than myself and Dave Page)?

I did :)

but with an 8.2 PostgreSQL server.

I would appreciate any feedback (preferably *before* 8.3 hits the
streets). You can find the debugger at:
http://pgfoundry.org/projects/edb-debugger

I'll try to work more on it and to use the devel server.

Regards.

--
Guillaume.
<!-- http://abs.traduc.org/
http://lfs.traduc.org/
http://docs.postgresqlfr.org/ -->

Ugaa...Sorry I don't have the margin of time.:-(
However, I examined it as EDB....

Regards,
Hiroshi Saito

From: "Guillaume Lelarge" <guillaume@lelarge.info>

Devrim GÜNDÜZ wrote:

Hi Korry,

On Tue, 2007-09-04 at 10:07 -0400, korry.douglas wrote:

Now that we've "announced"

Could you please define "we"? Is it "EDB" or "PostgreSQL" ? I'm asking
this per a thread @ -advocacy list.

We, EDB. Though I'm not sure it was so much announced as
mentioned-in-passing in all honesty.

In any case, it's a plugin for PostgreSQL 8.2 and above that allows you
to debug pl/pgsql functions using pgAdmin. You can step through
functions, step into or over function calls, set breakpoints, examine
variable values etc - all the normal stuff you expect from a debugger.
You can also directly debug a selected function, or set a breakpoint on
one and wait for your app to hit the breakpoint so you can debug it in
context.

/D

Dave Page wrote:

Devrim GÜNDÜZ wrote:

Hi Korry,

On Tue, 2007-09-04 at 10:07 -0400, korry.douglas wrote:

Now that we've "announced"

Could you please define "we"? Is it "EDB" or "PostgreSQL" ? I'm asking
this per a thread @ -advocacy list.

We, EDB. Though I'm not sure it was so much announced as
mentioned-in-passing in all honesty.

Or maybe in second thought Korry was actually referring the Information
Week article...

/D

Hi Dave,

On Tue, 2007-09-04 at 15:55 +0100, Dave Page wrote:

We, EDB. Though I'm not sure it was so much announced as
mentioned-in-passing in all honesty.

I was referring to the source of the information in that article,
Dave :)

In any case, it's a plugin for PostgreSQL 8.2 and above that allows
you to debug pl/pgsql functions using pgAdmin.

Yes I know and spent a bit time for testing it -- but not much.

Cheers,.
--
Devrim GÜNDÜZ
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/

Now that we've "announced"

Could you please define "we"? Is it "EDB" or "PostgreSQL" ? I'm asking
this per a thread @ -advocacy list.

That -advocacy thread is what got me started. I was referring to the
interview in InformationWeek - that's a PG-related interview not an
EDB-related interview.

I've heard a few people mention that they plan to include the PL/pgSQL
debugger in a PG 8.3 release announcement, I just want to make sure that
it gets a little exercise before we talk it up too much.

-- Korry

Hello Korry

I test it and with pgAdmin and sent some bug reports (pgAdmin had some
problems not debugger).

Is it available without pgAdmin? Debugger works well, but integration
with pgAdmin is knotty now. Some points

1. can be integrated into psql?

2. can be started from query execution (with breakpoint). Now I have
to expliciltly start debugged function.

Regards
Pavel Stehule

2007/9/4, korry.douglas <korry.douglas@enterprisedb.com>:

In any case, it's a plugin for PostgreSQL 8.2 and above that allows
you to debug pl/pgsql functions using pgAdmin.

Yes I know and spent a bit time for testing it -- but not much.

Devrim, does that mean that you've tried it and it seemed to work? Did
you try it with 8.2 or 8.3?

-- Korry

Devrim GÜNDÜZ <devrim@CommandPrompt.com> writes:

On Tue, 2007-09-04 at 10:07 -0400, korry.douglas wrote:

Now that we've "announced"

Could you please define "we"? Is it "EDB" or "PostgreSQL" ? I'm asking
this per a thread @ -advocacy list.

It looks like We == Bruce. EDB isn't mentioned in the article, he was
discussing PostgreSQL. So presumably he was speaking on behalf of the postgres
community.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Pavel Stehule wrote:

Hello Korry

I test it and with pgAdmin and sent some bug reports (pgAdmin had some
problems not debugger).

Did you report the issues you had? I don't recall seeing anything.

1. can be integrated into psql?

Not without significant effort I would imagine - it's too interactive.

2. can be started from query execution (with breakpoint). Now I have
to expliciltly start debugged function.

pgAdmin allows you to do it either explicitly or with a global
breakpoint (ie. one that will catch invocation in any backend).

What version of pgAdmin did you test exactly? The global breakpoint code
was maybe unavailable for a few days many months ago, but has been there
for a long time now.

Regards, Dave.

1. can be integrated into psql?

There is an API - I wouldn't want to try to use it from the
command-line, but you certainly can. You would call functions such as:
SELECT * FROM pldbg_set_breakpoint( 'myfunction' );
SELECT * FROM pldbg_wait_for_breakpoint( ... );
SELECT * FROM pldbg_step_into( ... );
SELECT * FROM pldbg_continue( .. .);
....
Since it is driven by an API, you can code up a user interface in
whatever language/environment you like.

2. can be started from query execution (with breakpoint). Now I have
to expliciltly start debugged function.

I'm not quite sure what you mean there. The debugger works in two
different modes:

1) In-context debugging: you set a breakpoint on a function (from
within a tool like pgAdmin) and then invoke that function from some
other client application

2) Direct debugging: you set a breakpoint on a function and the
debugger invokes that function on your behalf.

In pgAdmin, you start an in-context debugging by choosing the "Set
Breakpoint" (or perhaps "Set Global Breakpoint") option and you start a
direct debugging session by choosing the "Debug Function" option (sorry,
I don't have a copy of pgAdmin in front of me so I may have the spelling
wrong in there somewhere).

-- Korry

Dave,

Or maybe in second thought Korry was actually referring the Information
Week article...

Yeah, I think so. The PL/pgSQL debugger was part of a list of 14-15 features
I gave Charles Babcock; not sure why he liked that one, but he did.

Last I talked to Korry, it was ready to go. No?

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

2007/9/4, korry.douglas <korry.douglas@enterprisedb.com>:

1. can be integrated into psql?

There is an API - I wouldn't want to try to use it from the command-line,
but you certainly can. You would call functions such as:
SELECT * FROM pldbg_set_breakpoint( 'myfunction' );
SELECT * FROM pldbg_wait_for_breakpoint( ... );
SELECT * FROM pldbg_step_into( ... );
SELECT * FROM pldbg_continue( .. .);
....
Since it is driven by an API, you can code up a user interface in whatever
language/environment you like.

I tested it without success. I didn't find documentation. There is
some startup sequence, so pldbg functions raised errors.

2. can be started from query execution (with breakpoint). Now I have
to expliciltly start debugged function.

I'm not quite sure what you mean there. The debugger works in two
different modes:

1) In-context debugging: you set a breakpoint on a function (from within a
tool like pgAdmin) and then invoke that function from some other client
application

I mean method 1. I hadn't success with pgAdmin. Breakpoints was ignored.

2) Direct debugging: you set a breakpoint on a function and the debugger
invokes that function on your behalf.

In pgAdmin, you start an in-context debugging by choosing the "Set
Breakpoint" (or perhaps "Set Global Breakpoint") option and you start a
direct debugging session by choosing the "Debug Function" option (sorry, I
don't have a copy of pgAdmin in front of me so I may have the spelling wrong
in there somewhere).

-- Korry

I'll test it again in new beta pgAdmin

Pavel

Josh Berkus wrote:

Dave,

Or maybe in second thought Korry was actually referring the Information
Week article...

Yeah, I think so. The PL/pgSQL debugger was part of a list of 14-15 features
I gave Charles Babcock; not sure why he liked that one, but he did.

Last I talked to Korry, it was ready to go. No?

Yeah, we released the plugin, the client is integrated into pgAdmin 1.8.

Regards, Dave

Pavel Stehule wrote:

1) In-context debugging: you set a breakpoint on a function (from within a
tool like pgAdmin) and then invoke that function from some other client
application

I mean method 1. I hadn't success with pgAdmin. Breakpoints was ignored.

That normally means the debugger plugin hasn't been correctly installed.

From the readme:

- Edit your postgresql.conf file, and modify the
shared_preload_libraries config
option to look like:

shared_preload_libraries = '$libdir/plugins/plugin_debugger.so'

(on some platforms the file extension may differ - for example, on
Windows it will be .dll not .so).

...
...
...

The majority of problems we've encountered with the plugin are caused by
failing to add (or incorrectly adding) the debugger plugin library to
the shared_preload_libraries configuration directive in postgresql.conf
(following which, the server *must* be restarted). This will prevent
global breakpoints working on all platforms, and on some (notably
Windows) may prevent the pldbgapi.sql script from executing correctly.

Regards, Dave.

Yeah, I think so. The PL/pgSQL debugger was part of a list of 14-15 features
I gave Charles Babcock; not sure why he liked that one, but he did.

Last I talked to Korry, it was ready to go. No?

If by "ready to go" you mean "has been exercised by a mess o' people", no.

If by "ready to go" you mean "has been tested by a few people and
compiled on a few platforms", that's where we are now - I would like to
see some more testing (especially on platforms other than Windows,
Linux, and OS X).

The core of the debugger has been in use for quite some time, but I had
to strip out a lot of EDB-specific code and I'd like to see that the
result (the open-source version at pgFoundry) holds up well on other
platforms.

Josh, any chance you could try it out on Solaris?

-- Korry

Apologies - new list user, hit reply not reply all :-/

korry.douglas wrote:

Yeah, I think so. The PL/pgSQL debugger was part of a list of 14-15
features I gave Charles Babcock; not sure why he liked that one, but
he did.

Last I talked to Korry, it was ready to go. No?

If by "ready to go" you mean "has been exercised by a mess o' people", no.
If by "ready to go" you mean "has been tested by a few people and
compiled on a few platforms", that's where we are now - I would like to
see some more testing (especially on platforms other than Windows,
Linux, and OS X).

The core of the debugger has been in use for quite some time, but I had
to strip out a lot of EDB-specific code and I'd like to see that the
result (the open-source version at pgFoundry) holds up well on other
platforms.
Josh, any chance you could try it out on Solaris?

Looks great, and I'll be testing it shortly, but can I ask:
1. For 8.3 are we talking pgFoundry / contrib / core?
2. Would you accept an additional mode: logging?

It would be useful to be able to embed a couple of statements in
application to code to turn logging on/off at specific points in real
usage situations. Nothing as fancy as log4j/c/perl etc. just (timestamp,
facility, level, message) to a table.

SET plpgsql.logging_facilities='any,list,or,asterisk'
SET plpgsql.logging_levels='DEBUG,INFO'
SET plpgsql.logging_tablename='foo'

Hmm - tricky bit would presumably be the logging statement itself. Would
it be possible to keep the overheads low enough without interfering with
plpgsql itself?

--
Richard Huxton
Archonet Ltd

korry.douglas wrote:

Looks great, and I'll be testing it shortly, but can I ask:
1. For 8.3 are we talking pgFoundry / contrib / core?

The server side of the debugger is implemented as a contrib module, but
not distributed with the PG core. You have to get it from pgFoundry,
untar it (which puts it into the contrib tree), then "make install"

The interview made it sound more mainstream. But then it did sound a
little disjointed too.

Do we know if it going to be distributed with pgAdmin on Windows?

2. Would you accept an additional mode: logging?

[snip self]

The debugger is implemented as an instrumentation plugin for PL/pgSQL.
As sort of a "proof-of-concept", I threw together a total of three
instrumentation plugins; the debugger, a PL/pgSQL performance profiler
(which is included in the edb-debugger tarball), and a tracer.

Don't see the tracer, unless I'm missing what you mean.

When I first read your e-mail, I thought you might be looking for the
tracer, but I see that you really want something else. Does the "RAISE
DEBUG" statement not work for you?

Problem with RAISE DEBUGs throughout my plpgsql is that it's either all
on or all off. I'd like something more controllable.

I see you suggest recording the log
output in a table - that would be a very interesting option for the
'log_destination' GUC variable.

I'm not married to logging to a table, it just seemed sensible since the
profiler is doing that. Also, I'm not thinking of this as a way of
logging things permanently, just for debugging purposes.

--
Richard Huxton
Archonet Ltd

The interview made it sound more mainstream. But then it did sound a
little disjointed too.

I suspect that it may go more mainstream in a later release, but it
seems way too late for 8.3.

Do we know if it going to be distributed with pgAdmin on Windows?

The graphical debugger client is part of pgAdmin (on all platforms I
believe - Dave Page can say for sure).

I don't know if the server-side stuff will be available in the Win32
installer - Magnus (or Dave), can you comment?

Don't see the tracer, unless I'm missing what you mean.

You're right, it's not in the edb-debugger tarball. If you think you
might be interested, I can resurrect it.

Problem with RAISE DEBUGs throughout my plpgsql is that it's either
all on or all off. I'd like something more controllable.

How about a PL/pgSQL function such as LOG_MESSAGE( message TEXT, level
ENUM, facility ENUM )? Just a thought...

I can see how a plugin would be helpful though too.

-- Korry

korry.douglas wrote:

Do we know if it going to be distributed with pgAdmin on Windows?

The graphical debugger client is part of pgAdmin (on all platforms I
believe - Dave Page can say for sure).

Yes, it's fully integrated and supported on all platforms supported by
pgAdmin.

I don't know if the server-side stuff will be available in the Win32
installer - Magnus (or Dave), can you comment?

Yes, it will be added when I get back to pgInstaller work in the next
few days (hopefully).

/D

Korry,

If by "ready to go" you mean "has been tested by a few people and
compiled on a few platforms", that's where we are now - I would like to
see some more testing (especially on platforms other than Windows,
Linux, and OS X).

Well, if we don't publicize it at least a little, nobody will try it out.

To be clear, for 8.3 release I'm planning on plugging several projects
which are on pgFoundry and /contrib. We have some *really cool* stuff
that doesn't ship with the core distribution, and we don't make enough
noise about it. This makes MySQL look like they have more features than
us just because they ship the kitchen sink.

The core of the debugger has been in use for quite some time, but I had
to strip out a lot of EDB-specific code and I'd like to see that the
result (the open-source version at pgFoundry) holds up well on other
platforms.

Josh, any chance you could try it out on Solaris?

Yeah, once I get Nevada build 70 working on my laptop.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

korry.douglas wrote:

Don't see the tracer, unless I'm missing what you mean.

You're right, it's not in the edb-debugger tarball. If you think you
might be interested, I can resurrect it.

Hmm - it's not that the plpgsql I write has complex control-flows, but
it might prove useful to others.

Problem with RAISE DEBUGs throughout my plpgsql is that it's either
all on or all off. I'd like something more controllable.

How about a PL/pgSQL function such as LOG_MESSAGE( message TEXT, level
ENUM, facility ENUM )? Just a thought...

I do, but then comment them out when not testing. Perhaps I'm optimising
prematurely though. I'll have to run some timing tests...

--
Richard Huxton
Archonet Ltd

Gregory Stark wrote:

Devrim G?ND?Z <devrim@CommandPrompt.com> writes:

On Tue, 2007-09-04 at 10:07 -0400, korry.douglas wrote:

Now that we've "announced"

Could you please define "we"? Is it "EDB" or "PostgreSQL" ? I'm asking
this per a thread @ -advocacy list.

It looks like We == Bruce. EDB isn't mentioned in the article, he was
discussing PostgreSQL. So presumably he was speaking on behalf of the postgres
community.

For the record, though the article said I mentioned it, Josh Berkus said
it was he who mentioned it in a phone call to the author.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Josh Berkus wrote:

Dave,

Or maybe in second thought Korry was actually referring the Information
Week article...

Yeah, I think so. The PL/pgSQL debugger was part of a list of 14-15 features
I gave Charles Babcock; not sure why he liked that one, but he did.

Yea, it is very hard to guess what information an author will grab on
to.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Sep 4, 2007, at 1:01 PM, korry.douglas wrote:

The core of the debugger has been in use for quite some time, but I
had to strip out a lot of EDB-specific code and I'd like to see
that the result (the open-source version at pgFoundry) holds up
well on other platforms.
Josh, any chance you could try it out on Solaris?

I copied the latest from pgFoundry to my contrib folder (pg 8.2.1)
and executed 'make; make install'. I get the following errors on OS X
10.4.10:

plugin_profiler.c: In function 'tableExists':
plugin_profiler.c:698: error: too few arguments to function
'stringToQualifiedNameList'
plugin_profiler.c: In function 'dumpStatsXML':
plugin_profiler.c:847: warning: format '%07ld' expects type 'long
int', but argument 4 has type 'suseconds_t'
plugin_profiler.c:850: warning: format '%07ld' expects type 'long
int', but argument 4 has type 'suseconds_t'
make: *** [plugin_profiler.o] Error 1
rm plugin_debugger.o targetinfo.o pldbgapi.o
Makefile:63: warning: overriding commands for target `install'
../../src/makefiles/pgxs.mk:104: warning: ignoring old commands for
target `install'
Makefile:77: warning: overriding commands for target `installdirs'
../../src/makefiles/pgxs.mk:139: warning: ignoring old commands for
target `installdirs'
gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -
Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-
aliasing -DINCLUDE_PACKAGE_SUPPORT=0 -I../../src/pl/plpgsql/src -I.
-I../../src/include -c -o plugin_profiler.o plugin_profiler.c
plugin_profiler.c: In function 'tableExists':
plugin_profiler.c:698: error: too few arguments to function
'stringToQualifiedNameList'
plugin_profiler.c: In function 'dumpStatsXML':
plugin_profiler.c:847: warning: format '%07ld' expects type 'long
int', but argument 4 has type 'suseconds_t'
plugin_profiler.c:850: warning: format '%07ld' expects type 'long
int', but argument 4 has type 'suseconds_t'
make: *** [plugin_profiler.o] Error 1

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

The core of the debugger has been in use for quite some time, but I
had to strip out a lot of EDB-specific code and I'd like to see that
the result (the open-source version at pgFoundry) holds up well on
other platforms.
Josh, any chance you could try it out on Solaris?

I copied the latest from pgFoundry to my contrib folder (pg 8.2.1) and
executed 'make; make install'. I get the following errors on OS X
10.4.10:

Sorry about that John, there's a fix for this problem (it's an 8.2
versus 8.3 issue) in the CVS repository. I thought I had rolled a new
tarball after committing the fix but I guess not.

You can pull the latest plugin_profiler.c from:

http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/edb-debugger/server/plugin_profiler.c?rev=1.4
<http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/%7Echeckout%7E/edb-debugger/server/plugin_profiler.c?rev=1.4&gt;

or just comment out the plugin_profiler from your Makefile; change:
PLUGINS = plugin_debugger plugin_profiler # plugin_tracer
to
PLUGINS = plugin_debugger # plugin_profiler plugin_tracer

I'll try to roll up a new tarball Wednesday or Thursday.

-- Korry

On Sep 4, 2007, at 8:55 PM, korry.douglas wrote:

Sorry about that John, there's a fix for this problem (it's an 8.2
versus 8.3 issue) in the CVS repository. I thought I had rolled a
new tarball after committing the fix but I guess not.

You can pull the latest plugin_profiler.c from:

Thanks Korry, that fixed it.

Is there any documentation that describes how to use the SQL
functions? Some are obvious enough, but a simple example showing a
debugging session would be helpful.

In some simple tests it seems to work OK with pgAdmin (1.8b3) on OS
X. There appears to be a pgAdmin bug when you start a debug session
on a function that has no parameters:

ERROR: syntax error at or near ")"
LINE 1: SELECT * FROM myschema.myfunction)
^

Thanks for doing this project -- looks great.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

John DeSoi wrote:

In some simple tests it seems to work OK with pgAdmin (1.8b3) on OS X.
There appears to be a pgAdmin bug when you start a debug session on a
function that has no parameters:

ERROR: syntax error at or near ")"
LINE 1: SELECT * FROM myschema.myfunction)
^

That's odd - I cannot reproduce that on OS X using beta 4 (which has no
important changes in the debugger over beta 3).

Can you provide a simple test case?

Regards, Dave

Hi Dave,

On Sep 5, 2007, at 3:54 AM, Dave Page wrote:

That's odd - I cannot reproduce that on OS X using beta 4 (which
has no
important changes in the debugger over beta 3).

Can you provide a simple test case?

I'll try to come up with a simple test case and send it sometime this
evening. Possible hint: the function had no IN parameters, but many
OUT parameters.

John

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Is there any documentation that describes how to use the SQL
functions? Some are obvious enough, but a simple example showing a
debugging session would be helpful.

I'll add that to the README file and let you know when it's ready.

-- Korry

Hi Dave,

On Sep 5, 2007, at 3:54 AM, Dave Page wrote:

That's odd - I cannot reproduce that on OS X using beta 4 (which
has no
important changes in the debugger over beta 3).

Can you provide a simple test case?

I get the same error with this:

create or replace function debug_test(out t text, out i integer)
returns record as $$
begin
t := 'test 1';
i := 10;
return;
end;
$$ language plpgsql;

I did the following:

1. Right click the function and chose "Debug" from the "Debugging"
submenu.
2. Clicked the OK button on the dialog.

Best,

John

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

John DeSoi wrote:

Hi Dave,

On Sep 5, 2007, at 3:54 AM, Dave Page wrote:

That's odd - I cannot reproduce that on OS X using beta 4 (which has no
important changes in the debugger over beta 3).

Can you provide a simple test case?

I get the same error with this:

create or replace function debug_test(out t text, out i integer)
returns record as $$
begin
t := 'test 1';
i := 10;
return;
end;
$$ language plpgsql;

I did the following:

1. Right click the function and chose "Debug" from the "Debugging" submenu.
2. Clicked the OK button on the dialog.

Thanks John - bug found and fixed in SVN.

Regards Dave

Is there any documentation that describes how to use the SQL
functions? Some are obvious enough, but a simple example showing a
debugging session would be helpful.

John, I started writing up the API documentation and then noticed that
most of what I intended to write is already described in the pldbgapi.c
module.

Take a look at that module and let me know if you have any questions
(you can e-mail me off-list if you like). I'll update the documentation
in pldbgapi.c as needed.

-- Korry

Hi Korry,

On Sep 6, 2007, at 10:23 AM, korry.douglas wrote:

John, I started writing up the API documentation and then noticed
that most of what I intended to write is already described in the
pldbgapi.c module.

Take a look at that module and let me know if you have any
questions (you can e-mail me off-list if you like). I'll update
the documentation in pldbgapi.c as needed.

I just noticed that when digging around last night. It helped a lot
with my understanding of how things work. I think that needs to go in
the readme file or at least reference it from the readme file.

I would still like to see a simple example using psql. I know you
would not really use psql for this, but I think it would help a lot
with getting started for folks that want to use the debugger. I did
not spend lots of time on it, but even after reading pldbgapi.c I was
not able to get simple session going (e.g. how to start a session and
request the source for a procedure).

Thanks,

John

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

I would still like to see a simple example using psql. I know you
would not really use psql for this, but I think it would help a lot
with getting started for folks that want to use the debugger. I did
not spend lots of time on it, but even after reading pldbgapi.c I was
not able to get simple session going (e.g. how to start a session and
request the source for a procedure).

Here's a simple example using psql (I will add this to the README file
too). In this example, we are debugging a PL/pgSQL function named
'testwhere( x int)', just because I happen to have that function in
front of me. 'testwhere( x int )' is called the 'target function' - the
backend process that executes testwhere(x int) is called the 'target
process'. Since we are setting a "global" breakpoint, the first backend
to trip across the target function will become the target process (you
can also set a breakpoint in a specific process if you want to be less
annoying).

---
--- pldbg_get_target_info() is simply a convenience
--- function that returns various information about
--- a potential target function/trigger. In particular,
--- given a function signature (or name in the absence
--- of any ambiguity), pldbg_get_target_info() returns
--- the OID of the function.
---

test=# SELECT * FROM pldbg_get_target_info( 'testwhere', 'f' );
target | schema | nargs | argtypes | targetname | argmodes | argnames |
targetlang | fqname | returnsset | returntype
--------+--------+-------+----------+------------+----------+----------+------------+------------------+------------+------------
26149 | 2200 | 1 | 26 | testwhere | | {x}
| 16944 | public.testwhere | f | 25
(1 row)

---
--- Create a TCP port (OS-assigned address) where the
--- target process can find us.  pldbg_create_listener()
--- returns a handle that we have to give back to the
--- other pldbg_xxx() functions (the first argument in
--- the remaining function calls is the handle value
--- that we got from pldbg_create_listener()).
---

test=# SELECT * FROM pldbg_create_listener();
pldbg_create_listener
-----------------------
1
(1 row)

---
--- Now set a 'global' breakpoint on the target
--- function (whose OID is 26149).  The third
--- argument, if given, specifies a line number
--- within the target function.  The last argument
--- specifies an (optional) target backend process ID.
---
--- Since we are not specifying a particular backend
--- process, we will trap the first server process to
--- trip over our breakpoint.
---

test=# SELECT * from pldbg_set_global_breakpoint(1, 26149, NULL, NULL);
pldbg_set_global_breakpoint
-----------------------------
t
(1 row)

---
--- Now we have to wait for some other backend to trip
--- over our breakpoint. When that happens, the target
--- process will attach to the TCP port we created
--- earlier.
---

test=# SELECT * FROM pldbg_wait_for_target(1);
pldbg_wait_for_target
-----------------------
8433
(1 row)

*--- Now we can invoke the target function (testwhere() in this
--- example) in some other client application, perhaps a second
--- psql session.
---
--- Note that the previous call to pldbg_wait_for_target()
--- will hang at this point.
---
*
---
--- And wait for the attached target to reach a
--- breakpoint.  It may seem strange to have both
--- pldbg_wait_for_target() and pldbg_wait_for_breakpoint(),
--- but we need two different functions when we are
--- doing direct-debugging.
---
--- When the target reaches a breakpoint, pldbg_wait_for_breakpoint()
--- returns the OID of the function, the line number at which the
--- the target is paused, and the name of the target function.
---
test=# SELECT * FROM pldbg_wait_for_breakpoint(1);
 func  | linenumber | targetname
-------+------------+------------
 26149 |          5 | testwhere
(1 row)

---
--- When the target has paused, you can retrieve the
--- source code for the target function...
---
test=# SELECT * FROM pldbg_get_source(1, 26149);
                      pldbg_get_source
------------------------------------------------------------

declare
\x09result text;
begin
\x09select into result proname from pg_proc where oid = x;
\x09return result;
end;

(1 row)

---
--- You can also retrieve a list of all local variables
--- and their current values.
---
--- You can also retrieve the call stack or a list of
--- breakpoints.  And you can change the focus of the
--- debugger to a different stack frame.
---

test=# SELECT * from pldbg_get_variables(1);
name | varclass | linenumber | isunique | isconst | isnotnull | dtype
| value
--------+----------+------------+----------+---------+-----------+-------+-------
x | A | 0 | t | t | f | 26
| 60
result | L | 2 | t | f | f | 25
| NULL
(2 rows)

---
--- To "step into", call pldbg_step_into(); notice that
--- it returns a 'breakpoint' tuple to tell you where
--- the target has paused.
---
--- You could also call pldbg_step_over(), pldbg_continue(),
--- or pldbg_set_breakpoint() here. 
---

test=# SELECT * from pldbg_step_into(1);
func | linenumber | targetname
-------+------------+------------
26149 | 6 | testwhere
(1 row)

---
--- If you want to abort the target function, call
--- pldbg_abort_target(); the client application will
--- see an error message (ERROR:  canceling statement
--- due to user request)
---
test=# SELECT * FROM pldbg_abort_target(1);
 pldbg_abort_target
--------------------
 t
(1 row)

That's a simple example showing the general flow for in-context debugging.

-- Korry