Problem with table lock within a function

From: Daniel Cristian Cruz <danielcristian(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Problem with table lock within a function
Date: 2009-04-08 19:38:50
Message-ID: 48d0cacb0904081238u197f9967sa40f9c20192e311f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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
クルズ クリスチアン ダニエル

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2009-04-08 20:02:31 Re: Problem with table lock within a function
Previous Message Tom Lane 2009-04-08 18:17:57 Re: DEBUG message: concurrent ROOT page split