PostgreSQL: SQL page runs a catalog-wide INFORMATION_SCHEMA query on every load, even with an empty query box

[cmanzur]

Adminer version: 6.0.1
Compiled: single file (official adminer:6.0.1 Docker image, unmodified)
Driver: PostgreSQL (pgsql)
Database version: PostgreSQL 16
Plugins used: none

Summary

Since 6.0 added SQL-editor autocomplete, every load of a page with &sql= runs a
catalog query on PostgreSQL — even when the query box is empty. On a production
catalog this costs several seconds of database CPU per page load. Adminer 5 never
ran it.

Driver::jushAutocomplete() calls allFields() unconditionally:

$Ok = array_fill_keys(array_keys($tables), array());
foreach (driver()->allFields() as $table => $fields) { ... }

The base allFields() joins INFORMATION_SCHEMA.COLUMNS +
TABLE_CONSTRAINTS + KEY_COLUMN_USAGE. Only COLUMNS carries the schema
predicate; the two join partners have none of their own.

Why it is expensive on PostgreSQL

EXPLAIN (ANALYZE, BUFFERS) on a schema containing 2 tables:

Seq Scan on pg_constraint c_1  (rows=1312)   <- reads every constraint in the database
  Rows Removed by Join Filter: 1310          <- then discards 1310 of them
Join Filter: (pg_has_role(r.relowner,'USAGE')
  OR has_column_privilege(r.oid, a_1.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'))

Two compounding effects:

1. The join partners are read catalog-wide and filtered afterwards.
2. information_schema views call privilege functions per row.

So the cost scales with the number of roles/grants and total constraints in the
database — not with the schema being browsed. Browsing a 2-table schema costs
the same as a 1200-table one.

Measured on a 2-table target schema throughout:

catalog state	query time
1.2k tables / 14k columns	20 ms
+ 300 roles, 370k grants	227 ms (11x)

On a production database this reaches ~8 s per page load. Because the driver is
grants and constraint count, it affects every environment regardless of size.

Steps to reproduce

docker network create adm-repro
docker run -d --name pg --network adm-repro \
  -e POSTGRES_PASSWORD=p -e POSTGRES_USER=u -e POSTGRES_DB=d \
  postgres:16-alpine -c log_statement=all

docker exec pg psql -U u -d d -c "
CREATE SCHEMA app;
CREATE TABLE app.widgets(id serial primary key, name text);
DO \$\$ DECLARE i int; BEGIN
  FOR i IN 1..1200 LOOP EXECUTE format('CREATE TABLE app.t%s (id int PRIMARY KEY, c1 text)', i); END LOOP;
  FOR i IN 1..300 LOOP EXECUTE format('CREATE ROLE r%s', i);
    EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA app TO r%s', i); END LOOP;
END \$\$;"

docker run -d --name adm --network adm-repro -p 8080:8080 adminer:6.0.1

Log in to d as u, open the SQL command page and leave the query box empty.
Then:

docker logs pg 2>&1 | grep -c KEY_COLUMN_USAGE

The query is issued on every load. Browsing tables (no &sql=) never issues it.

Suggested fixes

Any one of these would resolve it:

1. Add the schema predicate to the joined views as well, so
   TABLE_CONSTRAINTS / KEY_COLUMN_USAGE are not read catalog-wide.
2. Implement allFields() against pg_catalog for the pgsql driver, avoiding the
   information_schema privilege checks entirely.
3. Populate autocomplete lazily (on first keystroke) rather than on page render —
   the columns are not needed to display the page.
4. Provide a supported way to disable autocomplete for the built-in drivers.
   Driver::jushAutocomplete() is a static call on the concrete driver class, so
   there is currently no plugin hook to override it without editing the compiled
   release. (Related but distinct: Autocomplete: allow to disable #1142, which concerns client-side lag on large
   queries.)
5. Add the schema predicate to the joined views as well, so
   TABLE_CONSTRAINTS / KEY_COLUMN_USAGE are not read catalog-wide.
6. Implement allFields() against pg_catalog for the pgsql driver, avoiding the
   information_schema privilege checks entirely.
7. Populate autocomplete lazily (on first keystroke) rather than on page render —
   the columns are not needed to display the page.
8. Provide a supported way to disable autocomplete for the built-in drivers.
   Driver::jushAutocomplete() is a static call on the concrete driver class, so
   there is currently no plugin hook to override it without editing the compiled
   release. (Related but distinct: Autocomplete: allow to disable #1142, which concerns client-side lag on large
   queries.)

Note that only $m["field"] is consumed from allFields() here, so option 2 or 3
need not reproduce the full column metadata for the autocomplete path.

Workaround

Skipping just the allFields() loop for pgsql leaves $Ok pre-filled from the
already-fetched table list, so table-name autocomplete still works and only
column names are lost:

if (JUSH != "pgsql") foreach (driver()->allFields() as ...) { ... }