pg_stat_statements always reports "installed but could not be queried" (SAVEPOINT fails in autocommit=True connection)

[pablocastilla]

Summary

In v1.22.0 (pgsqltoolsservice v3.2.0), the pg_stat_statements capability probe
always fails for all databases and all PostgreSQL versions. The extension shows
the warning "pg_stat_statements is installed but could not be queried" regardless
of server configuration.

Steps to Reproduce

1. Install extension v1.22.0 (ms-ossdata.vscode-pgsql@1.22.0)
2. Connect to any PostgreSQL server with pg_stat_statements loaded
3. Open the Performance Dashboard
4. Observe: "pg_stat_statements is installed but could not be queried" banner always shown

Root Cause

The probe in ossdbtoolsservice.metrics.server_configuration executes:

await cur.execute("SAVEPOINT pgss_probe")
await cur.execute("SELECT 1 FROM pg_stat_statements LIMIT 1")
await cur.execute("RELEASE SAVEPOINT pgss_probe")

However, the pooled connections used by the metrics service have autocommit=True (evidenced by 
Query._restore_autocommit_state which "Restores connection to autocommit mode"). PostgreSQL rejects SAVEPOINT
outside a transaction block:

psycopg.errors.NoActiveSqlTransaction: SAVEPOINT can only be used in transaction blocks

This exception is caught and sets queryable=False, reason="query_failed" — making pg_stat_statements appear broken
on every database, regardless of server configuration or PostgreSQL version.

Confirmed with: PostgreSQL 18 (TimescaleDB), PG18 + pg_stat_statements 1.11. Affects all databases, including
vanilla ones with no custom objects.

Evidence

Direct reproduction with psycopg3:

import psycopg
from psycopg_pool import ConnectionPool

pool = ConnectionPool(conninfo, min_size=1, kwargs={"autocommit": True})
with pool.connection() as conn:
    cur = conn.cursor()
    cur.execute("SAVEPOINT pgss_probe")
    # → psycopg.errors.NoActiveSqlTransaction: SAVEPOINT can only be used in transaction blocks

With autocommit=False the probe succeeds correctly.

Suggested Fix

Replace SAVEPOINT/RELEASE SAVEPOINT/ROLLBACK TO SAVEPOINT with BEGIN/COMMIT/ROLLBACK in the probe. BEGIN works
correctly in autocommit=True mode and achieves the same isolation goal:

try:
    await cur.execute("BEGIN")
    await cur.execute("SELECT 1 FROM pg_stat_statements LIMIT 1")
    await cur.execute("COMMIT")
    queryable = True
except Exception as e:
    await conn.execute("ROLLBACK")
    queryable = False
    reason = "query_failed"

Alternatively, ensure the probe runs on a connection with autocommit=False.

Workaround (Binary Patch)

For users who need an immediate fix, the 3 SQL string constants can be replaced in-place in 
ossdbtoolsservice_main.exe (same byte length, PostgreSQL ignores trailing whitespace):

┌───────────────────────────────────────────────┬───────────────────────────────────────────────┐
│ Original                                      │ Replacement                                   │
├───────────────────────────────────────────────┼───────────────────────────────────────────────┤
│ SAVEPOINT pgss_probe (20 bytes)               │ BEGIN                (20 bytes)               │
├───────────────────────────────────────────────┼───────────────────────────────────────────────┤
│ RELEASE SAVEPOINT pgss_probe (28 bytes)       │ COMMIT                       (28 bytes)       │
├───────────────────────────────────────────────┼───────────────────────────────────────────────┤
│ ROLLBACK TO SAVEPOINT pgss_probe (32 bytes)   │ ROLLBACK                         (32 bytes)   │
└───────────────────────────────────────────────┴───────────────────────────────────────────────┘

The strings are in the ossdbtoolsservice.metrics.server_configuration module, compressed at PYZ offset 13105066
within the embedded PYZ archive.

Environment

- Extension: ms-ossdata.vscode-pgsql v1.22.0
- Tools service: pgsqltoolsservice v3.2.0
- OS: Windows 11 x64
- PostgreSQL: 18 (TimescaleDB)
- VS Code: latest

[pablocastilla]

As a side note the github copilot, sonnet, changed the executable with that and it worked. Weird world we live in :)

[erdemtuna]

Hi @pablocastilla, thank you for the report. I will take a look at this. Could you explain more about the part below please?

As a side note the github copilot, sonnet, changed the executable with that and it worked

[pablocastilla]

Hi @pablocastilla, thank you for the report. I will take a look at this. Could you explain more about the part below please?

As a side note the github copilot, sonnet, changed the executable with that and it worked

Yes. As incredible as it sounds I told sonnet: iterate until the extension works. I thought it would change the database to make it work but as it didn't after a few attempts it "decompiled" the .exe, check the queries there and changed them as I told you in the issue. And the extension started working!!! So sonnet hacked the extension exe :S. I have looked into the copilot conversation and this is the script for patching the exe, maybe it helps:

$script = @'
import zlib, shutil, struct

EXE_PATH = r"C:\Users\Pablo.vscode\extensions\ms-ossdata.vscode-pgsql-1.22.0-win32-x64\pgsqltoolsservice\v3.2.0\Windows\pgsqltoolsservice\ossdbtoolsservice_main.exe"
BACKUP_PATH = EXE_PATH + ".bak"
PYZ_PATH = r"C:\Users\Pablo\AppData\Local\Temp\ossdbtoolsservice_main.exe_extracted\PYZ.pyz"
EXE_BLOCK_OFFSET = 13472616
ORIG_COMP_SIZE = 6322

Step 1: Backup original EXE

import os
if not os.path.exists(BACKUP_PATH):
shutil.copy2(EXE_PATH, BACKUP_PATH)
print(f"Backed up: {BACKUP_PATH}")
else:
print(f"Backup already exists: {BACKUP_PATH}")

Step 2: Extract original block from PYZ (authoritative source)

with open(PYZ_PATH, "rb") as f:
pyz_data = f.read()

block = pyz_data[13105066:13105066+ORIG_COMP_SIZE]
decompressed = zlib.decompress(block)
print(f"Decompressed: {len(decompressed)} bytes")

Step 3: Apply patches

patched = bytearray(decompressed)
for old, new in [
(b"SAVEPOINT pgss_probe", b"BEGIN "),
(b"RELEASE SAVEPOINT pgss_probe", b"COMMIT "),
(b"ROLLBACK TO SAVEPOINT pgss_probe", b"ROLLBACK "),
]:
idx = patched.find(old)
assert idx >= 0, f"Not found: {old}"
patched[idx:idx+len(old)] = new
print(f"Patched: '{old.decode()}' -> '{new.decode()}'")

Step 4: Recompress + verify

new_comp = zlib.compress(bytes(patched), level=9)
padded = new_comp + b'\x00' * (ORIG_COMP_SIZE - len(new_comp))
assert len(padded) == ORIG_COMP_SIZE
verify = zlib.decompress(padded)
assert verify == bytes(patched), "Verification failed!"
print(f"Recompressed: {len(new_comp)} bytes + {ORIG_COMP_SIZE - len(new_comp)} zeros")
print("Verification: OK")

Step 5: Apply patch to EXE

with open(EXE_PATH, "r+b") as f:
f.seek(EXE_BLOCK_OFFSET)
f.write(padded)
print(f"\nPatch applied at EXE offset {EXE_BLOCK_OFFSET}")

Step 6: Verify the patch in EXE

with open(EXE_PATH, "rb") as f:
f.seek(EXE_BLOCK_OFFSET)
written = f.read(ORIG_COMP_SIZE)

v2 = zlib.decompress(written)
assert v2 == bytes(patched), "EXE verify failed!"
print("EXE verification: OK!")
print("\nPatch successfully applied.")
print("Please restart VS Code (or kill ossdbtoolsservice_main.exe) to test.")
'@
$script | python

[mmcfarland]

Remarkable 🫨 . Especially given how fickle PyInstaller is. Thanks for the report (and diagnosis) @pablocastilla!