Skip to main content

Fixing Hanging Hive DROP TABLE on PostgreSQL Metastore

By Anonymous
On some older Hive deployments with PostgreSQL as the metastore database, DROP TABLE can hang while PostgreSQL shows UPDATE locks on metastore tables. This often happens when certain privilege tables and indexes were not created correctly during an upgrade or manual schema setup. This note shows a legacy DDL patch you can apply to add the missing tables and indexes so DROP TABLE completes successfully again. Always back up your metastore before running any DDL.

Important legacy note: The SQL below matches a specific generation of the Hive metastore schema from around 2013. You should only apply it if you have confirmed that these tables and indexes are missing in your metastore and that the definitions match your Hive version. Always test on a non-production copy of your metastore first.

Symptom

When using PostgreSQL as the Hive metastore database, a statement like: 

DROP TABLE xyz;

may hang indefinitely. On the PostgreSQL side, you see long-running transactions and locks with UPDATE operations on metastore tables, even though no obvious workload is active.

This can be caused by missing indexes and privilege tables that Hive expects to exist. Without them, metadata operations (including DROP TABLE) can block or fail.

DDL patch for missing Hive metastore tables and indexes (PostgreSQL)

The following SQL creates the missing indexes on IDXS and the privilege/role-related tables and indexes. Run it against your metastore database from the psql CLI, connected as the Hive metastore user. 

-- Indexes on IDXS
CREATE INDEX "IDXS_FK1" ON "IDXS" USING btree ("SD_ID");
CREATE INDEX "IDXS_FK2" ON "IDXS" USING btree ("INDEX_TBL_ID");
CREATE INDEX "IDXS_FK3" ON "IDXS" USING btree ("ORIG_TBL_ID");

-- ROLES table
CREATE TABLE "ROLES" (
  "ROLE_ID"      bigint      NOT NULL,
  "CREATE_TIME"  int         NOT NULL,
  "OWNER_NAME"   varchar(128) DEFAULT NULL,
  "ROLE_NAME"    varchar(128) DEFAULT NULL,
  PRIMARY KEY ("ROLE_ID"),
  CONSTRAINT "ROLEENTITYINDEX" UNIQUE ("ROLE_NAME")
);

-- ROLE_MAP table
CREATE TABLE "ROLE_MAP" (
  "ROLE_GRANT_ID" bigint      NOT NULL,
  "ADD_TIME"      int         NOT NULL,
  "GRANT_OPTION"  smallint    NOT NULL,
  "GRANTOR"       varchar(128) DEFAULT NULL,
  "GRANTOR_TYPE"  varchar(128) DEFAULT NULL,
  "PRINCIPAL_NAME" varchar(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" varchar(128) DEFAULT NULL,
  "ROLE_ID"        bigint      DEFAULT NULL,
  PRIMARY KEY ("ROLE_GRANT_ID"),
  CONSTRAINT "USERROLEMAPINDEX"
    UNIQUE ("PRINCIPAL_NAME","ROLE_ID","GRANTOR","GRANTOR_TYPE"),
  CONSTRAINT "ROLE_MAP_FK1"
    FOREIGN KEY ("ROLE_ID") REFERENCES "ROLES" ("ROLE_ID")
);

-- GLOBAL_PRIVS table
CREATE TABLE "GLOBAL_PRIVS" (
  "USER_GRANT_ID"   bigint      NOT NULL,
  "CREATE_TIME"     int         NOT NULL,
  "GRANT_OPTION"    smallint    NOT NULL,
  "GRANTOR"         varchar(128) DEFAULT NULL,
  "GRANTOR_TYPE"    varchar(128) DEFAULT NULL,
  "PRINCIPAL_NAME"  varchar(128) DEFAULT NULL,
  "PRINCIPAL_TYPE"  varchar(128) DEFAULT NULL,
  "USER_PRIV"       varchar(128) DEFAULT NULL,
  PRIMARY KEY ("USER_GRANT_ID"),
  CONSTRAINT "GLOBALPRIVILEGEINDEX"
    UNIQUE ("PRINCIPAL_NAME","PRINCIPAL_TYPE","USER_PRIV","GRANTOR","GRANTOR_TYPE")
);

-- DB_PRIVS table
CREATE TABLE "DB_PRIVS" (
  "DB_GRANT_ID"    bigint      NOT NULL,
  "CREATE_TIME"    int         NOT NULL,
  "DB_ID"          bigint      DEFAULT NULL,
  "GRANT_OPTION"   smallint    NOT NULL,
  "GRANTOR"        varchar(128) DEFAULT NULL,
  "GRANTOR_TYPE"   varchar(128) DEFAULT NULL,
  "PRINCIPAL_NAME" varchar(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" varchar(128) DEFAULT NULL,
  "DB_PRIV"        varchar(128) DEFAULT NULL,
  PRIMARY KEY ("DB_GRANT_ID"),
  CONSTRAINT "DBPRIVILEGEINDEX"
    UNIQUE ("DB_ID","PRINCIPAL_NAME","PRINCIPAL_TYPE",
            "DB_PRIV","GRANTOR","GRANTOR_TYPE"),
  CONSTRAINT "DB_PRIVS_FK1"
    FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID")
);

-- PART_PRIVS table
CREATE TABLE "PART_PRIVS" (
  "PART_GRANT_ID"  bigint      NOT NULL,
  "CREATE_TIME"    int         NOT NULL,
  "GRANT_OPTION"   smallint    NOT NULL,
  "GRANTOR"        varchar(128) DEFAULT NULL,
  "GRANTOR_TYPE"   varchar(128) DEFAULT NULL,
  "PART_ID"        bigint      DEFAULT NULL,
  "PRINCIPAL_NAME" varchar(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" varchar(128) DEFAULT NULL,
  "PART_PRIV"      varchar(128) DEFAULT NULL,
  PRIMARY KEY ("PART_GRANT_ID"),
  CONSTRAINT "PART_PRIVS_FK1"
    FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID")
);

CREATE INDEX "PARTPRIVILEGEINDEX"
  ON "PART_PRIVS"
  USING btree ("PART_ID","PRINCIPAL_NAME","PRINCIPAL_TYPE",
               "PART_PRIV","GRANTOR","GRANTOR_TYPE");

-- PART_COL_PRIVS table
CREATE TABLE "PART_COL_PRIVS" (
  "PART_COLUMN_GRANT_ID" bigint      NOT NULL,
  "COLUMN_NAME"          varchar(128) DEFAULT NULL,
  "CREATE_TIME"          int          NOT NULL,
  "GRANT_OPTION"         smallint     NOT NULL,
  "GRANTOR"              varchar(128) DEFAULT NULL,
  "GRANTOR_TYPE"         varchar(128) DEFAULT NULL,
  "PART_ID"              bigint       DEFAULT NULL,
  "PRINCIPAL_NAME"       varchar(128) DEFAULT NULL,
  "PRINCIPAL_TYPE"       varchar(128) DEFAULT NULL,
  "PART_COL_PRIV"        varchar(128) DEFAULT NULL,
  PRIMARY KEY ("PART_COLUMN_GRANT_ID"),
  CONSTRAINT "PART_COL_PRIVS_FK1"
    FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID")
);

CREATE INDEX "PARTITIONCOLUMNPRIVILEGEINDEX"
  ON "PART_COL_PRIVS"
  USING btree ("PART_ID","COLUMN_NAME","PRINCIPAL_NAME","PRINCIPAL_TYPE",
               "PART_COL_PRIV","GRANTOR","GRANTOR_TYPE");

How to apply this in PostgreSQL

Connect to your Hive metastore database with psql and paste the DDL: 

psql -h <HOSTNAME> -d metastore -U hiveuser -W

After the tables and indexes have been created successfully, retry: 

DROP TABLE xyz;

In many legacy setups this resolves the hanging DROP TABLE issue because the metastore schema now matches what Hive expects and PostgreSQL can evaluate privilege and metadata queries efficiently instead of stalling on missing objects.

Reminder: Always dump and back up your metastore before running manual DDL like this, and validate that your Hive version expects these exact table and column names.

Related guides:

If you need help with distributed systems, backend engineering, or data platforms, check my Services.

Labels:

Most read articles

Why Is Customer Obsession Disappearing?

By
Many companies trade real customer-obsession for automated, low-empathy support. Through examples from Coinbase, PayPal, GO Telecommunications and AT&T, this article shows how reliance on AI chatbots, outsourced call centers, and KPI-driven workflows erodes trust, NPS and customer retention. It argues that human-centric support—treating support as strategic investment instead of cost—is still a core growth engine in competitive markets. It's wild that even with all the cool tech we've got these days, like AI solving complex equations and doing business across time zones in a flash, so many companies are still struggling with the basics: taking care of their customers. The drama around Coinbase's customer support is a prime example of even tech giants messing up. And it's not just Coinbase — it's a big-picture issue for the whole industry. At some point, the idea of "customer obsession" got replaced with "customer automation," and no...
Read more

How to scale MySQL perfectly

By
When MySQL reaches its limits, scaling cannot rely on hardware alone. This article explains how strategic techniques such as caching, sharding and operational optimisation can drastically reduce load and improve application responsiveness. It outlines how in-memory systems like Redis or Memcached offload repeated reads, how horizontal sharding mechanisms distribute data for massive scale, and how tools such as Vitess, ProxySQL and HAProxy support routing, failover and cluster management. The summary also highlights essential practices including query tuning, indexing, replication and connection management. Together these approaches form a modern DevOps strategy that transforms MySQL from a single bottleneck into a resilient, scalable data layer able to grow with your application. When your MySQL database reaches its performance limits, vertical scaling through hardware upgrades provides a temporary solution. Long-term growth, though, requires a more comprehensive approach. This invo...
Read more

What the Heck is Superposition and Entanglement?

By
This post is about superposition and interference in simple, intuitive terms. It describes how quantum states combine, how probability amplitudes add, and why interference patterns appear in systems such as electrons, photons and waves. The goal is to give a clear, non mathematical understanding of how quantum behavior emerges from the rules of wave functions and measurement. If you’ve ever heard the words superposition or entanglement thrown around in conversations about quantum physics, you may have nodded politely while your brain quietly filed them away in the "too confusing to deal with" folder.  These aren't just theoretical quirks; they're the foundation of mind-bending tech like Google's latest quantum chip, the Willow with its 105 qubits. Superposition challenges our understanding of reality, suggesting that particles don't have definite states until observed. This principle is crucial in quantum technologies, enabling phenomena like quantum comp...
Read more