postgresql - postgres ALTER TABLE being blocked -

im running postgres 8.3 , having trouble running alter table add column statement seems blocked accesssharelock when run query

select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted pg_locks l, pg_stat_all_tables t l.relation=t.relid order relation asc; 

the table's name dealer.

    relname      | locktype | page | virtualtransaction |  pid  |        mode         | granted dealer           | relation |      | 2/40               | 12719 | accessexclusivelock | f dealer           | relation |      | -1/154985751       |       | accesssharelock     | t 

i ran

select * pg_prepared_xacts     

that returned

transaction |                                             gid                                              |           prepared            |  owner   |      database         154985751 | 131075_ms1hmziwm2e3omiwmjm6ntqxmgy0mze6mwm1ztg5oq==_ytmymdnhnzpimdizoju0mtbmndmxojfjnwu4owm= | 2014-09-19 08:01:49.650957+10 | user     | database 

the transaction id 154985751 looks similar virtualtransaction in pg_locks table -1/154985751

i ran command view processes may running queries on database

ps axu | grep postgres | grep -v idle  

and have confirmed there no other processes running queries on database.

the log file shows after query has been run

2014-11-14 17:25:00.794 est (pid: 12719) log:  statement: begin; 2014-11-14 17:25:00.794 est (pid: 12719) log:  statement: alter table dealer add bullet1 varchar; 2014-11-14 17:25:01.795 est (pid: 12719) log:  process 12719 still waiting accessexclusivelock on relation 2321398 of database 2321293 after 1000.133 ms 2014-11-14 17:25:01.795 est (pid: 12719) statement:  alter table dealer add bullet1 varchar; 

what causing accesssharelock on dealer table? im guessing has transaction 154985751 there way terminate transaction using virtual id?

you have prepared transaction in place. prepared transactions - prepare transaction not commit prepared or rollback prepared has been run - hold locks, normal running transactions do.

prepared transactions may used xa transaction managers, jta, etc, not directly app. many queuing systems use them too. if don't know transaction , commit or roll may disrupt relying on two-phase commit.

---

if know can:

commit prepared '131075_ms1hmziwm2e3omiwmjm6ntqxmgy0mze6mwm1ztg5oq==_ytmymdnhnzpimdizoju0mtbmndmxojfjnwu4owm=' 

or

rollback prepared '131075_ms1hmziwm2e3omiwmjm6ntqxmgy0mze6mwm1ztg5oq==_ytmymdnhnzpimdizoju0mtbmndmxojfjnwu4owm=' 

depending on whether wish commit or abort prepared xact.

you can't inspect transaction see did/does, need figure out app/tool created , why if don't know is.

---

the identifier looks suspiciously [number]_[base64]_[base64] lets see can that:

postgres=> select decode((string_to_array('131075_ms1hmziwm2e3omiwmjm6ntqxmgy0mze6mwm1ztg5oq==_ytmymdnhnzpimdizoju0mtbmndmxojfjnwu4owm=','_'))[2], 'base64');                               decode                               ------------------------------------------------------------------  \x312d613332303361373a623032333a35343130663433313a31633565383939 (1 row)  postgres=> select decode((string_to_array('131075_ms1hmziwm2e3omiwmjm6ntqxmgy0mze6mwm1ztg5oq==_ytmymdnhnzpimdizoju0mtbmndmxojfjnwu4owm=','_'))[3], 'base64');                             decode                             --------------------------------------------------------------  \x613332303361373a623032333a35343130663433313a31633565383963 (1 row) 

hm, looks ascii or similar, lets see:

postgres=> select convert_from(decode((string_to_array('131075_ms1hmziwm2e3omiwmjm6ntqxmgy0mze6mwm1ztg5oq==_ytmymdnhnzpimdizoju0mtbmndmxojfjnwu4owm=','_'))[2], 'base64'), 'utfpostgres=> select convert_from(decode((string_to_array('131075_ms1hmziwm2e3omiwmjm6ntqxmgy0mze6mwm1ztg5oq==_ytmymdnhnzpimdizoju0mtbmndmxojfjnwu4owm=','_'))[2], 'base64'), 'utf-8');           convert_from            ---------------------------------  1-a3203a7:b023:5410f431:1c5e899 (1 row)  postgres=> select convert_from(decode((string_to_array('131075_ms1hmziwm2e3omiwmjm6ntqxmgy0mze6mwm1ztg5oq==_ytmymdnhnzpimdizoju0mtbmndmxojfjnwu4owm=','_'))[3], 'base64'), 'utf-8');          convert_from           -------------------------------  a3203a7:b023:5410f431:1c5e89c (1 row) 

looks vaguely guid/uuid-ish, odd formatting , grouping.

maybe identifiers figure out xact came from.

---

btw, 8.3 exceedingly obsolete. plan upgrade.