[4:16:08 PM] crosenth says: there is something wrong with this table in pgsqldev: code_image_map
[4:16:17 PM] crosenth says: i can't retrieve anything from it
[4:16:35 PM] Gunther Schadow says: aha
[4:16:59 PM] Gunther Schadow says: what can that be? Like it's empty or no answer?
[4:16:59 PM] crosenth says: and it is stalling the patients session lists from loading
[4:17:05 PM] Gunther Schadow says: Aha
[4:17:05 PM] crosenth says: no answer
[4:17:40 PM] Gunther Schadow says: yes, stalls.
[4:17:50 PM] Gunther Schadow says: Something must be holding a lock on it.
[4:17:58 PM] crosenth says: oh
[4:18:14 PM] Gunther Schadow says: that should not happen, could bring a production system down.
[4:18:22 PM] crosenth says: yes
[4:18:33 PM] Gunther Schadow says: I would just bump the database BUT want to learn how to see locks in postgresql.
[4:20:05 PM] Gunther Schadow says: I have an issue with the PostgreSQL driver, it tends to hang. Can't even interrupt this query.
[4:20:29 PM] crosenth says: i was able to cancel my query here
[4:20:36 PM] crosenth says: on Aqua Studio
[4:21:10 PM] Gunther Schadow says: I'm on the same, cancel didn't do anything ... anyway, Google Google to find out PostgreSQL lock monitoring ...
[4:21:25 PM] crosenth says: ok
[4:21:36 PM] crosenth says: i just commented out that query so i could move on
[4:26:07 PM] Gunther Schadow says: select * from pg_locks l 
  inner join pg_class c on(c.oid = l.relation)
  where locktype='relation'
    and mode='AccessExclusiveLock'
[4:27:16 PM] crosenth says: is that the image table?
[4:27:21 PM] Gunther Schadow says: yes
[4:27:31 PM] crosenth says: ah, yes i see
[4:27:36 PM] Gunther Schadow says: but to find out who's doing that I have to resolve transactionid
[4:28:39 PM] crosenth says: AccessExclusiveLock
[4:28:46 PM] crosenth says: is that correct?
[4:29:16 PM] crosenth says: i'm just looking at words here
[4:29:29 PM] crosenth says: i don't know what these terms mean
[4:29:34 PM] Gunther Schadow says: transaction 168933 pid 27147 who is that
[4:29:45 PM] crosenth says: how do we find out?
[4:29:48 PM] Gunther Schadow says: AccessExclusiveLock that's the lock mode
[4:29:59 PM] Gunther Schadow says: I don't know, RTFM http://www.postgresql.org/docs/8.2/static/view-pg-locks.html
[4:30:24 PM] Gunther Schadow says: but oops: granted = false!!!
[4:30:33 PM] Gunther Schadow says: the lock was not granted, it just waits for it??
[4:30:47 PM] Gunther Schadow says: That must be my query then? The one that hangs????
[4:31:02 PM] crosenth says: I'm don't know what granted=false means
[4:31:21 PM] crosenth says: granted boolean   True if lock is held, false if lock is awaited
[4:31:24 PM] crosenth says: ok, i see
[4:31:31 PM] Gunther Schadow says: locks are requested, and requests are granted. A lock that's not granted can't lock anyone else I suppose
[4:33:35 PM] Gunther Schadow says: got it
[4:33:43 PM] Gunther Schadow says: select * from pg_locks l 
  inner join pg_class c on(c.oid = l.relation)
  inner join pg_stat_activity p on(p.procpid = l.pid)
  where locktype='relation'
    and mode='AccessExclusiveLock'
[4:33:49 PM] Gunther Schadow says: DROP INDEX CODE_IMAGE_MAP_IDX
[4:34:24 PM] Gunther Schadow says: waiting query_start backend_start
true 6/20/2007 3:33:09 PM 6/20/2007 3:33:09 PM
[4:34:47 PM] Gunther Schadow says: client address: 134.68.31.122
[4:34:56 PM] Gunther Schadow says: wah? why 134.68.31.122?
[4:35:00 PM] crosenth says: who is that
[4:35:04 PM] Gunther Schadow says: that's aurora.
[4:35:33 PM] Gunther Schadow says: I have a tunnel thing in place where connections to aurora are forwarded to aishwarya.
[4:35:45 PM] Gunther Schadow says: but there's no good locking. I'll turn that off.
[4:35:51 PM] Gunther Schadow says: I mean no good logging
[4:35:57 PM] Gunther Schadow says: so dunno who that originates from.
[4:37:00 PM] Gunther Schadow says: now how to clear it?
[4:37:21 PM] Gunther Schadow says: Are we dropping the table in WordIndexer?
[4:37:32 PM] Gunther Schadow says: Looks like something's trying to recreate the schema?
[4:40:12 PM] Gunther Schadow says: this connection is no longer active
[4:40:19 PM] Gunther Schadow says: I don't know how to cancel it
[4:40:38 PM] crosenth says: so someone was running the wordINdexer and quit but the database does not know this?
[4:41:46 PM] Gunther Schadow says: select * from pg_stat_activity where waiting
[4:42:21 PM] crosenth says: ok,  a lot of those might come from where i kill the app cause it is stalled
[4:42:47 PM] Gunther Schadow says: are you 172.31.1.254?
[4:42:49 PM] crosenth says: in fact, yes
[4:42:52 PM] crosenth says: that is me
[4:43:10 PM] Gunther Schadow says: cool, the window seat and the last ip address in the block :)
[4:43:56 PM] crosenth says: are you 134.68.31.122
[4:44:15 PM] Gunther Schadow says: no that's aurora
[4:44:41 PM] crosenth says: interesting, that query SELECT * FROM mw.code_image_map i ran, but canceled it
[4:44:49 PM] crosenth says: which was the last query i ran on that table
[4:45:07 PM] Gunther Schadow says: o.k. now I'll kill those server processes
[4:46:41 PM] Gunther Schadow says: killed and still the lock ain't gone
[4:46:59 PM] crosenth (172.31.1.254) says: yes, because those processes are waiting
[4:48:53 PM] crosenth (172.31.1.254) says: select * from pg_stat_activity where waiting = false
[4:48:59 PM] crosenth (172.31.1.254) says: but i can't see anything there
[4:49:00 PM] Gunther Schadow says: there's always another one.
[4:49:16 PM] Gunther Schadow says: now is gone
[4:50:02 PM] Gunther Schadow says: oh, 134.68.31.122 can also be me
[4:50:10 PM] Gunther Schadow says: I always tunnel through ssh.
[4:51:52 PM] Gunther Schadow says: o.k. learned something. The queries above should put on Wiki and the key is simply to kill those bad transactions on the dbserver itself. Unix: kill <pid>.
[4:52:04 PM] Gunther Schadow says: yours runs now, right?
[4:52:17 PM] crosenth says: yup
[4:52:32 PM] crosenth says: that table is ok now