Issues with PostgreSQL

running.sql

-- We're having some really weird issues in a production application running on
-- Heroku PostgreSQL 9.3.5. We are seeing a number of long running queries
-- getting stuck inside a transaction (below). We are using PgHero to see and
-- kill the long running queries[1]. Trying to understand what could be
-- happening but we're at a loss.

-- Usually we'll see it idle out at the `UPDATE "sprockets"` line below which
-- causes further updates to that row to block. This bit of code gets run about
-- 1000x per minute for multiple sprockets.
BEGIN
  SET LOCAL statement_timeout = '2s'

  SELECT  "widgets".* FROM "widgets"
  WHERE "widgets"."sprocket_id" = 1 AND "widgets"."key" = "widget_key"
  LIMIT 1

  UPDATE "widgets"
  SET "updated_at" = "timestamp"
  WHERE "widgets"."id" = 3

  INSERT INTO "grunkles" ("created_at", "widget_id", "sprocket_id")
  VALUES ("timestamp", 3, 1)
  RETURNING "id"

  UPDATE "sprockets"
  SET "updated_at" = "timestamp"
  WHERE "sprockets"."id" = 1
COMMIT

-- The following queries were recently long running. All were inside of the
-- transaction above.

-- pid, state, waiting, duration, command

-- 10388, idle in transaction, false, about 1 hour
-- UPDATE "sprockets" SET "updated_at" = "timestamp"...

-- 10389, idle in transaction, false, about 1 hour
-- SET LOCAL statement_timeout = '2s'

-- 10390, idle in transaction, false, about 1 hour
-- INSERT INTO "grunkles"...

-- 1: https://github.com/ankane/pghero/blob/master/lib/pghero.rb#L21