From: | Dennis Gearon <gearond(at)cvc(dot)net> |
---|---|
To: | Mike Mascari <mascarm(at)mascari(dot)com> |
Cc: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>, Ian Barwick <barwick(at)gmx(dot)net>, techlist(at)voyager(dot)phys(dot)utk(dot)edu, pgsql-general(at)postgresql(dot)org |
Subject: | Re: INSERT WHERE NOT EXISTS |
Date: | 2003-06-25 21:22:10 |
Message-ID: | 3EFA1282.6000503@cvc.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
And what might a future version of PostgreSQL, or current versions of other RDMBS's do to prevent that?
Mike Mascari wrote:
> scott.marlowe wrote:
>
>>On Wed, 25 Jun 2003, Ian Barwick wrote:
>>
>>
>>>On Wednesday 25 June 2003 21:37, Mike Mascari wrote:
>>>
>>>
>>>>I proposed that same solution 3 years ago. Tom shoots it down:
>>>>
>>>>http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3A4D611
>>>>6.1A613402%40mascari.com&rnum=1&prev=/groups%3Fq%3DMike%2BMascari%2BINSERT%2
>>>>BNOT%2BEXISTS%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den
>>>>
>>>>Reuben must be prepared for unique key violation, I'm afraid. And,
>>>>despite the optimism in the link, we still don't have savepoints. :-(
>>>
>>>aha, useful to know. Thanks.
>>
>>Oh yeah, in my example you need to do a select for update to be race safe.
>>
>
>
> But if two simultaneous "selects for update" fail to find rows, both
> clients will then attempt the INSERT, which will cause one of them to
> abort due to a unique key violation. In these "replace" scenarios, the
> application must be prepared for the unique key violation with the
> current version of PostgreSQL.
>
> Mike Mascari
> mascarm(at)mascari(dot)com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2003-06-25 21:40:47 | Re: [GENERAL] capturing and storing query statement with |
Previous Message | Reuven M. Lerner | 2003-06-25 21:13:49 | Re: Many Pl/PgSQL parameters -> AllocSetAlloc(128)? |