Thread: plpgsql debugging

The current implementation of plpgsql lacks some details that makes 
programming really hard: there's no language validator to check the code 
when creating the function, and there's support to debug the code.

Before I start hacking on this, I'd like to share my thoughts.

Looking at the code, I think that a validator could be added quite soon. 
The PLpgSQL_execstate struct could be extended by a validation_active 
bool flag, which changes the behaviour of all exec_stmt_XXX routines. 
The validator primarily executes the function, with that flag to TRUE, 
forcing all conditional statements to execute all execution paths 
exactly once, and sql statements being parsed.


Debugging is much harder.

There are two levels of debugging thinkable: full-blown stepping with 
breakpoints etc while running in the backend, and the small version 
having an intelligent console which simulates a backend understanding 
plpgsql language natively, so you can test the code by executing single 
blocks of code one after another (i.e not storing the function, but 
selectively executing parts of the function definition).

Backend debugging seems not possible the way the code is structured now. 
The execution path is stored on the backend program stack, so it's 
virtually impossible to interrupt the execution at a point for later 
continuation. The backend's flow of execution is identical with 
plpgsql's, interrupting plpgsql means stopping the backend.

Frontend debugging seems more feasible, by offering some functions, that 
enables a caller to initialize variables, call a statement block (that's 
compiled immediately, executed and discarded), and retrieve all vars.

Any thoughts about this? More caveats I haven't seen?

Regards,
Andreas

On Monday 08 September 2003 15:14, Andreas Pflug wrote:
>
> Looking at the code, I think that a validator could be added quite soon.
> The PLpgSQL_execstate struct could be extended by a validation_active
> bool flag, which changes the behaviour of all exec_stmt_XXX routines.
> The validator primarily executes the function, with that flag to TRUE,
> forcing all conditional statements to execute all execution paths
> exactly once, and sql statements being parsed.

Sounds good.

> Debugging is much harder.
>
> There are two levels of debugging thinkable: full-blown stepping with
> breakpoints etc while running in the backend, and the small version
> having an intelligent console which simulates a backend understanding
> plpgsql language natively, so you can test the code by executing single
> blocks of code one after another (i.e not storing the function, but
> selectively executing parts of the function definition).

Actually, a simple trace ability would be a huge step forward. It'd save me 
dotting RAISE statements around my functions while I write them. Even the 
ability to add DEBUG statements that checked some global flag before firing 
would be very useful (to me at least).

--  Richard Huxton Archonet Ltd

Richard Huxton wrote:

>Actually, a simple trace ability would be a huge step forward. It'd save me 
>dotting RAISE statements around my functions while I write them.
>
Sounds bloody familiar... :-(

> Even the ability to add DEBUG statements that checked some global flag before firing 
>would be very useful (to me at least).
>  
>
I could imagine a DEBUG .... which works like RAISE NOTICE with checking 
for a set variable, and ASSERT which works like RAISE ERROR.

Does anybody expect keyword conflicts from this?

Regards,
Andreas

On Monday 08 September 2003 17:14, Andreas Pflug wrote:
> Richard Huxton wrote:
> >Actually, a simple trace ability would be a huge step forward. It'd save
> > me dotting RAISE statements around my functions while I write them.
>
> Sounds bloody familiar... :-(
>
> > Even the ability to add DEBUG statements that checked some global flag
> > before firing would be very useful (to me at least).
>
> I could imagine a DEBUG .... which works like RAISE NOTICE with checking
> for a set variable, and ASSERT which works like RAISE ERROR.
>
> Does anybody expect keyword conflicts from this?

How about a DEBUG block, ideally with a token?

...
DEBUG ''foo'' RAISE NOTICE ''my loop counter is %'',i; -- any other valid statements here
END DEBUG;
...

=> SET DEBUG_TOKEN='foo';
=> SELECT my_function();

That would let you turn debugging on/off for various modules by token-name, 
and let you e.g. check whether there are the expected number of records in 
some target table.

Would that impose a horrible performance cost, or would the whole block just 
be skipped? (I only ever took a quick look at the plpgsql code)

--  Richard Huxton Archonet Ltd