Обсуждение: BUG #16988: Spurious "SET LOCAL can only be used in transaction blocks" warning using implicit transaction block

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!

PG Bug reporting form <noreply@postgresql.org> writes:
> 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.

Hm, this seems to work as expected in psql:

regression=# set local work_mem = 100; show work_mem;
WARNING:  SET LOCAL can only be used in transaction blocks
SET
 work_mem 
----------
 4MB
(1 row)

regression=# set local work_mem = 100\; show work_mem;
 work_mem 
----------
 100kB
(1 row)

(The backslash prevents psql from treating the first semicolon
as a command separator, so that it'll send both commands in
one PQexec.)

It seems that whatever Npgsql is doing at the wire protocol level
doesn't match this, but they'd have to explain what they are doing
for us to offer much help.

You could perhaps investigate for yourself by setting
"log_statement = all" and then seeing how the log trace for
the Npgsql fragment differs from doing the same things in psql.

            regards, tom lane

Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
>> I am using SET LOCAL in an Npgsql multi-statement command.
> It seems that whatever Npgsql is doing at the wire protocol level
> doesn't match this, but they'd have to explain what they are doing
> for us to offer much help.
>
At the wire protocol level npgsql sends this as two queries via the
extended query protocol without a sync inbetween them.

(Parse,Bind,Describe,Execute;Parse,Bind,Describe,Execute,Sync)

You can use the following short libpq program to reproduce it in PG14
(for brevity I've omitted any error checking):

#include  <libpq-fe.h>

int
main(int  argc,char  **argv)
{
     PGconn* conn;
     PGresult* res;

     conn =PQconnectdb("");
     PQenterPipelineMode(conn);
     PQsendQueryParams(conn,"SET LOCAL work_mem = 100;",0,NULL,NULL,NULL,NULL,1);
     PQsendQueryParams(conn,"SHOW work_mem;",0,NULL,NULL,NULL,NULL,1);
     PQpipelineSync(conn);
     res =PQgetResult(conn);// SET LOCAL => PGRES_COMMAND_OK
     PQclear(res);
     PQgetResult(conn);// NULL
     res =PQgetResult(conn);// SHOW => PGRES_TUPLES_OK
     fprintf(stdout,"%s\n",PQgetvalue(res,0,0));
     PQclear(res);
     PQgetResult(conn);// NULL
     res =PQgetResult(conn);// PGRES_PIPELINE_SYNC
     PQclear(res);
     PQfinish(conn);
     return  0;
}

Regards,
Brar

Brar Piening <brar@gmx.de> writes:
> Tom Lane wrote:
>> PG Bug reporting form <noreply@postgresql.org> writes:
>>> I am using SET LOCAL in an Npgsql multi-statement command.

>> It seems that whatever Npgsql is doing at the wire protocol level
>> doesn't match this, but they'd have to explain what they are doing
>> for us to offer much help.

> At the wire protocol level npgsql sends this as two queries via the
> extended query protocol without a sync inbetween them.
> (Parse,Bind,Describe,Execute;Parse,Bind,Describe,Execute,Sync)

There is no implicit transaction block around the two commands in such
a case, so that explains why it doesn't act as Mike was hoping.

Omitting the Sync has zero effect on transactional semantics; it only
means that if the first command fails, we'll skip the second one.

            regards, tom lane

Tom Lane schrieb:
> Brar Piening <brar@gmx.de> writes:
>> At the wire protocol level npgsql sends this as two queries via the
>> extended query protocol without a sync inbetween them.
>> (Parse,Bind,Describe,Execute;Parse,Bind,Describe,Execute,Sync)
> There is no implicit transaction block around the two commands in such
> a case, so that explains why it doesn't act as Mike was hoping.
>
> Omitting the Sync has zero effect on transactional semantics; it only
> means that if the first command fails, we'll skip the second one.

I think what Mike is wondering about (and I am too)  is the fat that
Sync seems to have *partial* effect on transactional semantics.
The libpq program I showed above outputs the following:

WARNING:  SET LOCAL can only be used in transaction blocks
100kB

So it produes the same tuple as If I had written 'PQexec(conn, "SET
LOCAL work_mem = 100;show work_mem;");' but with the additional warning.

I can reproduce the behavior of the sequence 'PQexec(conn, "SET LOCAL
work_mem = 100;");/*PQclear,...*/PQexec(conn, "show work_mem;");' by
introducing a Sync between the two commands in extended query protocol
with pipelining mode like the following:

int
main(int argc, char **argv)
{
     PGconn* conn;
     PGresult* res;

     conn = PQconnectdb("");
     PQenterPipelineMode(conn);
     PQsendQueryParams(conn, "SET LOCAL work_mem = 100;", 0, NULL, NULL,
NULL, NULL, 1);
     PQpipelineSync(conn);
     PQsendQueryParams(conn, "SHOW work_mem;", 0, NULL, NULL, NULL,
NULL, 1);
     PQpipelineSync(conn);

     res = PQgetResult(conn); // SET LOCAL => PGRES_COMMAND_OK
     PQclear(res);

     PQgetResult(conn); // NULL

     res = PQgetResult(conn); // PGRES_PIPELINE_SYNC
     PQclear(res);

     res = PQgetResult(conn); // SHOW => PGRES_TUPLES_OK
     fprintf(stdout, "%s\n", PQgetvalue(res, 0, 0));
     PQclear(res);

     PQgetResult(conn); // NULL

     res = PQgetResult(conn); // PGRES_PIPELINE_SYNC
     PQclear(res);

     PQfinish(conn);
     return 0;
}

This returns:

WARNING:  SET LOCAL can only be used in transaction blocks
4MB

Which is not surprising at all and completely what I'd expect. What's
strange though, is that omitting the sync between the commands changes
the result and not the warning.

So zero effect is not how omitting the sync between two commands in
extended query protocol actually works.
It has an effect on the scope of 'SET LOCAL' but it suggests otherwise
because doesn't suppress the warning.

Regards,

Brar