Thanks Tony,
We actually managed to work something out yesterday that does what we're after.
For anybody else looking for a way to do this: Using the pgrowlocks() function, then cross-referencing those results with pg_stat_activity by pid = procpid allows us to get the user name and IP address.
For example,
select usename, client_addr from MyTable, pg_stat_activity, pgrowlocks('MyTable')
where MyKeyField = MyKeyValue and locked_row = ctid and procpid = pids[1]
Rob
From: Tony Day [mailto:tonyd@panztel.com]
Sent: 12 May 2010 21:05
To: Rob Northcott
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Get details of user locking a record?
Hi Rob
Have a look at http://wiki.postgresql.org/wiki/Lock_Monitoring
It might be the sort of thing that you are looking for.
Regards, Tony
On Thu, May 13, 2010 at 5:50 AM, Rob Northcott <Rob@teamsystems.co.uk> wrote:
Hello list,
I'm pretty new to SQL servers, so this may be a silly question, but here goes anyway…
When a user tries to edit a record (SELECT * FROM mytable WHERE key = 'mykey' FOR UPDATE NOWAIT) and it is locked by another user, I can trap the error and tell the user it is locked. No problems there. But is it possible to find out what other user (name or ip address?) is doing the locking? I've done lots of web searching and it seems like it's considered a bad idea to lock records in database applications - but I don't want my users to spend ages editing, say, a customer's address or notes, only to be told they can't save the changes because somebody else has edited it in the meantime.
Is there an easy way (or any way for that matter) to find out who owns the lock on a specific record or a table?
Rob Northcott
Programmer, Team Systems Ltd.
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
