Reporting errors inside plpgsql/SPI queries

Started by Tom Lanealmost 22 years ago3 messages
#1Tom Lane
tgl@sss.pgh.pa.us

I've been fooling with adding a report of the executing query to the
CONTEXT stack when an error happens within a query submitted by a
plpgsql function. Since plpgsql submits all its queries through SPI,
the most convenient place to do this is in spi.c, and so the behavior
will also apply to queries submitted via SPI by user-written C
functions.

What I've currently got labels the failing query as a "SPI query",
for example

regression=# create or replace function foo(text) returns text as $$
regression$# begin
regression$# execute 'select * from ' || $1;
regression$# return 'good';
regression$# end
regression$# $$ language plpgsql;
CREATE FUNCTION

regression=# select foo('int4_tbl');
foo
------
good
(1 row)

regression=# select foo('nosuch_tbl');
ERROR: relation "nosuch_tbl" does not exist
CONTEXT: SPI query "select * from nosuch_tbl"
PL/pgSQL function "foo" line 2 at execute statement

regression=# select foo('fee fie fo fum');
ERROR: syntax error at or near "fo" at character 23
CONTEXT: SPI query "select * from fee fie fo fum"
PL/pgSQL function "foo" line 2 at execute statement

Although this is quite reasonable for queries submitted by user-written
C functions, I'm worried that plpgsql programmers will be confused
because they've never heard of SPI. I toyed with saying "SQL query"
instead, but that seems pretty nearly content-free ... it doesn't
distinguish these queries from ones submitted directly by the client.
Can anyone think of a better wording? Does this bother people enough
to justify hacking the SPI interface to allow a label to be passed in?

regards, tom lane

#2Joe Conway
mail@joeconway.com
In reply to: Tom Lane (#1)
Re: Reporting errors inside plpgsql/SPI queries

Tom Lane wrote:

Although this is quite reasonable for queries submitted by
user-written C functions, I'm worried that plpgsql programmers will
be confused because they've never heard of SPI. I toyed with saying
"SQL query" instead, but that seems pretty nearly content-free ... it
doesn't distinguish these queries from ones submitted directly by the
client. Can anyone think of a better wording?

"Embedded query"?

Does this bother people enough to justify hacking the SPI interface
to allow a label to be passed in?

That may be the only way to have the message make sense in all contexts.

Joe

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Joe Conway (#2)
Re: Reporting errors inside plpgsql/SPI queries

Joe Conway <mail@joeconway.com> writes:

Tom Lane wrote:

Does this bother people enough to justify hacking the SPI interface
to allow a label to be passed in?

That may be the only way to have the message make sense in all contexts.

Could be. For the moment I committed the code using "SQL query" (maybe
content-free, but by the same token reasonably likely to apply
everywhere...) I'm still open to alternatives though.

regards, tom lane