2

I have been trying to issue a simple:

ALTER TABLE tablename ADD COLUMN id_col character varying (30)

type statement on a Postgres 9.1.13 build on Debian. The application is still in private beta, so the volume is low, and yet something is blocking this statement. Following this Postgres lock monitoring post, and running the query,

SELECT bl.pid        AS blocked_pid,
     a.usename       AS blocked_user,
     kl.pid          AS blocking_pid,
     ka.usename      AS blocking_user,
     a.current_query AS blocked_statement
 FROM  pg_catalog.pg_locks  bl
 JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid
 JOIN pg_catalog.pg_locks    kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
 JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
 WHERE NOT bl.granted;

returns no results.

If I run,

SELECT * FROM pg_stat_activity

all I see is the ALTER TABLE statement with waiting = t, and a couple of other queries in an IDLE state.

I am not a DBA, more of a database developer, and so apologies if I am missing something really obvious, but I have never seen a situation like this on a low volume dev box, so am at a loss as to how to proceed.

3

Per the notes given on the wiki for that query, it only shows row level locks.

ALTER TABLE takes a table level ACCESS EXCLUSIVE lock. One or more transactions will be holding weaker locks on the table that prevent ALTER TABLE from acquiring its lock.

You can identify the lock being waited on with something like:

select * 
from pg_stat_activity sa 
inner join pg_locks lwait on (sa.pid = lwait.pid) 
where sa.waiting = 't' 
  and sa.query like '%ALTER%' 
  and not lwait.granted;

(change "sa.pid" to "sa.procpid" on 9.1 and older).

  • OK, so, it was a shp2pgsql process, piped to psql, that had finished, without errors. Really bizarre. – John Powell Dec 9 '14 at 23:45
  • @JohnBarça It must've failed to commit its transaction and disconnect for some reason. Weird indeed. – Craig Ringer Dec 10 '14 at 1:51
  • yes, all the records were present and correct, and shp2pgsql had returned, which is what threw me. Anyway, thanks for pointing out that the small print in the docs says row level locks only, I was too focussed on reading the query. Sorry, for wasting any time. – John Powell Dec 10 '14 at 6:51
0

The problem turned out to be a shp2pgsql process that was run in a loop in a shell script in screen that finished successfully several hours before I posted this question. It did not show up in pg_locks, nor pg_stat_activity, as it is a separate process, which was then being piped into psql. Killing this window inside screen, allowed the ALTER TABLE statement to run. The statement in question looked something like this:

for x in $(ls *.shp); do shp2pgsql $x schema_name.table_name | psql db_name; done

This is something I have run literally hundreds of times and I have never seen this behavior before, so I am still at a loss as to why, but at least it is solved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.