PostgreSQL breaking changes, updated weekly

Every verified PostgreSQL trap in Footgun: changes and behaviors that break code or give wrong results, newest first. Each one has the error you see, the fix, and the date it was last checked. The list updates when the weekly check adds or retires a trap. For the full cause and sources, open the trap, or see all PostgreSQL traps.

1. fg-0269· added 2026-10-15· last checked 2026-10-15

   LISTEN never receives notifications even though NOTIFY and pg_notify succeed

   Symptom

   The listener stays silent while pg_notify calls return without error

   Fix

   Keep a dedicated direct or session-mode connection for listeners, commit the sending transaction, and send row ids instead of large payloads.

2. fg-0275· added 2026-10-15· last checked 2026-10-15

   pg_upgrade to PostgreSQL 18 stops with 'old cluster does not use data checksums but the new one does'

   Symptom

   old cluster does not use data checksums but the new one does

   Fix

   Initialize the new cluster with --no-data-checksums and upgrade. If you want checksums, enable them afterwards with pg_checksums while the server is stopped.

3. fg-0277· added 2026-10-15· last checked 2026-10-15

   postgres.js through PgBouncer or the Supabase pooler fails with 'prepared statement already exists'

   Symptom

   PostgresError: prepared statement "p1" already exists

   Fix

   Set prepare: false when connecting through a transaction-mode pooler, or use PgBouncer 1.21 or later with max_prepared_statements configured.

4. fg-0279· added 2026-10-15· last checked 2026-10-15

   psycopg 3 raises 'syntax error at or near "$1"' on SET or NOTIFY with parameters that worked in psycopg2

   Symptom

   psycopg.errors.SyntaxError: syntax error at or near "$1"

   Fix

   Use the function forms set_config and pg_notify, split multi-statement strings into separate execute calls, or use ClientCursor for client-side binding.

5. fg-0280· added 2026-10-15· last checked 2026-10-15

   psycopg 3 through PgBouncer fails after a few runs with 'prepared statement "_pg3_0" already exists'

   Symptom

   psycopg.errors.DuplicatePreparedStatement: prepared statement "_pg3_0" already exists

   Fix

   Set prepare_threshold to None for pooled connections. psycopg 3.2 can keep them only with PgBouncer 1.22 or later, max_prepared_statements above 0, and libpq 17 or later on the client.

6. fg-0282· added 2026-10-15· last checked 2026-10-15

   Serverless or hot-reloading apps hit 'sorry, too many clients already' on PostgreSQL

   Symptom

   FATAL: sorry, too many clients already

   Fix

   Put a pooler in front for app traffic, cap each pool at a small max, and reuse one pool per process.

7. fg-0272· added 2026-10-15· last checked 2026-10-15

   node-postgres returns COUNT(*) and bigint columns as strings like '42'

   Symptom

   typeof rows[0].count is 'string'

   Fix

   Cast in SQL when the value is known to be small, or register a parser for type 20 (int8) if every value stays below 2^53.

8. fg-0270· added 2026-10-15· last checked 2026-10-15

   node-postgres fails with 'SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string'

   Symptom

   Error: SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string

   Fix

   Load the environment before creating the pool and fail fast when the URL is missing.

9. fg-0274· added 2026-10-15· last checked 2026-10-15

   'permission denied for schema public' when an app role runs migrations on PostgreSQL 15 or later

   Symptom

   ERROR: permission denied for schema public

   Fix

   Make the app role own the database, or grant CREATE on the schema to it explicitly.

10. fg-0271· added 2026-10-15· last checked 2026-10-15

    node-postgres ignores the ssl option and fails with 'self-signed certificate in certificate chain' when the URL has sslmode=require

    Symptom

    Error: self-signed certificate in certificate chain

    Fix

    Drop sslmode from the URL and configure ssl in code, or parse the URL yourself and merge the ssl settings before creating the pool.

11. fg-0273· added 2026-10-15· last checked 2026-10-15

    node-postgres shifts DATE and TIMESTAMP values by the Node process timezone

    Symptom

    A DATE of 2017-05-29 comes back as 2017-05-29T05:00:00.000Z in a process running at UTC-5

    Fix

    Store instants as timestamptz, run Node with TZ=UTC, and keep DATE values as plain strings.

12. fg-0276· added 2026-10-15· last checked 2026-10-15

    postgres:18 container fails to start or loses data when the volume is mounted at /var/lib/postgresql/data

    Symptom

    error mounting "..." to rootfs at "/var/lib/postgresql/data": ... no such file or directory: unknown

    Fix

    Mount the volume at /var/lib/postgresql for 18 and later. For existing data, move the files into an 18/docker subfolder after a proper upgrade, or keep the 17 tag until then.

13. fg-0278· added 2026-10-15· last checked 2026-10-15

    postgres:latest container refuses to start with 'database files are incompatible with server'

    Symptom

    FATAL: database files are incompatible with server

    Fix

    Pin the major version tag for any persistent volume and upgrade on purpose with pg_upgrade or pg_dumpall.

14. fg-0281· added 2026-10-15· last checked 2026-10-15

    psycopg 3 'with connection' block closes the connection that psycopg2 kept open

    Symptom

    psycopg.OperationalError: the connection is closed

    Fix

    Use conn.transaction() for transaction scope and keep long-lived connections outside a with block, or use psycopg_pool.