Thread: Query on exception handling in PL/pgSQL

Query on exception handling in PL/pgSQL

From
diya das
Date:
Hi,

  I am just a beginner in postgreSQL and writing some
functions in PL/pgSQL. I use the libpq interface in
the client to call this funtions.  My pgsql function
does an insert to a table and I have opened a
transaction block before calling my function from the
client. When the insert operation fails due to unique
key violation the whole transaction aborts. Is there a
way I can handle this error in my pgsql funtion rather
that aborting and not executing the rest of the
operations?. I have a workaround , But I was wondering
if there is an inexpensive way of doing it.

Another query I have is related to returning values
from funtions. How can I return multiple values from a
function? Is there any support for OUT variable in
pgsql functions?
Any pointers on this is greatly appreciated.

Thanks
Diya.



__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail

Re: Query on exception handling in PL/pgSQL

From
Tom Lane
Date:
diya das <diyadas@yahoo.com> writes:
> My pgsql function
> does an insert to a table and I have opened a
> transaction block before calling my function from the
> client. When the insert operation fails due to unique
> key violation the whole transaction aborts. Is there a
> way I can handle this error in my pgsql funtion rather
> that aborting and not executing the rest of the
> operations?

Not before PG 8.0.  In earlier versions you might be able to work around
the problem by testing for existence of the target key before you
insert; though this has obvious race-condition problems if you expect
multiple clients to be doing it simultaneously.

            regards, tom lane

Re: Query on exception handling in PL/pgSQL

From
Victor Ciurus
Date:
And how does exactly PG8.0 help us with this?

Tom, coould you be so kind and tell me/us when will PG incorporate
such a hugely claimed feature like "copy table_x from 'file_y.txt'
(using delimiters) IGNORE ON DUPLICATES"?

I do LOVE PG and I'm very pleased with its overall features level
still the lack of "ingore" on unique key constraint insert (especially
for large bulk inserts!) makes me feel quite helpless!

Or, is there another way, like in Oracle's exception handling, to
hanle this thing (COPY/INSERTs with Ignore?) I can't emagine how such
a 'simple' (no offense!) db like MySQL can hanlde INSERT IGONERS (not
to mention the 'so serious' ORACLE, INFORMIX , MSSQL and prolly DB2)
and PG not beeing able to do this!

Please enlighten me! I saw a huge deabte over this feature, dated
2001-2002, ended up on a 'promise' that such feature will be made
available staring with PG 7.1 or 7.2 (can't remeber exactly!) still no
sign of improvement on it!

Best regards,
Victor


On Fri, 26 Nov 2004 14:06:52 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> diya das <diyadas@yahoo.com> writes:
>
>
> > My pgsql function
> > does an insert to a table and I have opened a
> > transaction block before calling my function from the
> > client. When the insert operation fails due to unique
> > key violation the whole transaction aborts. Is there a
> > way I can handle this error in my pgsql funtion rather
> > that aborting and not executing the rest of the
> > operations?
>
> Not before PG 8.0.  In earlier versions you might be able to work around
> the problem by testing for existence of the target key before you
> insert; though this has obvious race-condition problems if you expect
> multiple clients to be doing it simultaneously.
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

Re: Query on exception handling in PL/pgSQL

From
Joachim Zobel
Date:
Am Fr, den 26.11.2004 schrieb diya das um 14:28:
>   I am just a beginner in postgreSQL and writing some
> functions in PL/pgSQL. I use the libpq interface in
> the client to call this funtions.  My pgsql function
> does an insert to a table and I have opened a
> transaction block before calling my function from the
> client. When the insert operation fails due to unique
> key violation the whole transaction aborts. Is there a
> way I can handle this error in my pgsql funtion rather
> that aborting and not executing the rest of the
> operations?. I have a workaround , But I was wondering
> if there is an inexpensive way of doing it.

Probably you want to do a write operation (UPDATE if the record exists,
INSERT otherwise). You should be aware that trying the INSERT first and
then UPDATEing if it fails is a bad way to do this. If the constraint is
ever accidentially turned of, your data gets corrupted. Provoking
exceptions for situations that are not exceptional is IMHO bad
programming practice anyway.

The good way is either (portable) to do a SELECT to check for existence
of the record or to do the UPDATE first and check FOUND:

UPDATE ...
WHERE key=$1

IF NOT FOUND THEN
  INSERT...
END IF;

Sincerely,
Joachim

--
"... ein Geschlecht erfinderischer Zwerge, die fuer alles gemietet werden
koennen."                            - Bertolt Brecht - Leben des Galilei


Re: Query on exception handling in PL/pgSQL

From
Alvaro Herrera
Date:
On Sat, Nov 27, 2004 at 11:37:34AM +0200, Victor Ciurus wrote:
> And how does exactly PG8.0 help us with this?

You can use BEGIN ... EXCEPTION ... END blocks.  It doesn't help you
with COPY/IGNORE ON DUPLICATES though.

> Tom, coould you be so kind and tell me/us when will PG incorporate
> such a hugely claimed feature like "copy table_x from 'file_y.txt'
> (using delimiters) IGNORE ON DUPLICATES"?

I think the canonical answer is "when somebody implements it".  _If_
she happens to convince the committers that it is a good idea to do
it her way.

If you desperately need that feature, you could code it yourself of
convince some hacker to do it for you (possibly paying but maybe not).

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Tiene valor aquel que admite que es un cobarde" (Fernandel)

Re: Query on exception handling in PL/pgSQL

From
Johan Wehtje
Date:
I would second this motion as it relates to bulk inserts, I also love
PG, but it's bulk insert/copy from file routines are definitely one area
where it cannot claim to be "most advanced".

My own wish list includes :
*Copy to a new table - so that a table in the db is created - default is
for all fields to be a relaxed varchar, but there should be an option of
using a  list of field names paired with data types in the copy command.
* specify a start row , a la MS SQL - so a file that might have 2 rows
of header,column information - or in the case of many Terminal captures
an arbitrary number of blank lines..
*support for using the first rows values as field names when copying to
a new table - or using them to map columns in the file to the
destination table.
*Specify row delimiter - so a row delimiter can be other than a new line
character - or can be a character combination. This would help when
dumping multi line text field values from one DB and loading them in
another without having to use the backslash escaped newline method.
*Commit after - specify number of rows to read before committing - and
behavior on error, ideally if I have 100,000 rows in a bulk copy and
there was one error in line 45,000 the server would write a file with
the same name as the input file + an extension like err, with just that
row omitted . Also it would load 100 rows at a time - perform any logging.

Johan Wehtje

Victor Ciurus wrote:

>And how does exactly PG8.0 help us with this?
>
>Tom, coould you be so kind and tell me/us when will PG incorporate
>such a hugely claimed feature like "copy table_x from 'file_y.txt'
>(using delimiters) IGNORE ON DUPLICATES"?
>
>I do LOVE PG and I'm very pleased with its overall features level
>still the lack of "ingore" on unique key constraint insert (especially
>for large bulk inserts!) makes me feel quite helpless!
>
>Or, is there another way, like in Oracle's exception handling, to
>hanle this thing (COPY/INSERTs with Ignore?) I can't emagine how such
>a 'simple' (no offense!) db like MySQL can hanlde INSERT IGONERS (not
>to mention the 'so serious' ORACLE, INFORMIX , MSSQL and prolly DB2)
>and PG not beeing able to do this!
>
>Please enlighten me! I saw a huge deabte over this feature, dated
>2001-2002, ended up on a 'promise' that such feature will be made
>available staring with PG 7.1 or 7.2 (can't remeber exactly!) still no
>sign of improvement on it!
>
>Best regards,
>Victor
>
>
>On Fri, 26 Nov 2004 14:06:52 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>
>>diya das <diyadas@yahoo.com> writes:
>>
>>
>>
>>
>>>My pgsql function
>>>does an insert to a table and I have opened a
>>>transaction block before calling my function from the
>>>client. When the insert operation fails due to unique
>>>key violation the whole transaction aborts. Is there a
>>>way I can handle this error in my pgsql funtion rather
>>>that aborting and not executing the rest of the
>>>operations?
>>>
>>>
>>Not before PG 8.0.  In earlier versions you might be able to work around
>>the problem by testing for existence of the target key before you
>>insert; though this has obvious race-condition problems if you expect
>>multiple clients to be doing it simultaneously.
>>
>>                        regards, tom lane
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 7: don't forget to increase your free space map settings
>>
>>
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>.
>
>
>