Re: What causes lock?

On Thu, Aug 04, 2005 at 03:03:47PM -0700, Frank Miles wrote:
> I have a python-based application which provides a user interface
> to a Postgresql database. The latest version of the application suddenly
> lost the inability to insert new data in some of the tables. The SQL
> statement(s) used still work if manually typed in psql, but it appears as
> though there are some locks that are persisting and prevent inserts and
> updates when the application is still running. Examining pg_locks shows a
> number of locks (almost all of mode 'AccessShareLock'). Other inserts
> and updates (to other tables) still work. The database logs (default
> noise level setting) don't show any errors.

Do any of the locks have granted = f? That's what you want to look
for if you're seeing blocking. But if the statement works in psql
when it would block or fail in your application, then the problem
might lie elsewhere.

> It would be really helpful to be able to be able to find out what
> is causing the persistent locks. Is there any way to determine what
> table(s),
> function(s), or other database items are involved in the lock?

You can get the relation name by casting the relation column to
regclass:

SELECT relation::regclass AS relname, * FROM pg_locks;

Note that this will resolve only relation names in the current
database.

--
Michael Fuhr