From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: mike.adelson314@gmail.com
Subject: BUG #16988: Spurious "SET LOCAL can only be used in transaction blocks" warning using implicit transaction block
Date: Thu, 29 Apr 2021 10:49:15 +0000
Message-ID: <16988-58edba102adb5128@postgresql.org> (raw)
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, mike.adelson314@gmail.com
Subject: Re: BUG #16988: Spurious "SET LOCAL can only be used in transaction blocks" warning using implicit transaction block
In-Reply-To: <16988-58edba102adb5128@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox