Re: Problem with table lock within a function - Mailing list pgsql-admin

From Daniel Cristian Cruz
Subject Re: Problem with table lock within a function
Date
Msg-id 48d0cacb0904081302v7382609at2092dc5aefad9dec@mail.gmail.com
Whole thread Raw
In response to Problem with table lock within a function  (Daniel Cristian Cruz <danielcristian@gmail.com>)
List pgsql-admin
Sorry... Long time not using pg_locks...

My function (on pid 20125) was running, pid 25743 requested an exclusive lock and is waiting, and all others were waiting for this one.

2009/4/8 Daniel Cristian Cruz <danielcristian@gmail.com>
Hi all,

I had a big function (the same one mentioned before) which is locking a table, where it doesn't use the table for updates, just selects. (PostgreSQL 8.2)

My pg_locks had:

sgn=# SELECT * FROM pg_locks WHERE relation = 1706665;
 locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction |  pid  |        mode         | granted
----------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+---------------------+---------
 relation |  1705022 |  1706665 |      |       |               |         |       |          |   160710046 | 20125 | AccessShareLock     | t
 relation |  1705022 |  1706665 |      |       |               |         |       |          |   160721896 | 24198 | AccessShareLock     | f
 relation |  1705022 |  1706665 |      |       |               |         |       |          |   160721876 | 25744 | AccessShareLock     | f
 relation |  1705022 |  1706665 |      |       |               |         |       |          |   160721874 | 25743 | AccessExclusiveLock | f
(4 registros)


What could be wrong? How could I get an access share lock only using select? Any way to avoid it? My fuction runs for 3 minutes and every developer is trying to kill me, because they are waiting for their results.

Regards,
--
Daniel Cristian Cruz
クルズ  クリスチアン ダニエル



--
Daniel Cristian Cruz
クルズ  クリスチアン ダニエル

pgsql-admin by date:

Previous
From: Tom Lane
Date:
Subject: Re: Problem with table lock within a function
Next
From: fatih ozturk
Date:
Subject: log_statements = mod or all