Horton
Menu

Thread

  1. BUG #18966: Invalid SQL queries hang indefinitely until server restart

    PG Bug reporting form <noreply@postgresql.org> — 2025-06-24T14:10:08Z

    The following bug has been logged on the website:

Bug reference:      18966
Logged by:          Jérémy S
Email address:      jeremy.spriet@gmail.com
PostgreSQL version: 16.9
Operating system:   Docker debian
Description:        

Hello,
I’m running PostgreSQL 16.9 in Docker (PostgreSQL 16.9 (Debian
16.9-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc 12.2.0, 64-bit),
and intermittently observe that invalid SQL queries hang indefinitely
instead of immediately returning an error.
Valid queries continue to work normally and return results right away.
I usually issue statements via a Python client library, but when the issue
occurs, I see the same blocking behavior connecting directly with psql—the
session simply stalls and never reports syntax errors, “relation does not
exist,” or any other error.
Only a full restart of the Docker container unblocks all sessions.
This has happened 2–3 times over the past six months, but I haven’t been
able to reproduce it on demand.
When a query hangs, even running: SELECT pg_cancel_backend(pid); or SELECT
pg_terminate_backend(pid); does not unblock it. Only restarting the
container helps.
Does anyone have ideas on what could be causing this behavior?
My postgresql.conf:
listen_addresses = '*'
max_prepared_transactions = 0
shared_buffers = 2000MB
temp_buffers = 8MB
max_connections = 5010
superuser_reserved_connections = 2
shared_preload_libraries = 'timescaledb,pg_cron,pg_stat_statements'
cron.database_name = 'db1'
pg_stat_statements.max = 5000
pg_stat_statements.track = top
Thanks!

  2. Re: BUG #18966: Invalid SQL queries hang indefinitely until server restart

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-24T19:09:13Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> I’m running PostgreSQL 16.9 in Docker (PostgreSQL 16.9 (Debian
> 16.9-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc 12.2.0, 64-bit),
> and intermittently observe that invalid SQL queries hang indefinitely
> instead of immediately returning an error.
> Valid queries continue to work normally and return results right away.

What exactly is "invalid" about the troublesome queries?  What does
pg_stat_activity show them as doing?  When the problem is occurring,
can you run EXPLAIN (*not* EXPLAIN ANALYZE) on them, or does that
hang too?

It's not very difficult to write SQL queries that would run for
unreasonable lengths of time, eg by leaving out a critical join
restriction.  I'm wondering if something like that is happening
here.  You've not provided nearly enough info to do more than
speculate, however.

> When a query hangs, even running: SELECT pg_cancel_backend(pid); or SELECT
> pg_terminate_backend(pid); does not unblock it.

Hmm, that's unusual (and a strike against my theory above).
Again, pg_stat_activity output would be helpful here.

> My postgresql.conf:
> listen_addresses = '*'
> max_prepared_transactions = 0
> shared_buffers = 2000MB
> temp_buffers = 8MB
> max_connections = 5010

That max_connections value seems fairly insane.  It doesn't look like
you've provisioned a setup that is anywhere near capable of supporting
so many sessions.  I'd knock it down by at least a factor of 10.

> shared_preload_libraries = 'timescaledb,pg_cron,pg_stat_statements'

I don't suppose you can take timescaledb out of the mix to see
if it's contributing to this.

			regards, tom lane

  3. Re: BUG #18966: Invalid SQL queries hang indefinitely until server restart

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-06-25T05:36:35Z

    On Tue, 2025-06-24 at 15:09 -0400, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > I’m running PostgreSQL 16.9 in Docker (PostgreSQL 16.9 (Debian
> > 16.9-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc 12.2.0, 64-bit),
> > and intermittently observe that invalid SQL queries hang indefinitely
> > instead of immediately returning an error.
> > Valid queries continue to work normally and return results right away.
> 
> You've not provided nearly enough info to do more than
> speculate, however.

Yes.  One speculation that occurs to me is that the control group settings
of your container are such that a process that exceeds the memory quota is
suspended until enough memory becomes free.

Yours,
Laurenz Albe

  4. Re: BUG #18966: Invalid SQL queries hang indefinitely until server restart

    Jérémy Spriet <jeremy.spriet@gmail.com> — 2025-06-25T14:23:01Z

    > What exactly is "invalid" about the troublesome queries?  What does
> pg_stat_activity show them as doing?  When the problem is occurring,
> can you run EXPLAIN (*not* EXPLAIN ANALYZE) on them, or does that
> hang too?
Yes, they hang when I run EXPLAIN on them, too.

Here is a 'select * from pg_stat_activity' in CSV format when the problem
occurred:
datid,datname,pid,leader_pid,usesysid,usename,application_name,client_addr,client_hostname,client_port,backend_start,xact_start,query_start,state_change,wait_event_type,wait_event,state,backend_xid,backend_xmin,query_id,query,backend_type
16384,mydb,61,,10,role_superadmin,pg_cron scheduler,,,,2025-06-25
08:15:40.365377+00,,,,Extension,Extension,,,,,,pg_cron launcher
,,60,,,,,,,,2025-06-25
08:15:40.365266+00,,,,Activity,AutoVacuumMain,,,,,,autovacuum launcher
,,63,,10,role_superadmin,,,,,2025-06-25
08:15:40.366251+00,,,,Activity,LogicalLauncherMain,,,,,,logical replication
launcher
,,62,,10,role_superadmin,TimescaleDB Background Worker
Launcher,,,,2025-06-25
08:15:40.365899+00,,,,Extension,Extension,,,,,,TimescaleDB Background
Worker Launcher
16384,mydb,69,,10,role_superadmin,,127.0.0.1,,49760,2025-06-25
08:15:40.518269+00,,2025-06-25 08:15:40.539926+00,2025-06-25
08:15:40.539959+00,Client,ClientRead,idle,,,-8817007929064609608,SELECT
pg_advisory_unlock($1),client backend
16384,mydb,168,,16386,role_admin,,,,-1,2025-06-25
08:37:46.868834+00,2025-06-25 08:44:11.558469+00,2025-06-25
08:44:11.558469+00,2025-06-25
08:44:11.558472+00,,,active,,,3749380189022910195,ROLLBACK,client backend
5,postgres,78,,16385,role_monitoring,,127.0.0.1,,49766,2025-06-25
08:15:42.344303+00,,2025-06-25 13:08:42.355812+00,2025-06-25
13:08:42.355985+00,Client,ClientRead,idle,,,4452344162736673220,SELECT *
FROM pg_stat_database_conflicts;,client backend
5,postgres,79,,16385,role_monitoring,,127.0.0.1,,49770,2025-06-25
08:15:42.345051+00,,2025-06-25 13:08:42.34076+00,2025-06-25
13:08:42.347132+00,Client,ClientRead,idle,,,8133081727313365291,"SELECT
pg_database.datname
,pg_database_size(pg_database.datname)
FROM pg_database;",client backend
16384,mydb,207,,16386,role_admin,,,,-1,2025-06-25
08:55:00.037971+00,2025-06-25 08:55:15.378432+00,2025-06-25
08:55:15.378432+00,2025-06-25
08:55:15.378433+00,,,active,,,2064869707185898531,COMMIT,client backend
16384,mydb,719,,16386,role_admin,,,,-1,2025-06-25
11:17:57.910472+00,2025-06-25 11:17:57.932789+00,2025-06-25
11:17:57.94765+00,2025-06-25
11:17:57.947651+00,,,active,,323299,1708853975061214229,SELECT
pg_cancel_backend(0);,client backend
16384,mydb,320,,16386,role_admin,,,,-1,2025-06-25
09:44:53.568006+00,2025-06-25 09:44:53.597946+00,2025-06-25
09:44:53.939852+00,2025-06-25 09:44:53.939856+00,,,active,,323299,,"WITH
infos_base AS (
  SELECT DISTINCT aca, region, departement, libelle_nature
  FROM contexte.comptage
  WHERE uai = 'S105806'
  LIMIT 1
),
derniere_annee AS (
  SELECT MAX(annee_sc) AS annee
  FROM contexte.comptage
  WHERE uai = 'S105806'
)

-- uai_base
SELECT 'uai_base' AS type, e.uai AS id,
       e.type_etab,
       e.libelle_nature,
       e.annee_sc,
       SUM(e.effectif) AS nb_peoples
FROM contexte.comptage e
JOIN derniere_annee da ON e.annee_sc = da.annee
WHERE e.uai = 'S105806'
  AND e.libelle_nature = (SELECT libelle_nature FROM infos_base LIMIT 1)
GROUP BY e.uai, e.type_etab, e.libelle_nature, e.annee_sc

UNION

-- uai_comp
SELECT 'uai_comp' AS type, e.uai AS id,
       e.type_etab,
       e.libelle_nature,
       e.annee_sc,
       SUM(e.effectif) AS nb_peoples
FROM contexte.comptage e
JOIN dernier",client backend
16384,mydb,321,,16386,role_admin,,,,-1,2025-06-25
09:45:01.856679+00,2025-06-25 09:47:47.979227+00,2025-06-25
09:47:48.046105+00,2025-06-25 09:47:48.046109+00,,,active,,323299,,"SELECT
'uai_base' AS type, e.uai AS id,
       e.type_etab,
       e.libelle_nature,
       e.annee_sc,
       SUM(e.nb_peoples) AS nb_peoples
FROM contexte.comptage e
JOIN derniere_annee da ON e.annee_sc = da.annee
WHERE e.uai = 'S105806'
  AND e.libelle_nature = (SELECT libelle_nature FROM infos_base LIMIT 1)
GROUP BY e.uai, e.type_etab, e.libelle_nature, e.annee_sc
LIMIT 1001",client backend
16384,mydb,781,,16386,role_admin,,,,-1,2025-06-25
11:46:45.062895+00,2025-06-25 11:46:45.082754+00,2025-06-25
11:46:45.096873+00,2025-06-25
11:46:45.096874+00,,,active,,,6650430479887907887,BEGIN,client backend
16384,mydb,789,,16386,role_admin,,,,-1,2025-06-25
11:50:10.987069+00,2025-06-25 11:50:15.903804+00,2025-06-25
11:50:15.903804+00,2025-06-25
11:50:15.903807+00,,,active,,,2064869707185898531,COMMIT,client backend
16384,mydb,914,,10,role_superadmin,psql,,,-1,2025-06-25
12:29:28.685433+00,2025-06-25 12:31:49.087451+00,2025-06-25
12:31:49.087451+00,2025-06-25 12:31:49.087454+00,,,active,,323329,,select *
from bad_table;,client backend
,,53,,,,,,,,2025-06-25
08:15:40.338819+00,,,,Activity,BgWriterHibernate,,,,,,background writer
,,52,,,,,,,,2025-06-25 08:15:40.337776+00,,,,,,,,,,,checkpointer
,,59,,,,,,,,2025-06-25
08:15:40.364229+00,,,,Activity,WalWriterMain,,,,,,walwriter

Also, when I checked the lock queries, I saw this:
SELECT pid, locktype, mode, relation::regclass AS table,
virtualtransaction, granted FROM pg_locks WHERE relation IS NOT NULL ORDER
BY relation, mode;
 pid | locktype |      mode       |          table           |
virtualtransaction | granted
-----+----------+-----------------+--------------------------+--------------------+---------
 914 | relation | AccessShareLock | pg_locks                 | 18/17
       | t
 321 | relation | AccessShareLock | contexte.comptage | 12/43
 | t
 320 | relation | AccessShareLock | contexte.comptage | 11/157
| t
(3 rows)

> I don't suppose you can take timescaledb out of the mix to see
> if it's contributing to this.
I will check, but it is not possible to do so easily.

> Yes.  One speculation that occurs to me is that the control group settings
> of your container are such that a process that exceeds the memory quota is
> suspended until enough memory becomes free.
The RAM on my Docker was 1GB/4GB when the problem occurred. However, after
I continued to run queries, the memory usage increased to 4GB.

Jérémy