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

On Tue, Dec 19, 2017 at 2: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.

--
Kevin Burke
925.271.7005 <(925)%20271-7005> | kev.inburke.com

*You would probably want to debug the function in interactive mode to find
out where you went wrong.*

*Both PgAdmin III and PgAdmin 4 can use the debugger from
EnterpriseDBhttps://www.pgadmin.org/docs/pgadmin3/1.22/debugger.html
<https://www.pgadmin.org/docs/pgadmin3/1.22/debugger.html&gt;https://www.pgadmin.org/docs/pgadmin4/dev/debugger.html
<https://www.pgadmin.org/docs/pgadmin4/dev/debugger.html&gt;*

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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:

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.

--
Kevin Burke
925.271.7005 <(925)%20271-7005> | kev.inburke.com

Assuming it's not a function in production yet, put some log statements in
it, then check the logs. If it's already in production, you'll probably
have to create a separate version of the function for testing.
--
Mike Nolan

Can you describe what you mean by log statements? I have log_statement
enabled and I can see the queries; the problem is the output is not logged
and not what I expect. I need to modify the function so it shows the output
of an intermediate CTE when I run it with specific inputs, and that's
currently a little cumbersome.

--
Kevin Burke
925.271.7005 | kev.inburke.com

On Tue, Dec 19, 2017 at 11:43 AM, Michael Nolan <htfoot@gmail.com> wrote:

On Tue, Dec 19, 2017 at 2:47 PM, Kevin Burke <kev@inburke.com> wrote:

Can you describe what you mean by log statements? I have log_statement
enabled and I can see the queries; the problem is the output is not logged
and not what I expect. I need to modify the function so it shows the output
of an intermediate CTE when I run it with specific inputs, and that's
currently a little cumbersome.

--
Kevin Burke
925.271.7005 <(925)%20271-7005> | kev.inburke.com

On Tue, Dec 19, 2017 at 11:43 AM, Michael Nolan <htfoot@gmail.com> wrote:

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:

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.

--
Kevin Burke
925.271.7005 <(925)%20271-7005> | kev.inburke.com

Assuming it's not a function in production yet, put some log statements
in it, then check the logs. If it's already in production, you'll probably
have to create a separate version of the function for testing.
--
Mike Nolan

*>Can you describe what you mean by log statements? *

*What he probably means is make use of the RAISE NOTIFY statement.*

*https://www.postgresql.org/docs/9.6/static/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE
<https://www.postgresql.org/docs/9.6/static/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE&gt;*

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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