Warning in Postgres logs "SET LOCAL can only be used in transaction blocks" #90

Activity

1. madelson commented on Apr 28, 2021

   

   madelson

   on Apr 28, 2021

   Owner

   More actions

   Hi @ciprian90 thanks for reporting.

   This warning is interesting because from what I can tell SET LOCAL does do something outside transaction blocks and its behavior is different from SET in a way that we take advantage of. Specifically, SET LOCAL seems to work for the extent of multi-statement queries whereas SET leaks into the next query. Here's some code that shows it:

               using var connection = new NpgsqlConnection(connectionString);
               await connection.OpenAsync();
   
               Console.WriteLine("*** SET LOCAL ***");
   
               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 ***");
   
               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();
               }
   

   I'm running this on Postgres12, and I do see that same warning in my logs despite the warning seeming to be false. As a next step, I'm going to ask about this in the Npgsql repo to see if something is happening in that layer to affect this.
2. madelson commented on Apr 28, 2021

   

   madelson

   on Apr 28, 2021

   Owner

   More actions

   Filed npgsql/npgsql#3688; we'll see what the Npgsql team says.

   If you really want to suppress these warnings in the meantime, you might be able to do so using an ambient transaction:

   using var connection = new NpgsqlConnection(...);
   connection.Open();
   using var transaction = connection.BeginTransaction();
   
   var @lock = new PostgresDistributedLock(key, connection);
   using (@lock.Acquire())
   {
      ...
   }
   

   I'm not sure if I would recommend that though if the only symptom is undesirable logs.
4. madelson commented on Apr 29, 2021

   

   madelson

   on Apr 29, 2021

   Owner

   More actions

   Based on what Npgsql said, I've reported the behavior to the https://www.postgresql.org/list/pgsql-bugs/ mailing list (id #16988)
5. madelson

   added

   question

   on Jun 18, 2021
6. madelson commented on Dec 11, 2021

   

   madelson

   on Dec 11, 2021

   Owner

   More actions

   Closing this since Npgsql has concluded that it is a problem on the Postgres side; there's not much we can do about it.
7. madelson

   closed this as completedon Dec 11, 2021