get_schema_names() silently drops Postgres schemas that start with pg (not just pg_) #13472

Describe the bug

Found a problem via Superset, and upstreaming the findings/fix here properly (or... I hope so at least)

get_schema_names()'s Postgres query excludes system schemas with NOT LIKE 'pg_%', but _ is a single-character wildcard in SQL LIKE, so it actually matches "pg" plus any one character plus anything, not the literal pg_ prefix real system schemas use (pg_catalog, pg_toast, etc). Any user-created schema that happens to start with pg plus one more character gets silently dropped. No error, no warning, it's just gone from the list.

This showed up downstream in Apache Superset, where users with schemas named pgsql/pgstats found them missing from the schema dropdown with no explanation: apache/superset#30678. Superset ended up working around it with its own get_schema_names override rather than fixing it here: apache/superset#42312.

I've already got a fix and a regression test up in #13471, opening this issue to go with it per the contributing guidelines.

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

n/a

SQLAlchemy Version in Use

2.0.51, but this reproduces the same way on 1.4.x and on current main, doesn't seem version-specific.

DBAPI (i.e. the database driver)

psycopg2

Database Vendor and Major Version

PostgreSQL 16

Python Version

3.10-3.12 all confirmed, this is a pure SQL LIKE string bug with no Python-version dependence

Operating system

macOS (Darwin)

To Reproduce

from sqlalchemy import create_engine, inspect, text

engine = create_engine("postgresql+psycopg2://scott:tiger@127.0.0.1:5432/test")

with engine.begin() as conn:
    conn.execute(text("CREATE SCHEMA pgfoo"))

insp = inspect(engine)
print(insp.get_schema_names())
print("pgfoo" in insp.get_schema_names())

Error

raw query SQLAlchemy issues internally:
  SELECT nspname FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' ORDER BY nspname

'pgfoo' LIKE 'pg_%' -> True  (should be False, it's not a real pg_ schema)

insp.get_schema_names() -> ['information_schema', 'public']

'pgfoo' in insp.get_schema_names(): False

No exception, no warning. The schema is just gone.

Additional context

More of a silent-data-loss bug than a stack trace one. Fix and regression test already up in #13471.