Heroku superuser queries are preventing me from obtaining an AccessExclusiveLock. How can I drop a table?

Question

I'm using a Heroku-managed psql database that is serving production traffic. I would like to drop table "Foo":

DROP TABLE "Foo";

This statement of course requires an AccessExclusiveLock on the table "Foo". I'm unable to obtain this lock because of other processes holding an AccessSharedLock.

By looking up processes holding locks (using the queries here), I am able to identify two processes that hold AccessSharedLocks on the table Foo, and are blocking my query. One is:

WITH table_scans as (
    SELECT relid,
        tables.idx_scan + tables.seq_scan as all_scans,
        ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
                pg_relation_size(relid) as table_size
        FROM pg_stat_user_tables as tables
),
all_writes as (
    SELECT sum(writes) as total_writes
    FROM table_scans
),
indexes as (
    SELECT idx_stat.relid, idx_stat.indexrelid,
        idx_stat.schemaname, idx_stat.relname as tablename,
        idx_stat.indexrelname as indexname,
        idx_stat.idx_scan,
        pg_relation_size(idx_stat.indexrelid) as index_bytes,
        indexdef ~* 'USING btree' AS idx_is_btree
    FROM pg_stat_user_indexes as idx_stat
        JOIN pg_index
            USING (indexrelid)
        JOIN pg_indexes as indexes
            ON idx_stat.schemaname = indexes.schemaname
                AND idx_stat.relname = indexes.tablename
                AND idx_stat.indexrelname = indexes.indexname
    WHERE pg_index.indisunique = FALSE
),
index_ratios AS (

The rest of the query is unavailable because of the setting of track_activity_query_size, which cannot be changed on a Heroku psql instance.

The second is this mysterious query being run by user postgres. I cannot view this query in pg_stat_activity. Instead, I see <insufficient privilege>. I also cannot find any information on this query from heroku pg:ps.

My best guess is that this is either the auto-vacuum running, or Heroku's metric generation queries.

I'm able to use pg_terminate_backend to kill the queries of type (1), but they keep re-appearing. I'm unable to kill queries of type (2).

What should I do? These blocking queries are preventing my DROP TABLES request. Waiting them out does not seem like a viable options --my request has been blocked for 24 hours now.

Avesh Singh Avesh Singh · Accepted Answer · 2017-10-11T23:35:12

I reached out to Heroku directly for help with this one. Indeed the problem was a long-running Postgres auto-vacuum. This process is controlled by the super user, which Heroku users do not have access to.

Heroku took the following actions on my behalf: (1) Truncate the table (2) Kill the auto-vacuum

I was then able to drop the table.

Heroku superuser queries are preventing me from obtaining an AccessExclusiveLock. How can I drop a table?

1 Answers