In response to <[EMAIL PROTECTED]>: > > On 6/14/2007, "Gregory Stark" <[EMAIL PROTECTED]> wrote: > > > > > > ><[EMAIL PROTECTED]> writes: > > > >> But everyone once in a long while it seems that I hit simultaneaous > >> execute() statements that deadlock on the insertion. > > > >What version of Postgres is this and do you have any foreign key constraints > >or triggers on the table you're inserting into? > > Version 8.2 > This table does not have foreign key constraints on it, but it is the > source of foreign key constraints on other tables. > No triggers. > > Is that insert the *only* DML > >you're executing? No updates or deletes? > > At the time of the failure, no other DML. > There are other's but they are on different tables. > > > >What do you mean by saying it deadlocks? Do you get a transaction abort with > >an error about a deadlock detected? Or do you just mean it freezes? > > "deadlock detected" > And the corresponding error I get is a primary key violation on the same > table. > > > The problem occurs when I have multiple processes acting on what appears > to be the exact same set of information. I can't really control the > issue of simultaneous/parallel processing
Put an "ORDER BY" in your SELECT. I believe the problem is that when this runs from two different places, the DB may order the returned values in a different order for each one, which leads to the possibility of two similar inserts deadlocking. Unless I misunderstand your schema, you should be able to guarantee against deadlocking by guaranteeing that the SELECT portion will always return rows in the same order. -- Bill Moran http://www.potentialtech.com ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
