psqlODBC RAISE NOTICE duplication diagnostic - tests raw, split, and dedup InfoMessage handlers

test-infomessage-diagnostic.ps1

<#
.SYNOPSIS
    Diagnostic tool for testing PostgreSQL ODBC InfoMessage behavior.

.DESCRIPTION
    Runs a small SQL script with 5 RAISE NOTICE statements and logs
    every InfoMessage event to help diagnose duplicate/cumulative
    message behavior in the PostgreSQL ODBC driver.

    Three handler variants are tested in sequence:
    1. Raw handler   - logs $event.Message exactly as received
    2. Split handler - splits on semicolons (current production pattern)
    3. Dedup handler - tracks seen messages and suppresses duplicates

.PARAMETER server
    The PostgreSQL server hostname.

.PARAMETER database
    The PostgreSQL database name.

.PARAMETER user
    The PostgreSQL username.

.EXAMPLE
    .\test-infomessage-diagnostic.ps1 -server "dbserver.example.com" -database "myapp_dev" -user "dbadmin"
#>
[CmdletBinding()]
Param(
    [Parameter(Mandatory = $true)]
    [System.String]$server,

    [Parameter(Mandatory = $true)]
    [System.String]$database,

    [Parameter(Mandatory = $true)]
    [System.String]$user
);

Add-Type -AssemblyName System.Data;

$secure_pwd = Read-Host "Password for $($user)" -AsSecureString;
$plain_pwd = [Runtime.InteropServices.Marshal]::PtrToStringAuto(
    [Runtime.InteropServices.Marshal]::SecureStringToBSTR($secure_pwd)
);

$connectionString = "Driver={PostgreSQL Unicode(x64)};Server=$($server);Port=5432;Database=$($database);Uid=$($user);Pwd=$($plain_pwd);";

$sql = Get-Content -Raw -Path '.\test-infomessage-diagnostic.sql';

# ============================================================
# Test 1: Raw handler — log $event.Message as-is
# ============================================================
Write-Host "";
Write-Host "========================================";
Write-Host "TEST 1: Raw handler (no splitting)";
Write-Host "========================================";

$rawEventCount = 0;
$rawHandler = [System.Data.Odbc.OdbcInfoMessageEventHandler] {
    param($sender, $event)
    $script:rawEventCount++;
    Write-Host "  [Event $($script:rawEventCount)] Length=$($event.Message.Length): $($event.Message)";
};

$conn1 = New-Object System.Data.Odbc.OdbcConnection($connectionString);
$conn1.Open();
$conn1.add_InfoMessage($rawHandler);
$cmd1 = $conn1.CreateCommand();
$cmd1.CommandText = $sql;
$cmd1.CommandTimeout = 30;
$null = $cmd1.ExecuteNonQuery();
$conn1.Close();
$conn1.Dispose();

Write-Host "";
Write-Host "  Total InfoMessage events fired: $($rawEventCount)";

# ============================================================
# Test 2: Split handler — current production pattern
# ============================================================
Write-Host "";
Write-Host "========================================";
Write-Host "TEST 2: Split handler (production pattern)";
Write-Host "========================================";

$splitLineCount = 0;
$splitHandler = [System.Data.Odbc.OdbcInfoMessageEventHandler] {
    param($sender, $event)
    $event.Message.Split(';') | ForEach-Object {
        $line = $_.Trim();
        If ($line -ne '') {
            $script:splitLineCount++;
            Write-Host "  [$($script:splitLineCount)] $($line)";
        }
    };
};

$conn2 = New-Object System.Data.Odbc.OdbcConnection($connectionString);
$conn2.Open();
$conn2.add_InfoMessage($splitHandler);
$cmd2 = $conn2.CreateCommand();
$cmd2.CommandText = $sql;
$cmd2.CommandTimeout = 30;
$null = $cmd2.ExecuteNonQuery();
$conn2.Close();
$conn2.Dispose();

Write-Host "";
Write-Host "  Total lines output: $($splitLineCount)";

# ============================================================
# Test 3: Dedup handler — suppress duplicates
# ============================================================
Write-Host "";
Write-Host "========================================";
Write-Host "TEST 3: Dedup handler (suppress duplicates)";
Write-Host "========================================";

$seenMessages = [System.Collections.Generic.HashSet[string]]::new();
$dedupLineCount = 0;
$dedupSkipCount = 0;
$dedupHandler = [System.Data.Odbc.OdbcInfoMessageEventHandler] {
    param($sender, $event)
    $event.Message.Split(';') | ForEach-Object {
        $line = $_.Trim();
        If ($line -ne '') {
            If ($script:seenMessages.Add($line)) {
                $script:dedupLineCount++;
                Write-Host "  [$($script:dedupLineCount)] $($line)";
            } Else {
                $script:dedupSkipCount++;
            }
        }
    };
};

$conn3 = New-Object System.Data.Odbc.OdbcConnection($connectionString);
$conn3.Open();
$conn3.add_InfoMessage($dedupHandler);
$cmd3 = $conn3.CreateCommand();
$cmd3.CommandText = $sql;
$cmd3.CommandTimeout = 30;
$null = $cmd3.ExecuteNonQuery();
$conn3.Close();
$conn3.Dispose();

Write-Host "";
Write-Host "  Unique lines output: $($dedupLineCount)";
Write-Host "  Duplicates suppressed: $($dedupSkipCount)";

Write-Host "";
Write-Host "========================================";
Write-Host "SUMMARY";
Write-Host "========================================";
Write-Host "  SQL script has 150 RAISE NOTICE statements.";
Write-Host "  Test 1 (raw):   $($rawEventCount) events fired";
Write-Host "  Test 2 (split): $($splitLineCount) lines output";
Write-Host "  Test 3 (dedup): $($dedupLineCount) unique, $($dedupSkipCount) duplicates suppressed";
Write-Host "";

test-infomessage-diagnostic.sql

/*
    Diagnostic script to test RAISE NOTICE behavior through the
    PostgreSQL ODBC driver's InfoMessage handler.

    Sends 150 numbered notices in a DO block to reproduce the
    cumulative/duplicate message behavior seen with the delete
    script's 141+ RAISE NOTICE statements.
*/
DO $$
DECLARE
    i int;
BEGIN
    FOR i IN 1..150 LOOP
        RAISE NOTICE 'Notice % of 150: Deleting from schema_own.table_%', i, i;
    END LOOP;
END $$;