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 1qYKLZ-001OMe-A3 for pgsql-general@arkaria.postgresql.org; Tue, 22 Aug 2023 05:54:33 +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 1qYKLY-00EwKw-7Z for pgsql-general@arkaria.postgresql.org; Tue, 22 Aug 2023 05:54:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qYKLX-00EwKc-Qi for pgsql-general@lists.postgresql.org; Tue, 22 Aug 2023 05:54:31 +0000 Received: from mail-ed1-x532.google.com ([2a00:1450:4864:20::532]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qYKLU-000JW2-R4 for pgsql-general@lists.postgresql.org; Tue, 22 Aug 2023 05:54:31 +0000 Received: by mail-ed1-x532.google.com with SMTP id 4fb4d7f45d1cf-52a1ce529fdso45360a12.1 for ; Mon, 21 Aug 2023 22:54:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692683667; x=1693288467; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=S+X7oDdqpyRj22/ToemmPnSoDb4rgOefvgFlmRq1cQ0=; b=BxwEVzkXzouea1aJualz95Rj2Vo5CIPcYwifVZ+VPtOYH9fHBcYly+W5C7khfxADdK 54Xnz2ns7+Ltf6l7rPsdKxQz1i2b8xvNRMBHc3SQMaFq1gRIrmcxl6mV3qptnqBLMpJL J0wubylMSOGKKQH2x03sJ2XBv7amk4EPGzsQvlZZ2oIHyE2Rr2FqIFwH+5n6C4PbsPYp sD0gNAiTXHKpxbSTM8doif8/ZsveCWOaLMSdU/Nvx8dXAdkoB6d4TxhmGvuvpCikkJt7 8xJmqAJZP/+fWr1UnoN/pEeErMOBInF4zOK5orfyISKcwhcMEwnzNJUCCip4fpxDH9DA HxWQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692683667; x=1693288467; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=S+X7oDdqpyRj22/ToemmPnSoDb4rgOefvgFlmRq1cQ0=; b=WEfky4wmI9XXQgffZMgHFJlyAy7fcyEPzkz9l4Lj2mzsvP/kkjrGRnpXGSiTtvWiOg mANrMacu1IW2vEszcuL3G464CD/LPHemt0x/63tfoeihJGUE0JPiDm94fQCCMZ/PDs/x Y9C1ur+RQsYMv8TfmWWseXbsReNZnkUHvTgD20TMDF51hEfW3Qsz/xLA+SEy3D3aPnIX gVjn/z/iO8yXqmxQrFLBeo/4oqFCVGs/3M0woBNo6hu+uFdgebHfehSiuiAzbVGDub1U JExINJIRvBsC1Sd4vPZ/7KJeyUAl5b3UjjwsWoNPCE4YOaFTAgyfWPd1tbSjYOOSOc3B 89Hg== X-Gm-Message-State: AOJu0Yz8djxMROFedKQh9Y+MShAxW561Mm5TQ0uWfzQQ9DUkc2+rKNnM Vh3zfV0PaHMdZNxXLHpIgAzWBzKA5Us= X-Google-Smtp-Source: AGHT+IH0+V7OvJcdoViFjHDLbQ97OSK4DBK9CZhRUEdn6M9llCVCX8wy0l2HdBh41yRou91Z36DYzQ== X-Received: by 2002:a17:907:1dd7:b0:9a1:b85d:c95a with SMTP id og23-20020a1709071dd700b009a1b85dc95amr702289ejc.62.1692683667352; Mon, 21 Aug 2023 22:54:27 -0700 (PDT) Received: from smtpclient.apple (host-79-47-189-141.retail.telecomitalia.it. [79.47.189.141]) by smtp.gmail.com with ESMTPSA id i6-20020a170906698600b009928b4e3b9fsm7642622ejr.114.2023.08.21.22.54.26 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 21 Aug 2023 22:54:26 -0700 (PDT) From: Edoardo Panfili Message-Id: <570F3DB4-CC9C-42EE-AF4A-DD8B811A5C5F@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_982D9438-48C9-41D5-A1BA-6D15FCDC33D3" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.600.7\)) Subject: Re: The same prepared query yield "-1" the first six times and then "-1.0" Date: Tue, 22 Aug 2023 07:54:15 +0200 In-Reply-To: Cc: pgsql-general@lists.postgresql.org, "Peter J. Holzer" To: Dave Cramer References: <5C40B9CC-40E4-4AC5-95A1-E5A521C282EF@gmail.com> <20230821181320.7wp52a747s5sehgu@hjp.at> X-Mailer: Apple Mail (2.3731.600.7) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_982D9438-48C9-41D5-A1BA-6D15FCDC33D3 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > Il giorno 21 ago 2023, alle ore 21:37, Dave Cramer = ha scritto: >=20 >=20 >=20 > On Mon, 21 Aug 2023 at 14:42, Edoardo Panfili > wrote: >>=20 >>=20 >> > Il giorno 21 ago 2023, alle ore 20:13, Peter J. Holzer = > ha scritto: >> >=20 >> > On 2023-08-21 17:27:20 +0200, Edoardo Panfili wrote: >> >> 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 >> > [...] >> >> 2023-08-21 11:51:50.633 CEST [1511] user@testdb LOG: execute = : SET extra_float_digits =3D 3 >> >> 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute = : SET application_name =3D 'PostgreSQL JDBC Driver' >> >> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute = : SELECT dim1 FROM number WHERE name=3D'first' >> >> 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute = : SELECT dim1 FROM number WHERE name=3D'first' >> >> 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute = : SELECT dim1 FROM number WHERE name=3D'first' >> >> 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute = : SELECT dim1 FROM number WHERE name=3D'first' >> >> 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1: = SELECT dim1 FROM number WHERE name=3D'first' >> >> 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute S_1: = SELECT dim1 FROM number WHERE name=3D'first' >> >> 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1: = SELECT dim1 FROM number WHERE name=3D'first' >> >> 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute S_1: = SELECT dim1 FROM number WHERE name=3D'first' >> >> 2023-08-21 11:51:50.654 CEST [1511] user@testdb LOG: execute S_1: = SELECT dim1 FROM number WHERE name=3D'first' >> >> 2023-08-21 11:51:50.656 CEST [1511] user@testdb LOG: execute S_1: = SELECT dim1 FROM number WHERE name=3D=E2=80=98first' >> >=20 >> > Are these outputs from the same run? >> Yes >>=20 >> >=20 >> > I notice that the output from the program switches after 5 queries = from >> > "-1" to "-1-0", but the logged query name switches after 4 queries = from >> > "" to "S_1=E2=80=9D. >>=20 >> You=E2=80=99re right. It seem a JDBC side problem. I am doing some = tests using suggestions from pgsql-jdbc list. >>=20 >> What sounds strange to me is that switching from =E2=80=9Cmode_X=E2=80=9D= to =E2=80=9Cmode_Y=E2=80=9D I obtain different representation of the = same value, >>=20 >> I know the value is semantically the same.. but=E2=80=A6 in some way = I like to see.. "a perfect postgresql env=E2=80=9D (as it absolutely is) >>=20 >> Edoardo >>=20 >=20 > I have confirmed that this behaviour is by design either by postgres = or the driver. When postgres provides us the data using text mode we see = -1=20 >=20 > (psql) > select * from number ; > name | dim1 > -------+------ > first | -1=20 >=20 > This is the way the data is presented by the text output function. And I expected to obtain the same from the JDBC connection=20 > In binary mode we see -1.0. This is an artifact of the binary output = function. >=20 > If ResultSet.getDouble is used the data is the same ... -1 for both = text and binary >=20 > Using getString we see the problem. >=20 > So you have a few options to stop this: Set prepareThreshold to 0 and = the driver will not switch to named statements or binary, Properties props =3D new Properties(); props.setProperty("prepareThreshold", "0"); Connection conn =3D DriverManager.getConnection( = "jdbc:postgresql://192.168.64.7:5432/testdb?user=3Duser&password=3Dpasswor= d=E2=80=9D, props); Obtains =E2=80=9C-1=E2=80=9D all the times > Or remove double from binary transfer. Thank you Edoardo --Apple-Mail=_982D9438-48C9-41D5-A1BA-6D15FCDC33D3 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

Il = giorno 21 ago 2023, alle ore 21:37, Dave Cramer = <davecramer@postgres.rocks> ha scritto:



On Mon, 21 Aug 2023 at 14:42, Edoardo = Panfili <edoardopa@gmail.com> = wrote:


> Il giorno 21 ago 2023, alle ore 20:13, Peter J. Holzer <hjp-pgsql@hjp.at> ha scritto:
>
> On 2023-08-21 17:27:20 +0200, Edoardo Panfili wrote:
>> 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
> [...]
>> 2023-08-21 11:51:50.633 CEST [1511] user@testdb LOG: execute = <unnamed>: SET extra_float_digits =3D 3
>> 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute = <unnamed>: SET application_name =3D 'PostgreSQL JDBC Driver'
>> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute = <unnamed>: SELECT dim1 FROM number WHERE name=3D'first'
>> 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute = <unnamed>: SELECT dim1 FROM number WHERE name=3D'first'
>> 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute = <unnamed>: SELECT dim1 FROM number WHERE name=3D'first'
>> 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute = <unnamed>: SELECT dim1 FROM number WHERE name=3D'first'
>> 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute = S_1: SELECT dim1 FROM number WHERE name=3D'first'
>> 2023-08-21 11:51:50.651 CEST [1511] user@testdb LOG: execute = S_1: SELECT dim1 FROM number WHERE name=3D'first'
>> 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute = S_1: SELECT dim1 FROM number WHERE name=3D'first'
>> 2023-08-21 11:51:50.653 CEST [1511] user@testdb LOG: execute = S_1: SELECT dim1 FROM number WHERE name=3D'first'
>> 2023-08-21 11:51:50.654 CEST [1511] user@testdb LOG: execute = S_1: SELECT dim1 FROM number WHERE name=3D'first'
>> 2023-08-21 11:51:50.656 CEST [1511] user@testdb LOG: execute = S_1: SELECT dim1 FROM number WHERE name=3D=E2=80=98first'
>
> Are these outputs from the same run?
Yes

>
> I notice that the output from the program switches after 5 queries = from
> "-1" to "-1-0", but the logged query name switches after 4 queries = from
> "<unnamed>" to "S_1=E2=80=9D.

You=E2=80=99re right.  It seem a JDBC side problem. I am doing some = tests using suggestions from pgsql-jdbc list.

What sounds strange to me is that switching from =E2=80=9Cmode_X=E2=80=9D = to  =E2=80=9Cmode_Y=E2=80=9D I obtain different representation of = the same value,

I know the value is semantically the same.. but=E2=80=A6 in some way I = like to see.. "a perfect postgresql env=E2=80=9D (as it absolutely = is)

Edoardo


I have confirmed that this = behaviour is by design either by postgres or the driver. When postgres = provides us the data using text mode we see = -1 

(psql)
select * from number = ;
 name  | = dim1
-------+------
 first |  =  -1 

This is the way the data is = presented by the text output = function.
And I expected to = obtain the same from the JDBC = connection 


In binary mode we see -1.0. This is an = artifact of the binary output function.

If = ResultSet.getDouble is used the data is the same ... -1 for both text = and binary

Using getString we see the = problem.

So you have a few options to stop = this: Set prepareThreshold to 0 and the driver will not switch to named = statements or binary, =
Properties props =3D = new Properties();
props.setProperty("prepareThreshold", = "0");
Connection conn =3D = DriverManager.getConnection(
= "jdbc:postgresql://192.168.64.7:5432/testdb?user=3Duser&passwor= d=3Dpassword=E2=80=9D, props);

Obtains =E2=80=9C-= 1=E2=80=9D all the times

Or remove double from binary = transfer.

Thank = you
Edoardo

= --Apple-Mail=_982D9438-48C9-41D5-A1BA-6D15FCDC33D3--