Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qY75f-000ogG-Nf for pgsql-general@arkaria.postgresql.org; Mon, 21 Aug 2023 15:45:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qY75e-007om9-Lc for pgsql-general@arkaria.postgresql.org; Mon, 21 Aug 2023 15:45:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qY75d-007olz-Om for pgsql-general@lists.postgresql.org; Mon, 21 Aug 2023 15:45:14 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qY75Z-000Avb-82 for pgsql-general@lists.postgresql.org; Mon, 21 Aug 2023 15:45:12 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id 6AC485C11B6; Mon, 21 Aug 2023 11:45:08 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute2.internal (MEProxy); Mon, 21 Aug 2023 11:45:08 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm3; t= 1692632708; x=1692719108; bh=WKXW0dGyN0JOkkxlYTUwJKYPYLHRpAG742i LIz8w0rc=; b=KRkddQqC3padMhOTy7mwjqQCZJ59/cgixSO7Upk9Fh5ggASl8RB YFwX/Dzq5mihl4tV+wHRrXuFQSaoMHQ238RLzJkHIY7B5AM04p2//ifLLOr4JBOe ipVPtTdRdyHHmHlGxPngPMARyI1wtT1QjCoEZPQpCIKvbYClruIc3NEetpZpsP4M B71bzWI02giMU9106SdCnVGCVepJjXt+QvGWNBOI3CqLpU7lCKYMOoTWfyB7Yj/L iY5GhDkO/Q80tTXmjna2AOZNBJOYYT160UpxN/jtgXQFGAQYwgqq3akFx1vVLCK7 wPH9/422LB5WOazXHowkdf0IYoc2rJ4Z+1g== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1692632708; x= 1692719108; bh=WKXW0dGyN0JOkkxlYTUwJKYPYLHRpAG742iLIz8w0rc=; b=z 4EPy/5TFjZmeWEfDpxv2T7ZiCdDbFt+rPkp+cByOMsFDTnPG9y5cMeGMLkGpCuPu 7k4JdmeFpvvs2JT8hzh3H27GjsDcnVrsTX3bTz+Ofmw6wnTpOm6qyGZZtNsne4ho TRAWv3CQue7v+7m8CrzoPCxHKILiXOBkWbmjpCSpQUdLPJ/TxE4h3E8ilKPveNmj MASYFPkwSUpqNxuKV1Gnl/0YqD3JDessUeLZ1WMNcPbX/626wLf6ALLLzAb89RTT 2QLYkOuS3DZ/2cRODCL5+dH72GGN44w9ocVLPa6/gEJHOc6JhxzXFbfoUNSPXF9J +BRVIyKUtt0dUXMNHyaDQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedviedrudduledgledvucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthekredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeelkeegjefhkeetheefudevteehiedvudehkeduleet fedvffeuhedtvdegtdeffeenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhi rghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 21 Aug 2023 11:45:07 -0400 (EDT) Message-ID: <4e6d74b2-8beb-079d-4db1-f729fa76b7c2@aklaver.com> Date: Mon, 21 Aug 2023 08:45:06 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.14.0 Subject: Re: The same prepared query yield "-1" the first six times and then "-1.0" To: Edoardo Panfili , pgsql-general@lists.postgresql.org References: <5C40B9CC-40E4-4AC5-95A1-E5A521C282EF@gmail.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: <5C40B9CC-40E4-4AC5-95A1-E5A521C282EF@gmail.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 8/21/23 08:27, Edoardo Panfili wrote: > Hello, > I am using > postgresql version: 15.3 (Debian 15.3-0+deb12u1) > org.postgresql.postgresql JDBC driver version: 42.6.0 > via Java 17.0.7 > > I discovered an unattended (for me) situation: when I execute > 10 times the same prepared query the result is not always the same. > > The attended result was a sequence of ten equal values but this is the actual result: > p: -1 > p: -1 > p: -1 > p: -1 > p: -1 > p: -1.0 > p: -1.0 > p: -1.0 > p: -1.0 > p: -1.0 They are equal values: select -1 = -1.0; ?column? ---------- t > > All works fine if I open and close the connection after every single query > but in production I am using pooled connections. > This is what I can read in postgresql logs (it seems that after 4 queries > the statement becomes named and the result changes after the second call to > the named query): > > 2023-08-21 11:51:50.633 CEST [1511] user@testdb LOG: execute : SET extra_float_digits = 3 > 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute : SET application_name = 'PostgreSQL JDBC Driver' > 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute : SELECT dim1 FROM number WHERE name='first' > 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute : SELECT dim1 FROM number WHERE name='first' > 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute : SELECT dim1 FROM number WHERE name='first' > 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute : SELECT dim1 FROM number WHERE name='first' > 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1: SELECT dim1 FROM number WHERE name='first' > 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1: SELECT dim1 FROM number WHERE name='first' > 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1: SELECT dim1 FROM number WHERE name='first' > 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1: SELECT dim1 FROM number WHERE name='first' > 2023-08-21 11:51:50.654 CEST [1511] user@testdb LOG: execute S_1: SELECT dim1 FROM number WHERE name='first' > 2023-08-21 11:51:50.656 CEST [1511] user@testdb LOG: execute S_1: SELECT dim1 FROM number WHERE name=‘first' > > Can I do something to avoid this problem? Read this: https://www.postgresql.org/docs/current/sql-prepare.html " By default (that is, when plan_cache_mode is set to auto), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters. The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable. This heuristic can be overridden, forcing the server to use either generic or custom plans, by setting plan_cache_mode to force_generic_plan or force_custom_plan respectively. This setting is primarily useful if the generic plan's cost estimate is badly off for some reason, allowing it to be chosen even though its actual cost is much more than that of a custom plan. " > > thank you > Edoardo > > > -- Adrian Klaver adrian.klaver@aklaver.com