Re: Deadlock with tsearch2 index ...

On Tue, 31 May 2005, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@postgresql.org> writes:
>> Just want to make sure that this is, in fact, what is expected:
>
>> client1: begin;
>> client1: update articles set some_col = <foo> where id = <bar>;
>> client2: update articles set some_col2 = <foo2> where id = <bar>;
>> client1: update articles set some_col3 = <foo> where id = <bar>;
>> client1: ** deadlock **
>
>> client2 can't finish its 'transaction', and is therefore preventing
>> client1 from continuing ... ?
>
> Hmm, I can see why client2 might be blocked by client1, but if client1
> holds the lock it should be able to continue to update the table.
>
> I take it from your title that this only happens if there's a tsearch2
> index on the table?  Can you put together a test case?

I haven't tried this myself, but the client wrote this very quick script 
that apparently recreates it every time:

test.sql:
---------------------------------------
/* load contrib/btree_gist.sql into database */

begin;

create table test ( id serial primary key, age int
);
create index test_age_key on test using gist(age);

insert into test values (nextval('test_id_seq'), 1);

commit;

/*

client1:
begin; update test set age = 2 where id = 1;
client2:
update test set age = 2 where id = 1;
client1:
update test set age = 2 where id = 1;
...deadlock...

*/


----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664