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