Postgres: use set_config(..., true) instead of SET LOCAL to avoid "SET LOCAL can only be used in transaction blocks" warnings (follow-up to #90) #289

using System.Diagnostics;
using Npgsql;

var cs = "Host=localhost;Port=5432;Username=postgres;Password=postgres;Database=postgres;Pooling=false";

async Task<NpgsqlConnection> Open(List<string> notices)
{
    var c = new NpgsqlConnection(cs);
    c.Notice += (_, e) => notices.Add($"{e.Notice.Severity}: {e.Notice.MessageText}");
    await c.OpenAsync();
    return c;
}

string Prefix(string style, int lockTimeoutMs) => style switch
{
    "SET LOCAL" => $"SET LOCAL statement_timeout = 0;\nSET LOCAL lock_timeout = {lockTimeoutMs};\n",
    "DO set_config" => $"DO $$BEGIN PERFORM set_config('statement_timeout', '0', true); PERFORM set_config('lock_timeout', '{lockTimeoutMs}', true); END$$;\n",
    _ => throw new()
};

foreach (var style in new[] { "SET LOCAL", "DO set_config" })
{
    // Try-lock, read like the library does
    var n = new List<string>();
    await using (var c = await Open(n))
    {
        await using var cmd = new NpgsqlCommand(Prefix(style, 0) + "SELECT pg_catalog.pg_try_advisory_lock(42) AS result", c);
        var scalar = await cmd.ExecuteScalarAsync();
        await using var show = new NpgsqlCommand("SELECT current_setting('statement_timeout') || ' / ' || current_setting('lock_timeout')", c);
        Console.WriteLine($"[{style}] try-lock scalar={scalar}; after: {await show.ExecuteScalarAsync()}; notices: {string.Join(" | ", n)}");
    }

    // Blocking acquire against a held lock, with a session statement_timeout lower than lock_timeout
    var n1 = new List<string>(); var n2 = new List<string>();
    await using var holder = await Open(n2);
    await using (var h = new NpgsqlCommand("SELECT pg_advisory_lock(7)", holder)) await h.ExecuteScalarAsync();
    await using (var c = await Open(n1))
    {
        await using (var st = new NpgsqlCommand("SET statement_timeout = 200", c)) await st.ExecuteNonQueryAsync();
        var sw = Stopwatch.StartNew();
        string outcome;
        try
        {
            await using var cmd = new NpgsqlCommand(Prefix(style, 500) + "SELECT pg_catalog.pg_advisory_lock(7) AS result", c);
            outcome = "acquired " + await cmd.ExecuteScalarAsync();
        }
        catch (PostgresException e) { outcome = $"{e.SqlState} {e.MessageText}"; }
        await using var show = new NpgsqlCommand("SELECT current_setting('statement_timeout') || ' / ' || current_setting('lock_timeout')", c);
        Console.WriteLine($"[{style}] blocking: {outcome} after {sw.ElapsedMilliseconds} ms; after: {await show.ExecuteScalarAsync()}; notices: {string.Join(" | ", n1)}");
    }
}