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@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
