Обсуждение: Abort Transaction DP PK (again)

Hi,

I know that this topic was discuted before but I like an alternative for
my high load INSERT query.
Why Postgres abort a transaction when find a duplicate PK ?!?!

A simple Fuction test:

(...) SELECT a,b from foo where a=1 and b=1 IF NOT FOUND   INSERT INTO foo VALUES(1,1) ELSE   UPDATE
(...)

Is, obviously, very very much slower than this (hipotetic) function:

(...) INSERT INTO foo IF "ERROR DP PK"   UPDATE
(...)

In both cases, INSERT will test if values violate the PK... but in first
example the extra SELECT cause an unnecessary extreame work...

Thanks,

Alexandre

Alexandre,

> I know that this topic was discuted before but I like an alternative for
> my high load INSERT query.
> Why Postgres abort a transaction when find a duplicate PK ?!?!

Postgres currently aborts a transaction upon *any* error condition, except 
warnings.  We do not, currently, have a exception-handling model for 
procedure code.  It's on the TODO list.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Josh,

It´s very difficult to change the behaviour of a duplicate PK, from
"error" to "warning"?

It´s possible, in a future release, to have an exception-handling for all
client interfaces, like ODBC or JDBC?

Thanks,

Alexandre

> Alexandre,
>
>> I know that this topic was discuted before but I like an alternative for
>> my high load INSERT query.
>> Why Postgres abort a transaction when find a duplicate PK ?!?!
>
> Postgres currently aborts a transaction upon *any* error condition, except
> warnings.  We do not, currently, have a exception-handling model for
> procedure code.  It's on the TODO list.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Alexandre,

> It´s very difficult to change the behaviour of a duplicate PK, from
> "error" to "warning"?

That would be a very bad idea because then the duplicate row would be inserted
anyway.   And you'd have to hack the source code.

> It´s possible, in a future release, to have an exception-handling for all
> client interfaces, like ODBC or JDBC?

I'm not quite sure what you mean.

--
-Josh BerkusAglio Database SolutionsSan Francisco