pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: Problems handling errors in PL/pgSQL
Date: Mon, 23 Apr 2001 12:48:57 -0700
Message-ID: <web-43127@davinci.ethosmedia.com> (raw)

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



view thread (7+ messages)  latest in thread

Message-ID: <web-43127@davinci.ethosmedia.com>
Permalink:  ../web-43127@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-43127@davinci.ethosmedia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com
  Subject: Re: Problems handling errors in PL/pgSQL
  In-Reply-To: <web-43127@davinci.ethosmedia.com>
  References: <web-43127@davinci.ethosmedia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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