Thread: Proposal for debugging of server-side stored procedures

Hi everyone,
Having browsed the TODO list, one of the items that I would be interested
on working on is a debugger for stored procedures. Having searched on this
topic in the archives, I'm still short of some answers that would allow me
to come up with a complete proposal that I can use to start coding.
The most important question to answer in my mind is how should the
debugger communicate with the server? I can see 3 ways in which this could
happen:    1) Use the existing FE/BE protocol to allow the user to control    the debugging session using stored
procedures/pseudo-tables,e.g.        SELECT pg_debug_enable('myfunction');        INSERT INTO pg_debug_breakpoints
(function,line) VALUES        ('myfunction', 2);        SELECT pg_debug_step('myfunction');    2) Spawn a separate
debuggerprocess listening on another port to    allow OOB communication with the server. This would involve the
 
design    and implementation of a debug FE/BE protocol, unless there is a    standard that already exists.    3) Extend
theexisting FE/BE protocol to allow transmission of an
 
OOB    debug channel.
My current thoughts are leaning towards 2 or 3, with the advantage of
approach 3 being that once you can connect to the database using a client,
the debugging functionality automatically becomes available without having
to worry about additional security checks on the debugger port (like
another version of pg_hba.conf just for the debugger) and firewalls
blocking debugger connections.
Thoughts?
Mark.

------------------------
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.

"Mark Cave-Ayland" <m.cave-ayland@webbased.co.uk> writes:
> The most important question to answer in my mind is how should the
> debugger communicate with the server?
>      1) Use the existing FE/BE protocol to allow the user to control
>      the debugging session using stored procedures/pseudo-tables, e.g.
>          SELECT pg_debug_enable('myfunction');
>          INSERT INTO pg_debug_breakpoints (function, line) VALUES
>          ('myfunction', 2);
>          SELECT pg_debug_step('myfunction');
I think this approach is utterly unworkable: it assumes that the
client application is cooperative, and it ignores the fact that the
operations you need to step through are inside a SQL command, which
is not a place where you can easily issue more SQL commands ---
certainly not without horrid damage to the FE/BE protocol.

You mustn't assume a cooperative application, either.  Suppose that
you need to debug what your PL functions are doing when driven by a
complicated web application.  Injecting debug commands through the
application would require the cooperation of quite a few layers of
code (eg, JDBC, a couple of levels of middleware, etc) and is just
not going to happen.
>      2) Spawn a separate debugger process listening on another port to
>      allow OOB communication with the server. This would involve the
> design
>      and implementation of a debug FE/BE protocol, unless there is a
>      standard that already exists.

The way I usually debug server problems is to attach with GDB to an
already-started server process.  This is fairly convenient and requires
no special client support, but it's less than desirable from a security
point of view: you have to be on the server's machine and be the same
user (or root) to be allowed to attach.  I would imagine any
direct-attach protocol we might invent would have to have comparable
restrictions, because it's just too hard to tell whether someone
knocking on the door should actually be allowed to debug that specific
session over there.

>      3) Extend the existing FE/BE protocol to allow transmission of an
> OOB
>      debug channel.

We could get around both the security and the application-cooperation
problems if we design the debugger as an intermediate process that sits
between the application and the server.  To debug an app, you tell it to
connect to a host/port where the debugger is listening, and then the
debugger turns around and forwards the connection to the real database.
Now the debugger can watch the FE/BE messages going by, and can inject
debug commands into the data stream and read debug responses without
the application being any the wiser.  So basically yeah, what we need
is a debug subchannel in the FE/BE protocol.  I'd suggest inventing
a single Debug message type (sendable in both directions) with the
contents being specified by a separate protocol definition.  Or perhaps
invert that and imagine the FE/BE protocol as embedded in a debug
protocol.

We might want to play the same game on the server side; that is, the
communication structure isclient <-> debugger user interface process <------>    debugger control process <-> backend
The advantage of this is the backend doesn't need to be explicitly
aware of debug operations, which would be a Good Thing because we
don't want it constantly checking the client port during queries
to see if messages have arrived.  The control process would then
have both access to the message stream, and the ability to invoke
trace/step facilities on the backend process.

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

Tom Lane wrote:
> "Mark Cave-Ayland" <m.cave-ayland@webbased.co.uk> writes:
> ... So basically yeah, what we need
> is a debug subchannel in the FE/BE protocol.  I'd suggest inventing
> a single Debug message type (sendable in both directions) with the
> contents being specified by a separate protocol definition.  Or perhaps
> invert that and imagine the FE/BE protocol as embedded in a debug
> protocol.
> 
I think this is a bad idea. PL/Java will use either shared memory or a socket to attach and 
as you already mentioned, when using C, a gdb will attach directly using the pid. I wouldn't 
be too surprised if Perl, Python, and PHP all have a similar solution and thus have no 
benefit from additions to the FE/BE protocol. IMO, debugging should be language specific and 
take place in a separate channel. There's no gain whatsoever mixing it with the FE/BE protocol.

Regards,
Thomas Hallgren

Thomas Hallgren <thomas@tada.se> writes:
> I think this is a bad idea. PL/Java will use either shared memory or a
> socket to attach and as you already mentioned, when using C, a gdb
> will attach directly using the pid. I wouldn't be too surprised if
> Perl, Python, and PHP all have a similar solution and thus have no
> benefit from additions to the FE/BE protocol. IMO, debugging should be
> language specific and take place in a separate channel. There's no
> gain whatsoever mixing it with the FE/BE protocol.

It may well be that for plperl and friends we can kick the problem off
to language-specific debuggers --- indeed, one of the first things we
need to do is look at those to see what we can avoid reinventing.
But what of plpgsql?

Also, any solution of this type probably requires that the person doing
debugging have database superuser access (in fact, be logged directly
into the server machine as the postgres user).  It'd be nice to have an
approach that could be used by non-superusers to debug their trusted-PL
functions.
        regards, tom lane

Tom Lane wrote:
> Thomas Hallgren <thomas@tada.se> writes:
>> I think this is a bad idea. PL/Java will use either shared memory or a
>> socket to attach and as you already mentioned, when using C, a gdb
>> will attach directly using the pid. I wouldn't be too surprised if
>> Perl, Python, and PHP all have a similar solution and thus have no
>> benefit from additions to the FE/BE protocol. IMO, debugging should be
>> language specific and take place in a separate channel. There's no
>> gain whatsoever mixing it with the FE/BE protocol.
> 
> It may well be that for plperl and friends we can kick the problem off
> to language-specific debuggers --- indeed, one of the first things we
> need to do is look at those to see what we can avoid reinventing.
> But what of plpgsql?
> 
Ideally, all pl's should use the same protocol. It should be language agnostic and allow 
different regions of the code to origin from different languages. That way, it would be 
possible to single step a plpgsql function that in turn calls a function in pljava. 
Incidentally, the JDWP (Java Debug Wire Protocol) was designed to do just that. But I think 
it would be very complicated to cross language boundaries even if we did use that.

The JDWP and the architecture that surrounds it might be a good source for inspiration 
though. See: http://java.sun.com/j2se/1.5.0/docs/guide/jpda/architecture.html.

> Also, any solution of this type probably requires that the person doing
> debugging have database superuser access (in fact, be logged directly
> into the server machine as the postgres user).  It'd be nice to have an
> approach that could be used by non-superusers to debug their trusted-PL
> functions.
> 
Indeed. In my case, it's a matter of who starts the VM and what options that are passed to 
it (given certain options, the JVM will listen to a port or a semaphore that controls a 
region of shared memory). That in turn is controlled using GUC settings so for PL/Java I 
think it would be possible to set it up that way.

Regards,
Thomas Hallgren

Thomas Hallgren wrote:

> Ideally, all pl's should use the same protocol. It should be language 
> agnostic and allow different regions of the code to origin from 
> different languages. That way, it would be possible to single step a 
> plpgsql function that in turn calls a function in pljava. Incidentally, 
> the JDWP (Java Debug Wire Protocol) was designed to do just that. But I 
> think it would be very complicated to cross language boundaries even if 
> we did use that.
> 
> The JDWP and the architecture that surrounds it might be a good source 
> for inspiration though. See: 
> http://java.sun.com/j2se/1.5.0/docs/guide/jpda/architecture.html.

I have no idea if this would fit .. but just to throw something into the 
mix that was designed to be somewhat language agnostic (with a slant 
towards web scripting languages):
http://xdebug.org/docs-dbgp.php

If it seems like it could fit I can create contact with the two authors 
Derick and Shane to discuss this further.

regards,
Lukas

> -----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.

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

Mark Cave-Ayland wrote:

[snip]
> 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.
>
>   

Debugging embedded perl has some significant challenges. You might find 
it interesting to see what can be done in the most famous embedded 
situation: mod_perl. See http://perl.apache.org/docs/1.0/guide/debug.html

using ptkdb might be nice ....

cheers

andrew

> -----Original Message-----
> From: Thomas Hallgren [mailto:thomas@tada.se]
> Sent: 09 June 2006 16:25
> To: Mark Cave-Ayland
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: Proposal for debugging of server-side stored procedures
> 
> Some thoughts from another Tom...

Hi Tom,

Thanks for the feedback :)

> 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.

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).

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. 

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.

> 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.

Well my original thinking was that you could factor most of the code into
PostgreSQL itself; the only thing that PL developers would have to is
provide an API for things like step, set breakpoint, read variable, and
eval.

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). 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.


Kind regards,

Mark.

------------------------
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.

> -----Original Message-----
> From: Andrew Dunstan [mailto:andrew@dunslane.net]
> Sent: 09 June 2006 17:01
> To: Mark Cave-Ayland
> Cc: 'Tom Lane'; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Proposal for debugging of server-side stored
> procedures

(cut)

> Debugging embedded perl has some significant challenges. You might find
> it interesting to see what can be done in the most famous embedded
> situation: mod_perl. See http://perl.apache.org/docs/1.0/guide/debug.html
> 
> using ptkdb might be nice ....
> 
> cheers
> 
> andrew


Hi Andrew,

Thanks for the link. This brings up another issue - what if someone wishes
to debug locally using their favourite tool rather than a PostgreSQL tool?
Should this be allowed even if it may circumvent PostgreSQL's user
permissions on functions within the database?


Kind regards,

Mark.

------------------------
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.

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

Thomas Hallgren wrote:

> 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.

The DBGp protocol [1] I mentioned earlier is used by Komodo for example. 
As a matter of fact Shane from ActiveState is one of the co-authors of 
the protocol. The other author is Derick from the php project.

regards,
Lukas

[1] http://xdebug.org/docs-dbgp.php

> -----Original Message-----
> From: Thomas Hallgren [mailto:thomas@tada.se]
> Sent: 11 June 2006 10:07
> To: Mark Cave-Ayland
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: Proposal for debugging of server-side stored procedures

Hi Tom,

(cut)

> 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.

It's not unusual, it's just that the standard libraries included with
Perl/Python contain practically no support for remote debugging - while they
both provide interfaces for you to write you own replacement, there doesn't
seem to be a standard for remote debugging other than Xdebug.

(cut)

> 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.

True, although there are times for example in our web applications where we
need to debug as a non super-user since the web user accessing the database
obviously doesn't have super-user rights. In the case of DBGP this could be
a case of allowing only the super-user to set the remote host/port while
allowing any user to initiate a debug session. At least in the case the
administrator would be aware of the implications of enabling debugging in
this way.

(cut)

> > 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!

I've been having a look at the licensing and it appears that the server side
libraries are licensed under the Artistic License
(http://www.opensource.org/licenses/artistic-license.php). Looking at the
wording of the license I can't see that there would be much of a problem
including these with PostgreSQL to give "out of the box" DBGP support, but
with the option of allowing the user to override the defaults per language
in postgresql.conf - this would allow, say, for Andrew to run ptkdb as an
alternative while normal users could just go with the defaults. Can anyone
see any problems with this? If not, I'd be happy to approach the
ActiveState/Xdebug people to see if this is feasible.

The only remaining angle would be to implement the Xdebug protocol for
plpgsql in C which would probably be the most time-consuming aspect of the
project :)

(cut)

> 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.

I'm not quite sure what you had in mind from your previous email - could you
elaborate more on this?


Kind regards,

Mark.

------------------------
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.

Hi,

I was just talking to Derick the author of DBGp and I realized this 
might be a topic for a joint effort among all open source RDBMS. I think 
it would be awesome if we could get a common protocol setup for stored 
procedure debugging.

regards,
Lukas