Re: Proposal for debugging of server-side stored procedures

"Mark Cave-Ayland" <m(dot)cave-ayland(at)webbased(dot)co(dot)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 is
client <-> 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