pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problems handling errors in PL/pgSQL
7+ messages / 3 participants
[nested] [flat]

* Problems handling errors in PL/pgSQL
@ 2001-04-23 19:48 Josh Berkus <josh@agliodbs.com>
  2001-04-23 23:11 ` Re: Problems handling errors in PL/pgSQL Joe Conway <jconway2@home.com>
  2001-04-23 23:57 ` Re: Problems handling errors in PL/pgSQL Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 7+ messages in thread

From: Josh Berkus @ 2001-04-23 19:48 UTC (permalink / raw)
  To: pgsql-sql

Folks,

	I've run up against a problematic limitation of PL/pgSQL's
error-handling ability which could force me to re-write about 25 custom
functions.  I'm hoping that you folks can show me a way around the
situation.

THE PROBLEM:

PL/pgSQL handles errors though "Implied Transactions", where the entire
function is a transaction and rolls back in the event that an error is
encountered.  There is no way to declare a transaction within a PL/pgSQL
function, nor can one issue a ROLLBACK or COMMIT statement within a
function. As such, I have designed all of my data-modifiaction funcitons
to take advantage of this functionality, packaging all updates within a
single function.

However, not all types of errors are so trapped.  The most problematic
un-trapped error is referential integrity:  if an INSERT or UPDATE fails
because of a referential integrity violation, the PL/pgSQL function will
still see the statement as a success and not error out.  Example:

Postgres ver. 7.1 RC2

CREATE TABLE "order_details" (
	"order_detail_id" integer DEFAULT nextval('order_details_order_detail__seq'::text)
NOT NULL,
	"order_usq" integer NOT NULL REFERENCES orders(usq),
	"detail_id" integer NOT NULL,
	"detail_req" boolean,
	Constraint "order_details_pkey" Primary Key ("order_detail_id")
);

CREATE FUNCTION "fn_save_order_details" (integer,integer[],boolean[])
RETURNS integer AS '
DECLARE
	v_order ALIAS for $1;
	arr_details ALIAS for $2;
	arr_req ALIAS for $3;
	arr_loop INT2;
	detail_no INT4;
	detail_r BOOLEAN;
BEGIN
	DELETE FROM order_details
	WHERE order_usq = v_order;

	arr_loop := 1;

	WHILE arr_details[arr_loop] LOOP
		detail_no := arr_details[arr_loop];
		detail_r := COALESCE(arr_req[arr_loop], FALSE);
		INSERT INTO order_details ( order_usq, detail_id, detail_req )
		VALUES ( v_order, detail_no, detail_r );
		arr_loop := arr_loop + 1;
	END LOOP;

	RETURN arr_loop - 1;
END;
' LANGUAGE 'plpgsql';

SELECT fn_save_order_details (7703, '{34,29,40}','{TRUE, TRUE, FALSE}');

---------
	3

... thus supposedly reporting success: 3 order_details were saved.

However, it turns out that order 7703 has been deleted.  Thus, the three
INSERTS we ran on order_details failed due to lack of referential
integrity; no records were saved.  Yet the function did not error.


THE QUESTIONS:  

1. Based on the above, it seems I have to go back and add data
validation and RAISE ERROR statements to all of my functions that do
INSERTS or UPDATES to tables with referential integrity triggers.  Is
there a way around this?

2. Is there a plan to fix this kind of deficiency in Postgres
function/procedure error handling?

Thanks so much for your suggestions,

-Josh



______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Problems handling errors in PL/pgSQL
  2001-04-23 19:48 Problems handling errors in PL/pgSQL Josh Berkus <josh@agliodbs.com>
@ 2001-04-23 23:11 ` Joe Conway <jconway2@home.com>
  2001-04-23 23:31   ` Re: Problems handling errors in PL/pgSQL Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Joe Conway @ 2001-04-23 23:11 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; pgsql-sql

> However, not all types of errors are so trapped.  The most problematic
> un-trapped error is referential integrity:  if an INSERT or UPDATE fails
> because of a referential integrity violation, the PL/pgSQL function will
> still see the statement as a success and not error out.  Example:
>

I'm not sure if this is what you're looking for, but in 7.1 you can do
something like:

    INSERT INTO bar(barpk,foopk) VALUES(barpkval,foopkval);
    GET DIAGNOSTICS rows = ROW_COUNT;
    -- do something based on rows --

See "24.2.5.4. Obtaining other results status" at
http://postgresql.readysetnet.com/users-lounge/docs/7.1/postgres/plpgsql-des
cription.html.

Hope this helps,

Joe






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Problems handling errors in PL/pgSQL
  2001-04-23 19:48 Problems handling errors in PL/pgSQL Josh Berkus <josh@agliodbs.com>
  2001-04-23 23:11 ` Re: Problems handling errors in PL/pgSQL Joe Conway <jconway2@home.com>
@ 2001-04-23 23:31   ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Josh Berkus @ 2001-04-23 23:31 UTC (permalink / raw)
  To: pgsql-sql

Joe,

> I'm not sure if this is what you're looking for, but in 7.1 you can
> do
> something like:
> 
>     INSERT INTO bar(barpk,foopk) VALUES(barpkval,foopkval);
>     GET DIAGNOSTICS rows = ROW_COUNT;
>     -- do something based on rows --

There's several other ways I can check, as well.  However, I was hoping
for some way to avoid adding anything to a couple of dozen functions
which are already debugged.

-Josh 

P.S. Does anyone yet have full documentation on GET DIAGNOSICS?
Roberto?

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Problems handling errors in PL/pgSQL
  2001-04-23 19:48 Problems handling errors in PL/pgSQL Josh Berkus <josh@agliodbs.com>
@ 2001-04-23 23:57 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-04-24 00:15   ` Re: Problems handling errors in PL/pgSQL Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Tom Lane @ 2001-04-23 23:57 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"Josh Berkus" <josh@agliodbs.com> writes:
> However, not all types of errors are so trapped.  The most problematic
> un-trapped error is referential integrity:  if an INSERT or UPDATE fails
> because of a referential integrity violation, the PL/pgSQL function will
> still see the statement as a success and not error out.

This would clearly be a bug, but I cannot replicate the problem:

regression=# SELECT fn_save_order_details (7703, '{34,29,40}','{TRUE, TRUE, FALSE}');
ERROR:  <unnamed> referential integrity violation - key referenced from order_details not found in orders
regression=#

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Problems handling errors in PL/pgSQL
  2001-04-23 19:48 Problems handling errors in PL/pgSQL Josh Berkus <josh@agliodbs.com>
  2001-04-23 23:57 ` Re: Problems handling errors in PL/pgSQL Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-04-24 00:15   ` Josh Berkus <josh@agliodbs.com>
  2001-04-24 00:19     ` Re: Problems handling errors in PL/pgSQL Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Josh Berkus @ 2001-04-24 00:15 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Tom,

> This would clearly be a bug, but I cannot replicate the problem:
> 
> regression=# SELECT fn_save_order_details (7703, '{34,29,40}','{TRUE,
> TRUE, FALSE}');
> ERROR:  <unnamed> referential integrity violation - key referenced
> from order_details not found in orders
> regression=#

Always good to have you folks test something.  This does appear to be a
bug ... in KPSQL (the KDE GUI for PSQL), not in Postgres.  When I run it
through command-line PSQL, an error is returned; for some reason, KPSQL
returns the return value for the function and no error.

Grazie!

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Problems handling errors in PL/pgSQL
  2001-04-23 19:48 Problems handling errors in PL/pgSQL Josh Berkus <josh@agliodbs.com>
  2001-04-23 23:57 ` Re: Problems handling errors in PL/pgSQL Tom Lane <tgl@sss.pgh.pa.us>
  2001-04-24 00:15   ` Re: Problems handling errors in PL/pgSQL Josh Berkus <josh@agliodbs.com>
@ 2001-04-24 00:19     ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-04-24 00:26       ` Re: Problems handling errors in PL/pgSQL Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2001-04-24 00:19 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"Josh Berkus" <josh@agliodbs.com> writes:
> Always good to have you folks test something.  This does appear to be a
> bug ... in KPSQL (the KDE GUI for PSQL), not in Postgres.  When I run it
> through command-line PSQL, an error is returned; for some reason, KPSQL
> returns the return value for the function and no error.

But how would KPSQL know what value the function might have returned?
Something fishy here ... did you check the postmaster log to see whether
an error is really being reported or not?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Problems handling errors in PL/pgSQL
  2001-04-23 19:48 Problems handling errors in PL/pgSQL Josh Berkus <josh@agliodbs.com>
  2001-04-23 23:57 ` Re: Problems handling errors in PL/pgSQL Tom Lane <tgl@sss.pgh.pa.us>
  2001-04-24 00:15   ` Re: Problems handling errors in PL/pgSQL Josh Berkus <josh@agliodbs.com>
  2001-04-24 00:19     ` Re: Problems handling errors in PL/pgSQL Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-04-24 00:26       ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Josh Berkus @ 2001-04-24 00:26 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Tom,

> But how would KPSQL know what value the function might have returned?
> Something fishy here ... did you check the postmaster log to see
> whether
> an error is really being reported or not?

<sheepish grin> Actually, I haven't been able to get the postgresql log
to work since I compiled RC2.  I keep meaning to take it up with PGSQL
Inc. but not getting around to it ...

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2001-04-24 00:26 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-23 19:48 Problems handling errors in PL/pgSQL Josh Berkus <josh@agliodbs.com>
2001-04-23 23:11 ` Joe Conway <jconway2@home.com>
2001-04-23 23:31   ` Josh Berkus <josh@agliodbs.com>
2001-04-23 23:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-24 00:15   ` Josh Berkus <josh@agliodbs.com>
2001-04-24 00:19     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-24 00:26       ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox