Re: BUG #18966: Invalid SQL queries hang indefinitely until server restart - Mailing list pgsql-bugs

> 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