Abort Transaction DP PK (again)

Lists: pgsql-sql
From: "alexandre :: aldeia digital" <alexandre(at)aldeiadigital(dot)com(dot)br>
To: pgsql-sql(at)postgresql(dot)org
Subject: Abort Transaction DP PK (again)
Date: 2003-08-07 17:04:30
Message-ID: 1108.200.170.156.137.1060275870.squirrel@webmail.ad2.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

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

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: "alexandre :: aldeia digital" <alexandre(at)aldeiadigital(dot)com(dot)br>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Abort Transaction DP PK (again)
Date: 2003-08-08 16:37:33
Message-ID: 200308080937.34002.josh@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

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

From: "alexandre :: aldeia digital" <alepaes(at)aldeiadigital(dot)com(dot)br>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Abort Transaction DP PK (again)
Date: 2003-08-08 20:22:26
Message-ID: 4150.192.168.1.100.1060374146.squirrel@webmail.ad2.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

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(at)postgresql(dot)org)
>

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: "alexandre :: aldeia digital" <alepaes(at)aldeiadigital(dot)com(dot)br>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Abort Transaction DP PK (again)
Date: 2003-08-08 21:45:44
Message-ID: 200308081445.44651.josh@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

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 Berkus
Aglio Database Solutions
San Francisco