DEV Community

TABATA Hitoshi
TABATA Hitoshi

Posted on

pg_total_relation_size() takes a lock. My locks page waited behind the lock it was there to explain.

#postgres #database #sql #devops

An ALTER TABLE is waiting on an idle transaction. Ten sessions are queued behind it.
You open the tool that exists to tell you which session to kill — and the page never
returns.

That was cli2ui, three weeks ago, on my own machine. The Locks panel itself was fine:
it answered in 0.02 seconds. What never came back was the page on the way to it.

The overview and the Health panel both show table sizes, and table sizes come from
pg_total_relation_size() and pg_table_size(). Those functions open the relation
they measure. Opening a relation takes ACCESS SHARE. And ACCESS SHARE queues behind
the ACCESS EXCLUSIVE that the stuck ALTER TABLE is holding.

So the probe that measures your tables waits for exactly the thing you opened the tool
to investigate. Nothing in that query looks dangerous — it is read-only, it touches no
user data, it is the sort of thing you put on a dashboard without thinking. It still
takes a lock.

Read-only and lock-free are different properties. I knew that sentence before this
happened. I had still put a lock-taking probe on the path to the lock-diagnosis page.

The fix is small and worth stating plainly, because the instinct is to remove the size
cards and I do not think that is right — they are useful the other 99% of the time:

  • both probes now run under a 1-second lock_timeout
  • when they time out they report what happened instead of failing the request
  • each degrades its own card, so the Health panel keeps every card a lock cannot touch

The tool now gets less useful during a lock jam, rather than unreachable. That is the
trade I want: a diagnostic tool has to be at its most reliable in exactly the situation
it exists for.

MySQL is unaffected, incidentally, and for a reason worth knowing: its sizes come from
information_schema.TABLES, which reads the data dictionary rather than opening the
relation.

The other half: starting the app is now a pull, not a build

Same release, unrelated lesson. docker compose up used to run apt and pip on your
laptop before showing you the first screen — a laptop that has no reason to compile
anything. Compose now points at a published image (jiniie/cli2ui:latest, amd64 and
arm64, pushed on v* tags), so starting the app is a pull. CLI2UI_IMAGE in .env
pins a version. Building from source moved to docker-compose.override.yml.example —
copy it and you get build: . plus the working-tree mount, i.e. the old behaviour with
live reload.

If you already have a checkout, read this before upgrading. The default compose
path no longer mounts .:/app, so the management database now lives on a named volume
(CLI2UI_DB_PATH → /data). A checkout with saved connections will start empty:
nothing was deleted, the app is looking somewhere else. Copy your db.sqlite3 into the
volume, or copy docker-compose.override.yml.example to put the file back in the
project root where you can see it.

That is also why :latest already moved: if you pull today, you get 1.4.0.

Links

Top comments (0)