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