Re: Proposal for debugging of server-side stored procedures - Mailing list pgsql-hackers

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 29 May 2006 18:05
> To: Mark Cave-Ayland
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Proposal for debugging of server-side stored
> procedures

(cut)

> As far as the debug protocol details go, it'd be worth looking at the
> GDB host protocol to see if it's usable; even if not directly usable
> (it's probably too low-level) it would give ideas about the functions
> you need to provide.  It'd also be smart to understand how debugging is
> commonly done in Perl, Python, etc, with an eye to being able to
> piggyback on existing tools instead of inventing our own.
> 
>             regards, tom lane


Hi Tom,

Thanks for your input on this. I've been spending some time researching
debuggers over the past week and AFAICT we'd be struggling to use the native
debuggers that come with Perl/Python et al. The main reason for this is that
the debuggers with Perl/Python are actually interactive environments, for
example debugging in Perl is initiated with "perl -d somefile.pl" which then
becomes a special interactive interpreter session. The same is also true
with Python which is launched in a similar way, "python -m pdb somefile.py".

However, both Perl and Python offer the ability to hook into the interpreter
to detect events. For example, Python offers a C API here [1] that allows
you to supply a callback when particular events occur; this would allow us
to execute a callback after the interpreter executes each line.

Perl seems a little more messy in that I can't find a documented C API to
hook into the interpreter, but it looks as if it may be possible to cook
something up with writing a new DB package [2] which uses XS call a C
callback. The other issue is that unlike Python, the debug capability must
be specified when creating the instance of interpreter rather than being
able to enable/disable debugging on the fly so it may mean that two
instances of the perl interpreter need to be maintained in memory - a
standard instance and a debugging instance.

Plpgsql isn't really a concern as we can simply code up whichever model we
eventually decide to use. The only bad news I can see is that it appears Tcl
may need to have the source patched in order to add debug hooks into the
interpreter [3].

Also, I'm wondering how to design the mechanism to be extendable in the
future beyond debugging server-side functions, such as hooking into more of
the executor mechanisms. For example, I could see a use case for allowing a
profiler to use the debug API to attach to the postmaster to receive
notifications whenever a new SQL query starts and ends; this would allow
someone to write a rather neat app that could rank the most popular queries
in terms of processing time really easily, but then maybe this could
considered treading on the toes of the existing stats process...


Thoughts?

Mark.

[1] http://docs.python.org/api/profiling.html
[2] http://search.cpan.org/~nwclark/perl-5.8.8/pod/perldebguts.pod
[3] http://www.xdobry.de/atkdebugger/index.html

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.