Catching errors inside a function

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.

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