Re: Proposal for debugging of server-side stored procedures

Mark Cave-Ayland wrote:

> I think that Java is quite unusual in that the design of JPDA is inherently
> client/server based to the point where they have defined the platform to
> allow you interact with the JVM via a socket. Unfortunately the same can't
> be said for Perl/Python - as you suggest passing the parameters into the
> interpreter is a trivial exercise but because the debugging classes in both
> languages are console interactive, the only thing you could do is redirect
> the console output to a socket (see
> http://search.cpan.org/dist/perl/lib/perl5db.pl and the RemotePort option in
> the case of Perl).
>
Obviously I'm not a Perl nor Python hacker. I just find it hard to believe that languages
that has matured over a decade doesn't have a remote debug option that can be used. Sockets,
shared-memory, pipes, or whatever. You will be able to attach to it somehow given it's been
launched with the correct flags.

I think you're wrong in thinking that client/server based debugging is in any way unusual.
Googling for perl+debug+remote tells me that client/server based Perl debugging is common.
There are a number of solutions. The same is true for Python.

> What I would like to see is some form of IDE that our developers can use,
> probably under Windows, that would enable them to step through and debug
> stored procedures on a remote server on another network. Using simple
> console redirection would involve parsing text output which strikes as being
> something that would break easily with new releases.
>
Good IDE's exists already, for both perl, python, and others (you mention Komodo further
down). In addition to that you have well tested Emacs mappings and command line clients that
people like to use. The more prominent ones will always provide upgrades when there are
protocol changes.

> And then of course, there is the problem of security whereby anyone could
> connect to the socket. For example, imagine a group of developers all
> debugging different functions simultaneously; if one of them connected to
> the wrong console socket then it could be confusing as the developer wanders
> why their code never stops at a breakpoint.
>
In my experience you have two use-cases.

1. You debug during development and have either have your own database instance to play
around with or a shared sandbox database where the security is very low.
2. You debug a running instance on a live server and the sysadmin is well aware what you're
doing. You will be given required privileges as needed.

I would argue that the times when security becomes an issue when debugging are extremely
rare and not worth spending lots of time and effort on. It is enough to prevent anyone but a
super-user (or even a sysadmin) to start a remote debug session.

> ... the only thing that PL developers would have to is
> provide an API for things like step, set breakpoint, read variable, and
> eval.
>
Well, the API isn't worth much without an implementation. So in essence, you're saying that
the only thing the PL developers would have to do is to provide a full blown debug server ;-)

> A promising option at the moment would be to implement the DBGP protocol for
> Perl/Python/Tcl as suggested by Lukas, mainly because it appears ActiveState
> have already written the modules to do this (see
> http://aspn.activestate.com/ASPN/Downloads/Komodo/RemoteDebugging).

There you go! Perl, PHP, Python, and Tcl all taken care of. IDE and all!

> The only
> issue I can see with this is again related to security in that the debugger
> would not respect the ACLs within PostgreSQL which could potentially allow a
> user to break inside a function that wasn't his/her own.
>
I'd use the Komodo IDE and implement the ability to start the PL using a GUC setting per my
original suggestion (with super-user requirement). Trivial solution, minimum effort, and
very useful. KISS principle.

It would be great if we could agree on a GUC flag (or flags) that would control debugging
for all PL's. At present, all PL implementors must use their own (module specific) flags.

Kind regards,
Thomas Hallgren