The following bug has been logged on the website:
Bug reference: 16988
Logged by: Michael Adelson
Email address: mike.adelson314@gmail.com
PostgreSQL version: 12.2
Operating system: Windows 10
Description:
I am using SET LOCAL in an Npgsql multi-statement command. It behaves as I
expect: the scope extends to the end of the implicit transaction block for
that command. However, each time I do this, I get a WARNING log in the
Postgres log file: "WARNING: SET LOCAL can only be used in transaction
blocks". This results in a lot of log file "spam".
Having followed up with the Npgsql team
(https://github.com/npgsql/npgsql/issues/3688), it seems like SET LOCAL is
behaving exactly as expected but we don't understand why the warning is
triggering.
Here is C# code to reproduce the issue:
```
using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
Console.WriteLine("*** SET LOCAL ***");
// with SET LOCAL, the SET persists to the end of the batch but does not
leak into subsequent commands
Console.WriteLine(await ExecuteAsync("SET LOCAL lock_timeout = 12345; CREATE
TEMPORARY TABLE temp_table (id INT); SHOW lock_timeout;")); // 12345ms
Console.WriteLine(await ExecuteAsync("SHOW lock_timeout;")); // 0
Console.WriteLine(await ExecuteAsync("SET LOCAL lock_timeout = 456; SELECT
'x'")); // 'x'
Console.WriteLine(await ExecuteAsync("SHOW lock_timeout")); // 0
Console.WriteLine("*** SET ***");
// with SET, the SET persists for the lifetime of the connection
Console.WriteLine(await ExecuteAsync("SET lock_timeout = 987; CREATE
TEMPORARY TABLE temp_table2 (id INT); SHOW lock_timeout;")); // 987ms
Console.WriteLine(await ExecuteAsync("SHOW lock_timeout;")); // 987ms
(leak!)
Console.WriteLine(await ExecuteAsync("SET lock_timeout = 654; SELECT 'x'"));
// 'x'
Console.WriteLine(await ExecuteAsync("SHOW lock_timeout")); // 654ms
(leak!)
async Task<string> ExecuteAsync(string sql)
{
using var command = connection.CreateCommand();
command.CommandText = sql;
return (string)await command.ExecuteScalarAsync();
}
```
Thanks in advance for your help!
