Re: What causes lock??

On Thu, 4 Aug 2005, Michael Fuhr wrote:

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

Sorry for being unclear (to you too, Jaime).  The statements will _not_
execute in psql when the application is running; they will only execute
when the app has been shut down.  That is, in psql the SQL statement hangs
until the app exits.  This is for the forced case, see below.

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

Aaaahhh!  That looks helpful.  Thanks!!

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

All show granted='t' with just the app running.  If I really force things, and
try to update the same bit of data from psql, yes I can get a granted= 'f',
but that clearly isn't what's happening with the app failing all by
itself.  By the way, in this forced condition, the rows that show granted='f'
have blank relname, relation, and database fields :(

Well... I'm less and less inclined to think that the update/insert failure
is due to a lock problem.  Weird!  Perhaps the python/psycopg interface?
I've never had a case where the python/psycopg invocation worked differently
than with the psql interface.  It may yet be time to build the psycopg from
source, as the Debian version is pretty old...

> --
> Michael Fuhr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: 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
>