Thread: Catching errors inside a function
I want to execute an INSERT query that may fail due to a primary key constraint
check. The primary key is on a field whose value is generated randomly by
another C function.
Is it possible to wrap the insert into a function that checks for failures and
retry the insert until success? Something like this:
function blah
{
do {
cod = generate code;
execute insert with cod as primary key;
} while (! errors);
}
I tried with SQL, PL/pgSQL and C functions but the backend aborts the
transaction and stops execution of the function as soon as the error is thrown.
Is there a way to disable this behaviour?
Thanks in advance for your help.
Francesco Casadei
On Mon, 3 Sep 2001, Francesco Casadei wrote:
> I want to execute an INSERT query that may fail due to a primary key constraint
> check. The primary key is on a field whose value is generated randomly by
> another C function.
Wouldn't it be easier if you just used a sequence to generate the
primary key, if you just want uniqueness?
> Is it possible to wrap the insert into a function that checks for failures and
> retry the insert until success? Something like this:
>
> function blah
> {
> do {
> cod = generate code;
> execute insert with cod as primary key;
> } while (! errors);
> }
Any error will abort the transaction. You can't avoid this. Perhaps you
can try inserting the value outside the transaction, and then open it to
do whatever you want with the value inserted.
> Is there a way to disable this behaviour?
No.
--
Alvaro Herrera (<alvherre[@]atentus.com>)
Why not just do a SELECT to check for existence of the proposed insert
value, and go ahead only if NOT FOUND?
----- Original Message -----
From: "Francesco Casadei" <f_casadei@libero.it>
To: <pgsql-general@postgresql.org>
Sent: Monday, September 03, 2001 1:07 PM
Subject: [GENERAL] Catching errors inside a function
> I want to execute an INSERT query that may fail due to a primary key
constraint
> check. The primary key is on a field whose value is generated randomly by
> another C function.
> Is it possible to wrap the insert into a function that checks for failures
and
> retry the insert until success? Something like this:
>
> function blah
> {
> do {
> cod = generate code;
> execute insert with cod as primary key;
> } while (! errors);
> }
>
> I tried with SQL, PL/pgSQL and C functions but the backend aborts the
> transaction and stops execution of the function as soon as the error is
thrown.
> Is there a way to disable this behaviour?
>
> Thanks in advance for your help.
>
> Francesco Casadei
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
On Tue, Sep 04, 2001 at 11:38:09AM -0500, Jeff Eckermann wrote:
> Why not just do a SELECT to check for existence of the proposed insert
> value, and go ahead only if NOT FOUND?
>
> ----- Original Message -----
> From: "Francesco Casadei" <f_casadei@libero.it>
> To: <pgsql-general@postgresql.org>
> Sent: Monday, September 03, 2001 1:07 PM
> Subject: [GENERAL] Catching errors inside a function
>
>
> > I want to execute an INSERT query that may fail due to a primary key
> constraint
> > check. The primary key is on a field whose value is generated randomly by
> > another C function.
> > Is it possible to wrap the insert into a function that checks for failures
> and
> > retry the insert until success? Something like this:
> >
> > function blah
> > {
> > do {
> > cod = generate code;
> > execute insert with cod as primary key;
> > } while (! errors);
> > }
> >
> > I tried with SQL, PL/pgSQL and C functions but the backend aborts the
> > transaction and stops execution of the function as soon as the error is
> thrown.
> > Is there a way to disable this behaviour?
> >
> > Thanks in advance for your help.
> >
> > Francesco Casadei
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
> end of the original message
The table will likely have about 80000-100000 records. A select, an insert and
the mandatory constraint check are a lot of work for just inserting a new row!!
The previous version of the function that generates the code worked this way
(with a SELECT assuring uniqueness of the code), but it was to slow.
Francesco Casadei
On Mon, Sep 03, 2001 at 03:47:35PM -0400, Alvaro Herrera wrote:
> On Mon, 3 Sep 2001, Francesco Casadei wrote:
>
> > I want to execute an INSERT query that may fail due to a primary key constraint
> > check. The primary key is on a field whose value is generated randomly by
> > another C function.
>
> Wouldn't it be easier if you just used a sequence to generate the
> primary key, if you just want uniqueness?
>
> > Is it possible to wrap the insert into a function that checks for failures and
> > retry the insert until success? Something like this:
> >
> > function blah
> > {
> > do {
> > cod = generate code;
> > execute insert with cod as primary key;
> > } while (! errors);
> > }
>
> Any error will abort the transaction. You can't avoid this. Perhaps you
> can try inserting the value outside the transaction, and then open it to
> do whatever you want with the value inserted.
>
> > Is there a way to disable this behaviour?
>
> No.
>
> --
> Alvaro Herrera (<alvherre[@]atentus.com>)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>
> end of the original message
Mmmmm... I don't remember why I chose not to use a sequence! Actually, it seems
to me a good idea. I will think about it. Thank you for your suggestion.
Francesco Casadei