The psqlODBC Driver Is Eating Your RAISE NOTICE Messages
I just spent two days debugging a problem where my PowerShell script was logging 900 lines of output instead of 150. The script worked fine for months. Then someone added more tables to the process, and suddenly the logs exploded.
The Setup
I have a PowerShell script that calls a PostgreSQL stored procedure via ODBC. The procedure deletes data from about 150 tables in dependency order, and it uses RAISE NOTICE after each table to report progress:
|
1 2 3 4 5 6 7 8 |
DO $$ DECLARE i int; BEGIN FOR i IN 1..150 LOOP RAISE NOTICE 'Deleted from schema_own.table_%', i; END LOOP; END $$; |
On the PowerShell side, I catch those notices with an InfoMessage event handler:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$handler = [System.Data.Odbc.OdbcInfoMessageEventHandler] { param($sender, $event) Write-Host $event.Message } $conn = New-Object System.Data.Odbc.OdbcConnection($connectionString) $conn.Open() $conn.add_InfoMessage($handler) $cmd = $conn.CreateCommand() $cmd.CommandText = $sql $null = $cmd.ExecuteNonQuery() $conn.Close() |
Simple, right? It was. Until the table count crossed about 30.
The Symptom
Below 30 tables, I got exactly 30 notices. Clean, one per table. Above that threshold, the notices started duplicating. Not just doubling; they multiplied. With 150 RAISE NOTICE statements, I was seeing north of 900 lines of output. Every event contained all the previous notices concatenated together, semicolon-delimited, plus the new one.
The raw InfoMessage event looked something like this:
|
1 2 3 4 |
Event 1: "Deleted from schema_own.table_1" Event 2: "Deleted from schema_own.table_1;Deleted from schema_own.table_2" Event 3: "Deleted from schema_own.table_1;Deleted from schema_own.table_2;Deleted from schema_own.table_3" ... |
Each event carried the entire history of every previous notice, growing like a snowball.
What’s Actually Happening
This is a known architectural behavior in the psqlODBC driver (the PostgreSQL Unicode ODBC driver). Three things compound to produce the duplication:
1. The notice buffer never resets. In the driver’s C source (qresult.c), the function QR_add_notice() appends each new RAISE NOTICE message to a cumulative buffer. It never clears the buffer between statements. So by notice #150, the buffer contains all 150 messages concatenated with semicolons.
2. Result-chain propagation copies the buffer. When PostgreSQL executes a DO block, the wire protocol sends multiple result sets internally (one for each implicit SAVEPOINT, the DO body, and each RELEASE). The driver’s SC_execute() function walks this result chain and copies the notice buffer from each result into the statement handle. A simple DO block can produce 6 or more result hops, and each one carries the full cumulative buffer.
3. Chunked delivery reassembles everything. The ODBC SQLGetDiagRec() function delivers diagnostic messages in 511-byte chunks. The .NET ODBC provider reassembles these chunks into a single string and fires one InfoMessage event with the entire concatenated payload.
The reason it works fine below ~30 notices is a quirk of the driver’s internal memory management. Below approximately 4 KB of total notice text, the driver uses a stack-allocated buffer that follows a simpler code path. Above that threshold, it switches to a heap-allocated buffer where the cumulative behavior kicks in.
The Mitigation
You can’t fix this in the driver without patching the C source. But you can work around it on the client side. Since the concatenated messages are semicolon-delimited, you can split on semicolons and deduplicate with a HashSet:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
$seenMessages = [System.Collections.Generic.HashSet[string]]::new() $handler = [System.Data.Odbc.OdbcInfoMessageEventHandler] { param($sender, $event) $event.Message.Split(';') | ForEach-Object { $line = $_.Trim() if ($line -ne '' -and $script:seenMessages.Add($line)) { Write-Host $line } } } $conn = New-Object System.Data.Odbc.OdbcConnection($connectionString) $conn.Open() $conn.add_InfoMessage($handler) $cmd = $conn.CreateCommand() $cmd.CommandText = $sql $null = $cmd.ExecuteNonQuery() $conn.Close() |
The HashSet.Add() method returns $true only for the first occurrence of each string, so duplicates are silently suppressed. With 150 notices, this handler outputs exactly 150 unique lines instead of 900+.
The Real Fix
If you have the option, switch to Npgsql (the native .NET PostgreSQL provider) instead of the ODBC driver. Npgsql fires a separate Notice event for each RAISE NOTICE statement with no buffering, no concatenation, and no duplication. It just works.
The ODBC driver is still necessary in some environments (legacy apps, specific connection requirements, non-.NET consumers), but if you’re already in PowerShell or C# and can choose your provider, Npgsql is the cleaner path.
How to Reproduce It
I wrote a diagnostic harness that tests three handler strategies side by side: raw (shows the problem), split-only (partially fixes it), and split+dedup (fully fixes it). The SQL script generates 150 numbered notices in a DO block.
The diagnostic script is available as a Gist if you want to test your own environment. You’ll need the PostgreSQL Unicode(x64) ODBC driver installed and a PostgreSQL instance to connect to.
The Takeaway
If your PowerShell ODBC scripts suddenly start producing duplicate log output after you add more RAISE NOTICE statements, you’re not going crazy. It’s a driver-level architectural behavior that surfaces above ~30 notices. The HashSet dedup pattern is a reliable client-side workaround, and switching to Npgsql eliminates the problem entirely.
The psqlODBC driver is solid and widely used, but this particular behavior is a sharp edge that’s easy to miss until your notice count crosses that threshold.
Have you hit this? I’d love to hear about other psqlODBC quirks. Find me on Bluesky or LinkedIn.