Обсуждение: Query on exception handling in PL/pgSQL
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
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
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 >
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
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)
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 > >. > > >