Re: Debugging a function - what's the best way to do this quickly?

On Tue, Dec 19, 2017 at 1:24 PM, Kevin Burke <kev@inburke.com> wrote:
> I'm writing a function that looks a little like this:
>
> DROP FUNCTION IF EXISTS myfunction;
> CREATE OR REPLACE FUNCTION myfunction(arg1 uuid,
>     _symbol text,
>     _start timestamp with time zone,
>     _end timestamp with time zone
> ) RETURNS TABLE (arg5 date, arg6 float)
> AS $$
> WITH cte1 AS ( ... ),
> cte2 AS ( ... ),
> cte3 AS ( ... ),
> cte4 AS ( ... ),
> cte5 AS ( ... )
> SELECT X as arg5, Y as arg6 FROM cte5;
> $$
>
> The function is not returning the correct results; I think the problem is in
> cte2 or cte3. What's the easiest way to debug this? I would like to send
> some test inputs through the program, observe the output from cte3, and
> modify the values and see if I get the correct new answers. Here are the
> approaches I know right now:
>
> - Modify the function return to contain the columns for cte3. (I don't think
> there is a way to indicate RETURNS * or similar wildcard)
> - Reload the function.
> - Call the function with the test arguments, and view the resulting table.
> Modify/reload/rerun as appropriate.
>
> Or:
>
> - Copy the function to another file.
> - Delete the function prologue and epilogue
> - Replace every use of the input arguments with the hardcoded values I want
> to test with
> - Run the file, making changes as necessary.
>
> This seems pretty cumbersome. Is there an easier way I am missing?
> Specifically it would be neat if it was easier to visualize the intermediate
> steps in the query production. If there are professional tools that help
> with this I would appreciate pointers to those as well.

If you have a lot of chained CTEs and the problem lies within that
chain, copying the query and subbing arguments is likely the best
option.  For really nasty situations I tend to convert the CTEs, one
at a time, to temp tables, reviewing the results on each step.  I've
scaled back my use of CTEs a lot lately for this and other reasons
(mainly problems with statistics) although I really appreciate the
lack of catalog bloat.

I also heavily abuse 'RAISE NOTICE' for debugging purposes.  Something
like this:

CREATE OR REPLACE FUNCTION Notice(TEXT) RETURNS VOID AS
$$
BEGIN
  RAISE NOTICE '%', $1;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION NoticeValue(anyelement) RETURNS anyelement AS
$$
  SELECT $1 FROM (SELECT NOTICE($1::TEXT)) q;
$$ LANGUAGE SQL;

'NoticeValue' can be used just about anywhere, for example:

SELECT a FROM foo WHERE...
could be quickly converted to:
SELECT NoticeValue(a) AS a FROM foo WHERE....

Don't forget, we can convert records to json and 'notice' them:
SELECT a,  NoticeValue(to_json(a)) FROM foo WHERE....

Dynamic SQL (via EXECUTE) can be a real pleasure to debug (not so much
to write and review), particularly if you (securely) do your own
parameterization since you can just print out the entire query.  From
a debugging standpoint, that's as good as it gets.

Also, there is a pl/pgsql debugger.  I don't have any experience with
it, maybe somebody else can comment.  I work exclusively with psql,
and so tend to use techniques that work well there.

merlin