Re: [GENERAL] deadlock while re-indexing table

2008-02-12 Thread Dave Cramer
On 12-Feb-08, at 5:05 PM, Tom Lane wrote: Dave Cramer <[EMAIL PROTECTED]> writes: The other process is inserting into the user_profile table. Did either transaction do anything else in the same transaction previously? It would appear that the insert is running before the reindex starts.

Re: [GENERAL] deadlock while re-indexing table

2008-02-12 Thread Tom Lane
Dave Cramer <[EMAIL PROTECTED]> writes: >>> The other process is inserting into the user_profile table. >> >> Did either transaction do anything else in the same transaction >> previously? >> > It would appear that the insert is running before the reindex starts. That's not possible --- if i

Re: [GENERAL] deadlock while re-indexing table

2008-02-12 Thread Dave Cramer
On 12-Feb-08, at 1:02 PM, Gregory Stark wrote: "Dave Cramer" <[EMAIL PROTECTED]> writes: On 12-Feb-08, at 10:37 AM, Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: Dave Cramer wrote: reindex table user_profile; ERROR: deadlock detected DETAIL: Process 32450 waits for AccessEx

Re: [GENERAL] deadlock while re-indexing table

2008-02-12 Thread Gregory Stark
"Dave Cramer" <[EMAIL PROTECTED]> writes: > On 12-Feb-08, at 10:37 AM, Tom Lane wrote: > >> Alvaro Herrera <[EMAIL PROTECTED]> writes: >>> Dave Cramer wrote: reindex table user_profile; ERROR: deadlock detected DETAIL: Process 32450 waits for AccessExclusiveLock on relation 1

Re: [GENERAL] deadlock while re-indexing table

2008-02-12 Thread Dave Cramer
On 12-Feb-08, at 10:37 AM, Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: Dave Cramer wrote: reindex table user_profile; ERROR: deadlock detected DETAIL: Process 32450 waits for AccessExclusiveLock on relation 194689112 of database 163880909; blocked by process 31236. Process 31

Re: [GENERAL] deadlock while re-indexing table

2008-02-12 Thread Gregory Stark
"Dave Cramer" <[EMAIL PROTECTED]> writes: > reindex table user_profile; > ERROR: deadlock detected > DETAIL: Process 32450 waits for AccessExclusiveLock on relation 194689112 of > database 163880909; blocked by process 31236. > Process 31236 waits for AccessShareLock on relation 194689110 of d

Re: [GENERAL] deadlock while re-indexing table

2008-02-12 Thread Tom Lane
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Can we rework REINDEX TABLE so that it processes each index on its own > transaction? It still wouldn't be guaranteed deadlock-free. There might be fewer cases, but whether it would help Dave's particular case is just speculation when we don't know wha

Re: [GENERAL] deadlock while re-indexing table

2008-02-12 Thread Alvaro Herrera
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > I don't find this very surprising ... I would suggest using "reindex > > index" for each index instead. I'm not sure if REINDEX TABLE is > > supposed to be deadlock-free. > > It's not guaranteed to be so, but I'd think simple cases

Re: [GENERAL] deadlock while re-indexing table

2008-02-12 Thread Tom Lane
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Dave Cramer wrote: >> reindex table user_profile; >> ERROR: deadlock detected >> DETAIL: Process 32450 waits for AccessExclusiveLock on relation >> 194689112 of database 163880909; blocked by process 31236. >> Process 31236 waits for AccessShareLock

Re: [GENERAL] deadlock while re-indexing table

2008-02-12 Thread Alvaro Herrera
Dave Cramer wrote: > reindex table user_profile; > ERROR: deadlock detected > DETAIL: Process 32450 waits for AccessExclusiveLock on relation > 194689112 of database 163880909; blocked by process 31236. > Process 31236 waits for AccessShareLock on relation 194689110 of > database 163880909; b

[GENERAL] deadlock while re-indexing table

2008-02-12 Thread Dave Cramer
reindex table user_profile; ERROR: deadlock detected DETAIL: Process 32450 waits for AccessExclusiveLock on relation 194689112 of database 163880909; blocked by process 31236. Process 31236 waits for AccessShareLock on relation 194689110 of database 163880909; blocked by process 32450. jnj=