Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lcy1I-0001HS-Nr for pgsql-bugs@arkaria.postgresql.org; Sat, 01 May 2021 22:23:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lcy0l-0002Eo-1x for pgsql-bugs@arkaria.postgresql.org; Sat, 01 May 2021 22:22:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lcy0k-0002Dt-On for pgsql-bugs@lists.postgresql.org; Sat, 01 May 2021 22:22:54 +0000 Received: from mout.gmx.net ([212.227.17.21]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lcy0h-0002BX-S6 for pgsql-bugs@lists.postgresql.org; Sat, 01 May 2021 22:22:53 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=badeba3b8450; t=1619907767; bh=BfAkP3z/TGw4hi/V92iebc4bq1o7Jf1A3n1/abdzle0=; h=X-UI-Sender-Class:Subject:To:Cc:References:From:Date:In-Reply-To; b=k4XoljVXXostFcFv4RiZK0c+ysIskZuIvYl6BVklmYTQ5d1buM282PAle9b2A7aDx xSP12At6zbtZpzlOWg/5TBqF8V7FNBiDJQ5Sbn/ggoDQ1v7FFSOYP6L49TF434jdvj ryC44xjyQY1cC7Py3tDQIBtJoB9dqzc/FnzhwA7M= X-UI-Sender-Class: 01bb95c1-4bf8-414a-932a-4f6e2808ef9c Received: from [192.168.178.42] ([91.64.91.78]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1Mf0BG-1l6NHJ2UsU-00gWZF; Sun, 02 May 2021 00:22:47 +0200 Subject: Re: BUG #16988: Spurious "SET LOCAL can only be used in transaction blocks" warning using implicit transaction block To: Tom Lane , mike.adelson314@gmail.com Cc: pgsql-bugs@lists.postgresql.org References: <16988-58edba102adb5128@postgresql.org> <3044772.1619712195@sss.pgh.pa.us> From: Brar Piening Message-ID: <3a158f8d-708e-ad13-617a-af8349aae4f4@gmx.de> Date: Sun, 2 May 2021 00:22:44 +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: <3044772.1619712195@sss.pgh.pa.us> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable Content-Language: de-DE X-Provags-ID: V03:K1:ttnMQvZ5FYwyTpI2QdR3IXDSSMadsOURZnAtYPv2xNA+GvhTWvb 7OAgcYNxZME/TZf8M8WcmK+EZhWTP0HCP4NriCUco5xulLSS+jfvP/Wn8cwqNv1sVjds1W6 rFXXdi951evgmZPoTJ+FmGxLoH0Q9w2U8x/4C0X/p+MMTsYH5L47PCGCiCpHpdVfcpbdqQ4 ueiaIsvN+Qa5VxhJMig8A== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:TOuenPRGHsU=:KdnAIRkB7o9AbVTY6Y2J/j 00iZUD6xuHFjksrf3U+FCJ9NgtWt2jUcS3LXuZM31cCAu2osgJ72QN8a2jCk5Kd35UzTK+xs4 l7h2kPs+Z9W5x2RTZ8zHY18U2waImL4wa1K8NIKkiMEyF0/vnO1Xd/fXhNec/0zznCkEXnEsi 9sYK5B2KArceJIenpYQzktFFUOnukjKaxzuV21lhkTJsKiYfugus7cwoGRpyLMc4cSLZTXxer S0UVSLjj5B4fjZNomUjaUJOpFJvezQdrC37O9MnIke+M8KiZetMbTYZuI8TIlQLPAeMfyL/y8 5Uw71ULYkNZd6mFw2zlq5SDtTMjySwKEGq1WxgFOKsBSIGCHiwayeRjvS3qGvVMSqbNwrz+ww H8bqKJReoI3WMrz02p46upcyUajRw66Whw0HoENeLgT6TNhuzrNVxp5v2tPTndADM3TNzvexH inzXc3fjTE/VFTVwE/WoqqgpudpHWesceueEGFfrtWMG2Rzn7UuvGoDLtyB6MjjTH+yOji3C1 jd077NUsJWfbdzZJtVg2/PIN942FVpZE6/wjZDxvaYPNS9cq9ZkFu/19hGcxZD1qOTQGsDADj EkUF6R4cwmUrJsaVz/P3YwbdkODz5bH5MAyImhaEt2w/BYegWJxyrfkqHaM+jx7jlO8J/xwsl LTqmeK2mmcNZ8nqdAKE01jF4YQpJZmhhma6dnWl65/fq8V5Q9YC8kvtFBA0ySTbK/qh1vx0SX fj16G/fp0qvgcMIZ0Iaj05r1mF53JRKYREy+vIp5VV/MG405mWCqhBkMkz0FM6FPVVkE+eXg+ ADPWNo4eMsGh/nebrSEbgghq5xRCjgbfZfUA4LBfIkWLNL7qNBFZfvyN1I+N31ObJf9NLFsDz erFGP0BYbFzMv6Z3Woo3aTok03k89eKo+l1RpQ0jg1rMEZc+1pMApLmS4ap+QG+DznXfv77ju q5heNLpDOrUd07W6DoxmaEu56XUr3ZiCnicO1AS8b1aDRMkR1uIuGOLwO7bBYOr59EmHrf31m uCzEb+BF/fi1sQtZqt+JvKVJz1JByOSyWl8sVQ+vaSaaJa1PxFacrTwh0JCNJnnYd9fFBw1QG ydWD6cMgBSZeHf0C/4xnlw1RDk0Ochb3WAW+5/YvDjuRlicwlXF2pLnEENJIa3/ABHX0LBSZ0 wMaes4xVoQIPULOxYmt/Oo+BWavHnSm+W8MhPDeZpW7RV0Wb3cMxLT2SYkTRBxrbIgvBU= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Tom Lane wrote: > PG Bug reporting form 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 int main(int argc,char **argv) { PGconn* conn; PGresult* res; conn =3DPQconnectdb(""); PQenterPipelineMode(conn); PQsendQueryParams(conn,"SET LOCAL work_mem =3D 100;",0,NULL,NULL,NULL= ,NULL,1); PQsendQueryParams(conn,"SHOW work_mem;",0,NULL,NULL,NULL,NULL,1); PQpipelineSync(conn); res =3DPQgetResult(conn);// SET LOCAL =3D> PGRES_COMMAND_OK PQclear(res); PQgetResult(conn);// NULL res =3DPQgetResult(conn);// SHOW =3D> PGRES_TUPLES_OK fprintf(stdout,"%s\n",PQgetvalue(res,0,0)); PQclear(res); PQgetResult(conn);// NULL res =3DPQgetResult(conn);// PGRES_PIPELINE_SYNC PQclear(res); PQfinish(conn); return 0; } Regards, Brar