Re: Proposal for debugging of server-side stored procedures

Some thoughts from another Tom...

Mark Cave-Ayland wrote:
> ... 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".
>
All PL's are launched someway or another . It would probably be very simple to add the '-d'
flag to the PL/Perl launcher and the '-m' flag to the PL/Python launcher and control it with
module specific GUC settings. PL/Java already does this. The SQL to initialize debugging
looks like this:

SET pljava.vmoptions TO '-agentlib:jdwp=transport=dt_shmem,server=y,suspend=y';

This tells the VM to load in-process debugging libraries and specifies the kind of
connection to be made. As soon as the first java function is called, the process is
suspended and waits for a client debugger to attach.

The amount of work needed to create similar solutions for perl, python, tcl, etc. is
probably limited and fairly trivial.

> 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.
>
And it would force you to write your own proprietary debugger backend for each language.
That's a major thing to undertake. Have you considered the maintenance aspect of it? Not
something that would make the author of a PL module scream with joy. It might be wise to
also consider what debugger preferences a skilled perl/python/<whatever language> developer
might have. A home brewed debugger in the form of a PostgreSQL add-on might not be a natural
choice.

> 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].
>
You'll find more bad news as you go along. I have sincere doubts that inventing your own
multi-language debugger is the right way to go.

> 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...
>
>
SQL debugging and hooking into the executor sounds really interesting and something that
would really be worth the effort. I doubt there's a gain mixing that with debugging of pl's
in general. Having multiple debugger clients, one for each language, and one for SQL, might
be a good thing.

Regards,
Thomas Hallgren