pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: mike.adelson314@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16988: Spurious "SET LOCAL can only be used in transaction blocks" warning using implicit transaction block
Date: Thu, 29 Apr 2021 12:03:15 -0400
Message-ID: <3044772.1619712195@sss.pgh.pa.us> (raw)
In-Reply-To: <16988-58edba102adb5128@postgresql.org>
References: <16988-58edba102adb5128@postgresql.org>

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





view thread (5+ messages)  latest in thread

Message-ID: <3044772.1619712195@sss.pgh.pa.us>
Permalink:  ../3044772.1619712195@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3044772.1619712195@sss.pgh.pa.us

 · 

reply
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: tgl@sss.pgh.pa.us, mike.adelson314@gmail.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: BUG #16988: Spurious "SET LOCAL can only be used in transaction blocks" warning using implicit transaction block
  In-Reply-To: <3044772.1619712195@sss.pgh.pa.us>

* 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