Client is running 7.4.6 right now (we're looking at moving to 7.4.8 when
we can get a suitable ops window for the upgrade), and is experiencing
semi-regular (10-16 times a day) deadlocks, with the following
information:

A little background:

1) We do not do explicit row or table locking.
2) We use serialized transactions quite often.
3) All deadlocks are fighting for the same relation: 4335271, which is
the tsearch2 fulltext index on the articles table:
articles_idxft1_idx.
4) I have a trigger on the articles table which is executed before
insert or update. It updates the fulltext index column if any
changes are made to the relevant article columns. This is the only
place where changes are made to the fulltext index column.

DETAIL: Process 8122 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 11846.
Process 11846 waits for ShareLock on transaction 2897133915; blocked by process 10042.
Process 10042 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 8122.
----------------------------------
DETAIL: Process 10042 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 11846.
Process 11846 waits for ShareLock on transaction 2897133915; blocked by process 10042.

I haven't had much experience (okay, I've yet to use it) with tsearch2,
but according to
http://www.postgresql.org/docs/8.0/i...mitations.html, GiST does
have concurrency issues "The current implementation of GiST within
PostgreSQL has some major limitations: GiST access is not concurrent"

Could this be what we are experiencing?

This is a wee bit vague, but I'm not sure what direction(s) to look at for
Deadlocks themselves, so pointers/ideas would be much appreciated ...

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> I haven't had much experience (okay, I've yet to use it) with tsearch2,
> but according to
> http://www.postgresql.org/docs/8.0/i...mitations.html, GiST
> does have concurrency issues "The current implementation of GiST within
> PostgreSQL has some major limitations: GiST access is not concurrent"
>
> Could this be what we are experiencing?

All GiST indexes allow only 1 writer at a time. Dunno if that's causing
your headaches. The other thing that causes deadlocks is using foreign
keys...

Chris

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

"Marc G. Fournier" <scrappy@postgresql.org> writes:
> ...
> 3) All deadlocks are fighting for the same relation: 4335271, which is
> the tsearch2 fulltext index on the articles table:
> articles_idxft1_idx.

> DETAIL: Process 8122 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 11846.
> Process 11846 waits for ShareLock on transaction 2897133915; blocked by process 10042.
> Process 10042 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 8122.
> ----------------------------------
> DETAIL: Process 10042 waits for AccessExclusiveLock on relation 4335271 of database 17142; blocked by process 11846.
> Process 11846 waits for ShareLock on transaction 2897133915; blocked by process 10042.

Gist indexes require AccessExclusiveLock for any update, so the blocks
on 4335271 just indicate an index update attempt. The "ShareLock on
transaction" lines suggest a block on a row that is updated or selected
FOR UPDATE. It's hard to say more without more info. Do you have any
related foreign keys, for instance?

regards, tom lane

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

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