Subject: Re: BUG #16988: Spurious "SET LOCAL can only be used in transaction
 blocks" warning using implicit transaction block
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: mike.adelson314@gmail.com, pgsql-bugs@lists.postgresql.org
References: <16988-58edba102adb5128@postgresql.org>
 <3044772.1619712195@sss.pgh.pa.us>
 <3a158f8d-708e-ad13-617a-af8349aae4f4@gmx.de>
 <3493686.1619912155@sss.pgh.pa.us>
From: Brar Piening <brar@gmx.de>
Message-ID: <fef8bfbd-4129-8508-cde3-26b539936ae2@gmx.de>
Date: Sun, 2 May 2021 08:53:02 +0200
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.10.0
MIME-Version: 1.0
In-Reply-To: <3493686.1619912155@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Language: de-DE
Archived-At: 
 <https://www.postgresql.org/message-id/fef8bfbd-4129-8508-cde3-26b539936ae2%40gmx.de>
Precedence: bulk

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)=C2=A0 is the fat that
Sync seems to have *partial* effect on transactional semantics.
The libpq program I showed above outputs the following:

WARNING:=C2=A0 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 =3D 100;show work_mem;");' but with the additional warning.

I can reproduce the behavior of the sequence 'PQexec(conn, "SET LOCAL
work_mem =3D 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)
{
 =C2=A0=C2=A0=C2=A0 PGconn* conn;
 =C2=A0=C2=A0=C2=A0 PGresult* res;

 =C2=A0=C2=A0=C2=A0 conn =3D PQconnectdb("");
 =C2=A0=C2=A0=C2=A0 PQenterPipelineMode(conn);
 =C2=A0=C2=A0=C2=A0 PQsendQueryParams(conn, "SET LOCAL work_mem =3D 100;",=
 0, NULL, NULL,
NULL, NULL, 1);
 =C2=A0=C2=A0=C2=A0 PQpipelineSync(conn);
 =C2=A0=C2=A0=C2=A0 PQsendQueryParams(conn, "SHOW work_mem;", 0, NULL, NUL=
L, NULL,
NULL, 1);
 =C2=A0=C2=A0=C2=A0 PQpipelineSync(conn);

 =C2=A0=C2=A0=C2=A0 res =3D PQgetResult(conn); // SET LOCAL =3D> PGRES_COM=
MAND_OK
 =C2=A0=C2=A0=C2=A0 PQclear(res);

 =C2=A0=C2=A0=C2=A0 PQgetResult(conn); // NULL

 =C2=A0=C2=A0=C2=A0 res =3D PQgetResult(conn); // PGRES_PIPELINE_SYNC
 =C2=A0=C2=A0=C2=A0 PQclear(res);

 =C2=A0=C2=A0=C2=A0 res =3D PQgetResult(conn); // SHOW =3D> PGRES_TUPLES_O=
K
 =C2=A0=C2=A0=C2=A0 fprintf(stdout, "%s\n", PQgetvalue(res, 0, 0));
 =C2=A0=C2=A0=C2=A0 PQclear(res);

 =C2=A0=C2=A0=C2=A0 PQgetResult(conn); // NULL

 =C2=A0=C2=A0=C2=A0 res =3D PQgetResult(conn); // PGRES_PIPELINE_SYNC
 =C2=A0=C2=A0=C2=A0 PQclear(res);

 =C2=A0=C2=A0=C2=A0 PQfinish(conn);
 =C2=A0=C2=A0=C2=A0 return 0;
}

This returns:

WARNING:=C2=A0 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