BUG #5867: wish: plpgsql print table for debug

On Mon, Feb 7, 2011 at 1:01 AM, Richard Neill
<postgresql@richardneill.org> wrote:

The following bug has been logged online:

Bug reference:      5867
Logged by:          Richard Neill
Email address:      postgresql@richardneill.org
PostgreSQL version: 9.03
Operating system:   Linux
Description:        wish: plpgsql print table for debug
Details:

When debugging a plpgsql function, it would be really amazingly useful to be
able to do a regular psql-style SELECT, and have the result printed to
screen.

Something like:

  Raise Notice table 'SELECT .... '

and then plpgsql would run the query and dump the result to screen, using
its helpful formatting.

As far as I can see, this isn't possible (though there are a lot of people
searching for how to do it), and the only workaround is to manually handle
the looping and formatting, raising lots of individual notices. This makes
debugging much harder than it should be.

It wouldn't be too hard to write a loop that runs the select statement
and does RAISE NOTICE on each row. Getting that into the psql
formatting would be a little trickier, but I don't see why you
couldn't write a PL/pgsql function to do it. Then you could just call
that function and pass it an SQL query every time you want to do this.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

The following bug has been logged online:

Bug reference: 5867
Logged by: Richard Neill
Email address: postgresql@richardneill.org
PostgreSQL version: 9.03
Operating system: Linux
Description: wish: plpgsql print table for debug
Details:

When debugging a plpgsql function, it would be really amazingly useful to be
able to do a regular psql-style SELECT, and have the result printed to
screen.

Something like:

Raise Notice table 'SELECT .... '

and then plpgsql would run the query and dump the result to screen, using
its helpful formatting.

As far as I can see, this isn't possible (though there are a lot of people
searching for how to do it), and the only workaround is to manually handle
the looping and formatting, raising lots of individual notices. This makes
debugging much harder than it should be.

It wouldn't be too hard to write a loop that runs the select statement
and does RAISE NOTICE on each row. Getting that into the psql
formatting would be a little trickier, but I don't see why you
couldn't write a PL/pgsql function to do it. Then you could just call
that function and pass it an SQL query every time you want to do this.

I'm rather hoping that this would actually be an enhancement to
PL/PGSQL, (or at least an officially documented howto) rather than just
a private debugging function.

Do you not think it would be really amazingly useful? After all, in C,
the single most useful debugging tool is "fprintf(stderr,...)", and yet
postgresql doesn't have an equivalent that can operate on the most
common data format. [I'm stretching the analogy a bit here, but it seems
to me that a multi-row table is to postgresql as int is to C.]

There are a lot of people who would benefit from it, most of whom
(including me) don't really have the expertise to do it well.

Also, there is a lot of value in being able to debug as needed with a
1-line debugging statement, then get back to the problem at hand, rather
than having to break out of the current programming task to write a
debug function :-)

Thanks very much,

Richard

Hello

Do you not think it would be really amazingly useful? After all, in C, the
single most useful debugging tool is "fprintf(stderr,...)", and yet
postgresql doesn't have an equivalent that can operate on the most common
data format. [I'm stretching the analogy a bit here, but it seems to me that
a multi-row table is to postgresql as int is to C.]

it's nonsense - PL/pgSQL is procedural language - so there are same -
similar types like C

There are a lot of people who would benefit from it, most of whom (including
me) don't really have the expertise to do it well.

I don't think so we need a special enhancing of RAISE statement. What
is a problem on lines

FOR r IN SELECT ... LOOP
RAISE NOTICE r;
END LOOP;

???

Also, there is a lot of value in being able to debug as needed with a 1-line
debugging statement, then get back to the problem at hand, rather than
having to break out of the current programming task to write a debug
function :-)

CREATE OR REPLACE FUNCTION debug_query(text)
RETURNS void AS $$
DECLARE r record;
BEGIN
FOR r IN EXECUTE $1 LOOP
RAISE NOTICE r;
END;
END;
$$ LANGUAGE plpgsql;

Regards

Pavel Stehule

On Thu, Mar 3, 2011 at 12:12 PM, Richard Neill <rn214@richardneill.org> wrote:

Do you not think it would be really amazingly useful? After all, in C, the
single most useful debugging tool is "fprintf(stderr,...)", and yet
postgresql doesn't have an equivalent that can operate on the most common
data format. [I'm stretching the analogy a bit here, but it seems to me that
a multi-row table is to postgresql as int is to C.]

Sure it does. You can pass the tuple to RAISE NOTICE easily enough.
It won't have all the same bells and whistles psql would supply, but
it prints out well enough for debugging. Or at least it's never
bothered me.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:

On Thu, Mar 3, 2011 at 12:12 PM, Richard Neill <rn214@richardneill.org> wrote:

Do you not think it would be really amazingly useful? After all, in C, the
single most useful debugging tool is "fprintf(stderr,...)", and yet
postgresql doesn't have an equivalent that can operate on the most common
data format. [I'm stretching the analogy a bit here, but it seems to me that
a multi-row table is to postgresql as int is to C.]

Sure it does. You can pass the tuple to RAISE NOTICE easily enough.
It won't have all the same bells and whistles psql would supply, but
it prints out well enough for debugging. Or at least it's never
bothered me.

Note that doing anything more than RAISE NOTICE or equivalent would
imply a significant protocol change. You can't just shove a table out
to the client, because it'll think that that's the response to the outer
SELECT (or whatever) command that called your function. So while it'd
be kind of cool if you could invoke psql's table pretty-printing stuff
this way, the amount of work required to get there seems vastly out of
proportion to the benefit.

regards, tom lane

Dear Pavel,

Thanks for your help.

Do you not think it would be really amazingly useful? After all, in C, the
single most useful debugging tool is "fprintf(stderr,...)", and yet
postgresql doesn't have an equivalent that can operate on the most common
data format. [I'm stretching the analogy a bit here, but it seems to me that
a multi-row table is to postgresql as int is to C.]

it's nonsense - PL/pgSQL is procedural language - so there are same -
similar types like C

Sorry - I perhaps over-stretched the analogy. What I meant was that, at
least apparently, SQL "types" include anything that can result from an
SQL statement, including an individual "record" or an entire temporary
table. I know that strictly speaking this isn't true, but it seems to me
that one should be able to do:
RAISE NOTICE (SELECT ....)

CREATE OR REPLACE FUNCTION debug_query(text)
RETURNS void AS $$
DECLARE r record;
BEGIN
FOR r IN EXECUTE $1 LOOP
RAISE NOTICE r;
END;
END;
$$ LANGUAGE plpgsql;

Thanks for your help - but I'm afraid this doesn't actually work. psql
rejects the line "RAISE NOTICE r;"

Raise notice expects a format string and some variables, very similar to
printf(). This means that we'd have to write something like:
RAISE NOTICE ('first %, second %, third %', col1, col2, col3;
except that our debug_query function doesn't know in advance how many
columns there are, (or the types and their names).

Richard

Sure it does. You can pass the tuple to RAISE NOTICE easily enough.
It won't have all the same bells and whistles psql would supply, but
it prints out well enough for debugging. Or at least it's never
bothered me.

Sorry if I'm being dense, but I can't see how you can pass a tuple; I
think raise-notice only lets you pass individual strings/integers. But I
don't think we can pass all of them without specifying in advance how
many there are....

On Thu, Mar 3, 2011 at 1:37 PM, Richard Neill <rjn@richardneill.org> wrote:

Sure it does.  You can pass the tuple to RAISE NOTICE easily enough.
It won't have all the same bells and whistles psql would supply, but
it prints out well enough for debugging.  Or at least it's never
bothered me.

Sorry if I'm being dense, but I can't see how you can pass a tuple; I think
raise-notice only lets you pass individual strings/integers. But I don't
think we can pass all of them without specifying in advance how many there
are....

Pavel had it almost right. Here's a version that works for me.

CREATE FUNCTION debug_query(qry text) RETURNS void
LANGUAGE plpgsql
AS $$
declare
r record;
begin
for r in execute qry loop
raise notice '%', r;
end loop;
end
$$;

And here it is doing its thing:

rhaas=# select debug_query('SELECT * FROM foo');
NOTICE: (1,Richard)
NOTICE: (2,Robert)
NOTICE: (3,Tom)
debug_query
-------------

(1 row)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Note that doing anything more than RAISE NOTICE or equivalent would
imply a significant protocol change. You can't just shove a table out
to the client, because it'll think that that's the response to the outer
SELECT (or whatever) command that called your function. So while it'd
be kind of cool if you could invoke psql's table pretty-printing stuff
this way, the amount of work required to get there seems vastly out of
proportion to the benefit.

Dear Tom,

Thanks for your help. I agree that changing the protocol would be great
overhead; I'm not really suggesting that. Perhaps I should give an
example of what I mean

(1) Consider the following table, tbl_numbers:

number | english | french | german
----------------------------------------
1 one un ein
2 two deux zwei
3 three trois drei

(2) My desired debug function would be called this:

RAISE NOTICE_DEBUG ("SELECT * from tbl_numbers")

(3) The resulting logfile would then contain multiple separate lines,
each looking a bit like this:

NOTICE: number english french german
NOTICE: 1 one un ein
NOTICE: 2 two deux zwei
NOTICE: 3 three trois drei

While pretty-printing would be nice, I agree it's not really important.
It would be nice to add the same space-padding to each field for
alignment, but delimiting with a single tab would be sufficient.

Richard

2011/3/3 Richard Neill <rjn@richardneill.org>:

Sure it does.  You can pass the tuple to RAISE NOTICE easily enough.
It won't have all the same bells and whistles psql would supply, but
it prints out well enough for debugging.  Or at least it's never
bothered me.

Sorry if I'm being dense, but I can't see how you can pass a tuple; I think
raise-notice only lets you pass individual strings/integers. But I don't
think we can pass all of them without specifying in advance how many there
are....

yes, it's possible for ROW or RECORD datatype

Regards

Pavel Stehule

On Thu, Mar 3, 2011 at 12:37 PM, Richard Neill <rjn@richardneill.org> wrote:

Sure it does.  You can pass the tuple to RAISE NOTICE easily enough.
It won't have all the same bells and whistles psql would supply, but
it prints out well enough for debugging.  Or at least it's never
bothered me.

Sorry if I'm being dense, but I can't see how you can pass a tuple; I think
raise-notice only lets you pass individual strings/integers. But I don't
think we can pass all of them without specifying in advance how many there
are....

raise notice '%', (select array_to_string(array(select foo from foo), E'\n'));

:^).

merlin

Tom Lane <tgl@sss.pgh.pa.us> writes:

Note that doing anything more than RAISE NOTICE or equivalent would
imply a significant protocol change.

My understanding is that the standard allows multiple resultsets per
query, is that the protocol change you're talking about?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

2011/3/3 Dimitri Fontaine <dimitri@2ndquadrant.fr>:

Tom Lane <tgl@sss.pgh.pa.us> writes:

Note that doing anything more than RAISE NOTICE or equivalent would
imply a significant protocol change.

My understanding is that the standard allows multiple resultsets per
query, is that the protocol change you're talking about?

There is nothing similar in standard. Multirecordset is nice, but not
standard feature.

Regards

Pavel Stehule