Thread: BUG #5867: wish: plpgsql print table for debug
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.
On Mon, Feb 7, 2011 at 1:01 AM, Richard Neill <postgresql@richardneill.org> wrote: > > The following bug has been logged online: > > Bug reference: =A0 =A0 =A05867 > Logged by: =A0 =A0 =A0 =A0 =A0Richard Neill > Email address: =A0 =A0 =A0postgresql@richardneill.org > PostgreSQL version: 9.03 > Operating system: =A0 Linux > Description: =A0 =A0 =A0 =A0wish: 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: > > =A0 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. --=20 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
> Thanks very much,
>
> Richard
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
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
On Thu, Mar 3, 2011 at 1:37 PM, Richard Neill <rjn@richardneill.org> wrote:
>
>> Sure it does. =A0You 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. =A0Or 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 thi=
nk
> 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=3D# select debug_query('SELECT * FROM foo');
NOTICE: (1,Richard)
NOTICE: (2,Robert)
NOTICE: (3,Tom)
debug_query
-------------
(1 row)
--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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....
> 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. =C2=A0You 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. =C2=A0Or 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 thi= nk > 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 > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
On Thu, Mar 3, 2011 at 12:37 PM, Richard Neill <rjn@richardneill.org> wrote: > >> Sure it does. =A0You 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. =A0Or 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 thi= nk > 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 > Regards, > -- > Dimitri Fontaine > http://2ndQuadrant.fr =C2=A0 =C2=A0 PostgreSQL : Expertise, Formation et = Support > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >