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 ... ?

Assuming that this is, in fact, 'normal', is there a way of breaking the
deadlock?

If this is *not*, in fact, 'normal', is there any more information that I
can provide to debug this?

This is a 7.4.6 database right now ...

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

"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?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Tue, 31 May 2005, Tom Lane wrote:
>
> The real solution is to upgrade GIST to be concurrent. Oleg and Teodor
> have made some noises about that in the past, but nothing's been done
> about it that I've heard of.

unfortunately, we still couldn't find 2-3 months for dedicated work on
concurrency&recovery for GiST. I'm trying to find support here in Russia
for our work.

>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Regards,
Oleg
__________________________________________________ ___________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(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

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tue, 31 May 2005, Tom Lane wrote:

> (2) we acquire and release the index lock for each *tuple* rather than
> each statement. Then client2 doesn't hold the index lock while it's
> waiting for the row lock to clear.
>
> Neither of these cures sounds attractive :-(. I think #1 would probably
> do as much to create deadlock cases as to prevent them. #2 would avoid
> the deadlock but the performance cost would be high.

But ... this wouldn't affect SELECT operations, would it? And only GiST
related operations? Would the performance loss be noticeable? And, would
the performance cost not be worth getting rid of the deadlocks, until the
concurrency issues can be properly dealt with?

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

---------------------------(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

> The real solution is to upgrade GIST to be concurrent. Oleg and Teodor
> have made some noises about that in the past, but nothing's been done
> about it that I've heard of.

This whole GiST concurrency think really needs to be looked at There
is so much cool stuff that can be done with it, and now that there are
som fantastic uses of it, such as tsearch2 and pg_trgm, people are
starting to run into these walls

WAL logging would also be nice, especially since Tom mentioned in the
manual that it was just a Small Matter Of Programming

Chris


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

> unfortunately, we still couldn't find 2-3 months for dedicated work on
> concurrency&recovery for GiST. I'm trying to find support here in Russia
> for our work.

How much money (US Dollars) would you need?

Chris


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Christopher Kings-Lynne wrote:
>> unfortunately, we still couldn't find 2-3 months for dedicated work on
>> concurrency&recovery for GiST. I'm trying to find support here in Russia
>> for our work.
>
>
> How much money (US Dollars) would you need?
>

Command Prompt could jump on that as well. We could help sponsor a bit.

Sncerely,

Joshua D. Drake


> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org


--
Your PostgreSQL solutions provider, Command Prompt, Inc.
24x7 support - 1.800.492.2240, programming, and consulting
Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit
http://www.commandprompt.com / http://www.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Wed, 2005-06-01 at 09:30 +0800, Christopher Kings-Lynne wrote:
> This whole GiST concurrency think really needs to be looked at

I spent some time looking at it toward the end of last year, but
unfortunately I didn't have enough time to devote to it to get a working
implementation (it is quite a lot of work). I'm still interesting in
working on it, although I won't have any time until after 8.1.

-Neil



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

>> How much money (US Dollars) would you need?
>>
>
> Command Prompt could jump on that as well. We could help sponsor a bit.

Maybe we could start a funding project for it?

USD convert to lots of roubles I assume, so it'd be good like that.
Perhaps someone (not me - too busy) on the PostgreSQL Foundation could
organise contacting some companies to see if we can get a little out of
several

ie. Command Prompt, Fujitsu, Pervasive, EnterpriseDB, etc.?

Oleg & Teodor: how about letting us know what funding target we'd need?

Chris


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster