Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
2011/3/3 Dimitri Fontaine : > Tom Lane 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 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 > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
Tom Lane 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
On Thu, Mar 3, 2011 at 12:37 PM, Richard Neill 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
2011/3/3 Richard Neill : > >> 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 > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
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 threetroisdrei
(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: numberenglishfrenchgerman
NOTICE: 1 oneunein
NOTICE: 2 twodeux 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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
On Thu, Mar 3, 2011 at 1:37 PM, Richard Neill 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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
Robert Haas writes: > On Thu, Mar 3, 2011 at 12:12 PM, Richard Neill 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
On Thu, Mar 3, 2011 at 12:12 PM, Richard Neill 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
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 > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
The following bug has been logged online: Bug reference: 5867 Logged by: Richard Neill Email address: postgre...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5867: wish: plpgsql print table for debug
On Mon, Feb 7, 2011 at 1:01 AM, Richard Neill wrote: > > The following bug has been logged online: > > Bug reference: 5867 > Logged by: Richard Neill > Email address: postgre...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5867: wish: plpgsql print table for debug
The following bug has been logged online: Bug reference: 5867 Logged by: Richard Neill Email address: postgre...@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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
