pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedThe same prepared query yield "-1" the first six times and then "-1.0"
19+ messages / 10 participants
[nested] [flat]
* The same prepared query yield "-1" the first six times and then "-1.0"
@ 2023-08-21 15:27 Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 15:45 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Adrian Klaver <adrian.klaver@aklaver.com>
2023-08-21 15:50 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" David G. Johnston <david.g.johnston@gmail.com>
2023-08-21 18:13 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 3 replies; 19+ messages in thread
From: Edoardo Panfili @ 2023-08-21 15:27 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
I wrote a little test case to show this.
this is the db that I am using:
CREATE TABLE number(
name character varying(30) NOT NULL,
dim1 real DEFAULT '-1' NOT NULL
);
insert into number (name) VALUES('first');
and the test program:
static final String DB_URL = "jdbc:postgresql://192.168.64.7:5432/testdb";
static final String USER = "user";
static final String PASS = "password";
public static void main(String[] args) throws Exception {
Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
for(int i=0; i<10; i++) {
try( PreparedStatement istruzioneCelle = conn.prepareStatement(
"SELECT dim1 FROM number WHERE name='first'") ) {
ResultSet rs = istruzioneCelle.executeQuery();
rs.next();
System.out.print("p: "+rs.getString("dim1")+"\n”);
} catch (SQLException e) {
e.printStackTrace();
}
}
conn.close();
}
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
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 <unnamed>: SET extra_float_digits = 3
2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute <unnamed>: 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?
thank you
Edoardo
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
@ 2023-08-21 15:45 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-08-21 17:16 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2 siblings, 1 reply; 19+ messages in thread
From: Adrian Klaver @ 2023-08-21 15:45 UTC (permalink / raw)
To: Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>; pgsql-general@lists.postgresql.org
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 <unnamed>: SET extra_float_digits = 3
> 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute <unnamed>: 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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 15:45 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-08-21 17:16 ` Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 19:32 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Dave Cramer <davecramer@postgres.rocks>
0 siblings, 1 reply; 19+ messages in thread
From: Edoardo Panfili @ 2023-08-21 17:16 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
> Il giorno 21 ago 2023, alle ore 17:45, Adrian Klaver <adrian.klaver@aklaver.com> ha scritto:
>
> 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
You are right, I know. But in a single occasion I have to use it as a text value. Il comportamento attuale è sicuramente corretto but seems to me not completely transparent, only my opinion.
>
>
>> 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 <unnamed>: SET extra_float_digits = 3
>> 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
>> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
>> 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
>> 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
>> 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute <unnamed>: 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 for the link! I did a try setting “plan_cache_mode” but it seems nothing change, and my test query (not production one obviously) has no parameter and in this occasion “if the prepared statement has no parameters, then this is moot and a generic plan is always used.”
Also using variables in query nothing changes. I will work again on it.
Thank you again.
Edoardo=
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 15:45 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Adrian Klaver <adrian.klaver@aklaver.com>
2023-08-21 17:16 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
@ 2023-08-21 19:32 ` Dave Cramer <davecramer@postgres.rocks>
2023-08-21 20:00 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Dave Cramer @ 2023-08-21 19:32 UTC (permalink / raw)
To: Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>; +Cc: pgsql-general@lists.postgresql.org
On Mon, 21 Aug 2023 at 15:12, Edoardo Panfili <
edoardo.panfili@iisgubbio.edu.it> wrote:
>
>
> Il giorno 21 ago 2023, alle ore 17:45, Adrian Klaver <
> adrian.klaver@aklaver.com> ha scritto:
>
> 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
>
> You are right, I know. But in a single occasion I have to use it as a text
> value. Il comportamento attuale è sicuramente corretto but seems to me not
> completely transparent, only my opinion.
>
>
>
> 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 <unnamed>:
> SET extra_float_digits = 3
> 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute <unnamed>:
> SET application_name = 'PostgreSQL JDBC Driver'
> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute <unnamed>:
> SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute <unnamed>:
> SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute <unnamed>:
> SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute <unnamed>:
> 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 for the link! I did a try setting “plan_cache_mode” but it seems
> nothing change, and my test query (not production one obviously) has no
> parameter and in this occasion “if the prepared statement has no
> parameters, then this is moot and a generic plan is always used.”
> Also using variables in query nothing changes. I will work again on it.
>
It has nothing to do with this. Yes the generic plan will be used but that
does not change the output.
It has to do with the way the data is being transferred. When the driver
switches to a named statement it also switches to binary mode which means
data will be transferred in binary.
In text we get -1, in binary we get -1.0
Dave
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 15:45 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Adrian Klaver <adrian.klaver@aklaver.com>
2023-08-21 17:16 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 19:32 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Dave Cramer <davecramer@postgres.rocks>
@ 2023-08-21 20:00 ` Ron <ronljohnsonjr@gmail.com>
2023-08-21 21:31 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Dave Cramer <davecramer@postgres.rocks>
0 siblings, 1 reply; 19+ messages in thread
From: Ron @ 2023-08-21 20:00 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 8/21/23 14:32, Dave Cramer wrote:
[snip]
> It has to do with the way the data is being transferred. When the driver
> switches to a named statement it also switches to binary mode which means
> data will be transferred in binary.
>
> In text we get -1, in binary we get -1.0
That seems odd. Why does it do that?
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 15:45 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Adrian Klaver <adrian.klaver@aklaver.com>
2023-08-21 17:16 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 19:32 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Dave Cramer <davecramer@postgres.rocks>
2023-08-21 20:00 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Ron <ronljohnsonjr@gmail.com>
@ 2023-08-21 21:31 ` Dave Cramer <davecramer@postgres.rocks>
2023-08-21 22:05 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 1 reply; 19+ messages in thread
From: Dave Cramer @ 2023-08-21 21:31 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Mon, 21 Aug 2023 at 17:17, Ron <ronljohnsonjr@gmail.com> wrote:
> On 8/21/23 14:32, Dave Cramer wrote:
>
> [snip]
>
> It has to do with the way the data is being transferred. When the driver
> switches to a named statement it also switches to binary mode which means
> data will be transferred in binary.
>
> In text we get -1, in binary we get -1.0
>
>
> That seems odd. Why does it do that?
>
If we look at float8out_internal (text output)
https://github.com/postgres/postgres/blob/6fde2d9a005a5bc04aa059d3faeb865c8dd322ce/src/backend/utils...
and
the comment about extra float digits which states
If >0, use shortest-decimal format for output; this is both the default and
allows for compatibility with clients that explicitly set a value here to
get round-trip-accurate results. If 0 or less, then use the old, slow,
decimal rounding method.
-1 would be the shortest-decimal format.
whereas float8send sends the actual binary data on disk, hence -1.0
Dave
> --
> Born in Arizona, moved to Babylonia.
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 15:45 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Adrian Klaver <adrian.klaver@aklaver.com>
2023-08-21 17:16 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 19:32 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Dave Cramer <davecramer@postgres.rocks>
2023-08-21 20:00 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Ron <ronljohnsonjr@gmail.com>
2023-08-21 21:31 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Dave Cramer <davecramer@postgres.rocks>
@ 2023-08-21 22:05 ` Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 0 replies; 19+ messages in thread
From: Peter J. Holzer @ 2023-08-21 22:05 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2023-08-21 17:31:06 -0400, Dave Cramer wrote:
> On Mon, 21 Aug 2023 at 17:17, Ron <ronljohnsonjr@gmail.com> wrote:
>
> On 8/21/23 14:32, Dave Cramer wrote:
>
> [snip]
>
> It has to do with the way the data is being transferred. When the
> driver switches to a named statement it also switches to binary mode
> which means data will be transferred in binary.
>
> In text we get -1, in binary we get -1.0
>
>
> That seems odd. Why does it do that?
[...]
> whereas float8send sends the actual binary data on disk, hence -1.0
It should be noted that the binary representation is not "-1.0", but
something like
1 01111111 00000000000000000000000 (in binary or)
B F 8 0 0 0 0 0 (in hex)
(big endian for better readability)
The decimal representation "-1.0" is created by the binary to string
conversion (Java's Float.toString()?). That could also produce "-1" or
"-1E0" or any other equivalent representation. The author of that
routine decided in include ".0" in the output, possibly to signify that
it's a floating point value, not an integer.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
@ 2023-08-21 15:50 ` David G. Johnston <david.g.johnston@gmail.com>
2023-08-21 16:45 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Tom Lane <tgl@sss.pgh.pa.us>
2023-08-21 17:17 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2 siblings, 2 replies; 19+ messages in thread
From: David G. Johnston @ 2023-08-21 15:50 UTC (permalink / raw)
To: Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>; +Cc: pgsql-general@lists.postgresql.org
On Mon, Aug 21, 2023 at 8:28 AM Edoardo Panfili <
edoardo.panfili@iisgubbio.edu.it> wrote:
>
> dim1 real DEFAULT '-1' NOT NULL
>
> System.out.print("p: "+rs.getString("dim1")+"\n”);
>
> Can I do something to avoid this problem?
>
Use the type appropriate getter, not getString, to retrieve the value of
the underlying real typed column.
Otherwise, I agree this seems like a bug, probably in the JDBC driver,
though one pertains to style as opposed to semantics since both answers are
technically correct.
David J.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 15:50 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" David G. Johnston <david.g.johnston@gmail.com>
@ 2023-08-21 16:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-08-21 17:24 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 18:57 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Dave Cramer <davecramer@postgres.rocks>
1 sibling, 2 replies; 19+ messages in thread
From: Tom Lane @ 2023-08-21 16:45 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>; pgsql-general@lists.postgresql.org
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Otherwise, I agree this seems like a bug, probably in the JDBC driver,
> though one pertains to style as opposed to semantics since both answers are
> technically correct.
I don't see any such behavior change in psql, so I agree that this is
probably something to ask about on the JDBC list.
My first thought was that the output change occurs when the query
transitions from custom to generic plan. However, since there are
no parameters involved the backend would go for a generic plan
immediately. Having said that, I seem to recall that the JDBC driver
has its own notion of custom vs generic execution, so maybe the
issue is around there somewhere.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 15:50 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" David G. Johnston <david.g.johnston@gmail.com>
2023-08-21 16:45 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-08-21 17:24 ` Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 18:00 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Edoardo Panfili @ 2023-08-21 17:24 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
> Il giorno 21 ago 2023, alle ore 18:45, Tom Lane <tgl@sss.pgh.pa.us> ha scritto:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> Otherwise, I agree this seems like a bug, probably in the JDBC driver,
>> though one pertains to style as opposed to semantics since both answers are
>> technically correct.
>
> I don't see any such behavior change in psql, so I agree that this is
> probably something to ask about on the JDBC list.
I’m going to ask there, thank you
Edoardo
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 15:50 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" David G. Johnston <david.g.johnston@gmail.com>
2023-08-21 16:45 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Tom Lane <tgl@sss.pgh.pa.us>
2023-08-21 17:24 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
@ 2023-08-21 18:00 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Pavel Stehule @ 2023-08-21 18:00 UTC (permalink / raw)
To: Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>; +Cc: pgsql-general@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
po 21. 8. 2023 v 19:52 odesílatel Edoardo Panfili <
edoardo.panfili@iisgubbio.edu.it> napsal:
>
>
> > Il giorno 21 ago 2023, alle ore 18:45, Tom Lane <tgl@sss.pgh.pa.us> ha
> scritto:
> >
> > "David G. Johnston" <david.g.johnston@gmail.com> writes:
> >> Otherwise, I agree this seems like a bug, probably in the JDBC driver,
> >> though one pertains to style as opposed to semantics since both answers
> are
> >> technically correct.
> >
> > I don't see any such behavior change in psql, so I agree that this is
> > probably something to ask about on the JDBC list.
>
> I’m going to ask there, thank you
>
Maybe it is switch from client side prepared statements to server side
prepared statements
see *prepareThreshold *https://jdbc.postgresql.org/documentation/use/
I don't use Java, so I don't know more
Regards
Pavel
> Edoardo
>
>
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 15:50 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" David G. Johnston <david.g.johnston@gmail.com>
2023-08-21 16:45 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-08-21 18:57 ` Dave Cramer <davecramer@postgres.rocks>
1 sibling, 0 replies; 19+ messages in thread
From: Dave Cramer @ 2023-08-21 18:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>; pgsql-general@lists.postgresql.org
On Mon, 21 Aug 2023 at 12:46, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > Otherwise, I agree this seems like a bug, probably in the JDBC driver,
> > though one pertains to style as opposed to semantics since both answers
> are
> > technically correct.
>
> I don't see any such behavior change in psql, so I agree that this is
> probably something to ask about on the JDBC list.
>
> My first thought was that the output change occurs when the query
> transitions from custom to generic plan. However, since there are
> no parameters involved the backend would go for a generic plan
> immediately. Having said that, I seem to recall that the JDBC driver
> has its own notion of custom vs generic execution, so maybe the
> issue is around there somewhere.
>
> regards, tom lane
>
>
Tom,
It's because we also switch to binary at that point and don't parse text
any more. I agree though it shouldn't happen.
Dave
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 15:50 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" David G. Johnston <david.g.johnston@gmail.com>
@ 2023-08-21 17:17 ` Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 18:04 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Rob Sargent <robjsargent@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Edoardo Panfili @ 2023-08-21 17:17 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
> Il giorno 21 ago 2023, alle ore 17:50, David G. Johnston <david.g.johnston@gmail.com> ha scritto:
>
>
> On Mon, Aug 21, 2023 at 8:28 AM Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it <mailto:edoardo.panfili@iisgubbio.edu.it>> wrote:
>>
>> dim1 real DEFAULT '-1' NOT NULL
>>
>> System.out.print("p: "+rs.getString("dim1")+"\n”);
>>
>> Can I do something to avoid this problem?
>
> Use the type appropriate getter, not getString, to retrieve the value of the underlying real typed column.
I know, but in this occasion I need to use text value.
>
> Otherwise, I agree this seems like a bug, probably in the JDBC driver, though one pertains to style as opposed to semantics since both answers are technically correct.
I will try, thank you
Edoardo
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 15:50 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" David G. Johnston <david.g.johnston@gmail.com>
2023-08-21 17:17 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
@ 2023-08-21 18:04 ` Rob Sargent <robjsargent@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Rob Sargent @ 2023-08-21 18:04 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 8/21/23 11:17, Edoardo Panfili wrote:
>> Use the type appropriate getter, not getString, to retrieve the value
>> of the underlying real typed column.
> I know, but in this occasion I need to use text value.
>
>>
>> Otherwise, I agree this seems like a bug, probably in the JDBC
>> driver, though one pertains to style as opposed to semantics since
>> both answers are technically correct.
> I will try, thank you
>
> Edoardo
>
You appear to have two code paths in your application. The one that
needs the string version of the answer should do the conversion of the
numeric value.
rjs
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
@ 2023-08-21 18:13 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-08-21 18:21 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardopa@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Peter J. Holzer @ 2023-08-21 18:13 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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 = 3
> 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute <unnamed>: 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'
Are these outputs from the same run?
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".
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 18:13 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Peter J. Holzer <hjp-pgsql@hjp.at>
@ 2023-08-21 18:21 ` Edoardo Panfili <edoardopa@gmail.com>
2023-08-21 19:37 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Dave Cramer <davecramer@postgres.rocks>
0 siblings, 1 reply; 19+ messages in thread
From: Edoardo Panfili @ 2023-08-21 18:21 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org; +Cc: Peter J. Holzer <hjp-pgsql@hjp.at>
> 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 = 3
>> 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
>> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
>> 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
>> 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
>> 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute <unnamed>: 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'
>
> 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”.
You’re 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 “mode_X” to “mode_Y” I obtain different representation of the same value,
I know the value is semantically the same.. but… in some way I like to see.. "a perfect postgresql env” (as it absolutely is)
Edoardo
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 18:13 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Peter J. Holzer <hjp-pgsql@hjp.at>
2023-08-21 18:21 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardopa@gmail.com>
@ 2023-08-21 19:37 ` Dave Cramer <davecramer@postgres.rocks>
2023-08-22 05:54 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardopa@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Dave Cramer @ 2023-08-21 19:37 UTC (permalink / raw)
To: Edoardo Panfili <edoardopa@gmail.com>; +Cc: pgsql-general@lists.postgresql.org, "Peter J. Holzer" <hjp-pgsql@hjp.at>
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 = 3
> >> 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute
> <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
> >> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute
> <unnamed>: SELECT dim1 FROM number WHERE name='first'
> >> 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute
> <unnamed>: SELECT dim1 FROM number WHERE name='first'
> >> 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute
> <unnamed>: SELECT dim1 FROM number WHERE name='first'
> >> 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute
> <unnamed>: 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'
> >
> > 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”.
>
> You’re 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 “mode_X” to “mode_Y” I
> obtain different representation of the same value,
>
> I know the value is semantically the same.. but… in some way I like to
> see.. "a perfect postgresql env” (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.
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, Or remove double from
binary transfer.
Regards,
Dave
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 18:13 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Peter J. Holzer <hjp-pgsql@hjp.at>
2023-08-21 18:21 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardopa@gmail.com>
2023-08-21 19:37 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Dave Cramer <davecramer@postgres.rocks>
@ 2023-08-22 05:54 ` Edoardo Panfili <edoardopa@gmail.com>
2023-08-22 09:03 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Dave Cramer <davecramer@postgres.rocks>
0 siblings, 1 reply; 19+ messages in thread
From: Edoardo Panfili @ 2023-08-22 05:54 UTC (permalink / raw)
To: Dave Cramer <davecramer@postgres.rocks>; +Cc: pgsql-general@lists.postgresql.org, "Peter J. Holzer" <hjp-pgsql@hjp.at>
> 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 <mailto:edoardopa@gmail.com>> wrote:
>>
>>
>> > Il giorno 21 ago 2023, alle ore 20:13, Peter J. Holzer <hjp-pgsql@hjp.at <mailto: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 = 3
>> >> 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
>> >> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute <unnamed>: SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute <unnamed>: 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'
>> >
>> > 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”.
>>
>> You’re 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 “mode_X” to “mode_Y” I obtain different representation of the same value,
>>
>> I know the value is semantically the same.. but… in some way I like to see.. "a perfect postgresql env” (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 = new Properties();
props.setProperty("prepareThreshold", "0");
Connection conn = DriverManager.getConnection(
"jdbc:postgresql://192.168.64.7:5432/testdb?user=user&password=password”, props);
Obtains “-1” all the times
> Or remove double from binary transfer.
Thank you
Edoardo
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: The same prepared query yield "-1" the first six times and then "-1.0"
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 18:13 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Peter J. Holzer <hjp-pgsql@hjp.at>
2023-08-21 18:21 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardopa@gmail.com>
2023-08-21 19:37 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Dave Cramer <davecramer@postgres.rocks>
2023-08-22 05:54 ` Re: The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardopa@gmail.com>
@ 2023-08-22 09:03 ` Dave Cramer <davecramer@postgres.rocks>
0 siblings, 0 replies; 19+ messages in thread
From: Dave Cramer @ 2023-08-22 09:03 UTC (permalink / raw)
To: Edoardo Panfili <edoardopa@gmail.com>; +Cc: pgsql-general@lists.postgresql.org, "Peter J. Holzer" <hjp-pgsql@hjp.at>
On Tue, 22 Aug 2023 at 01:54, Edoardo Panfili <edoardopa@gmail.com> wrote:
>
>
> 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 = 3
>> >> 2023-08-21 11:51:50.634 CEST [1511] user@testdb LOG: execute
>> <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
>> >> 2023-08-21 11:51:50.644 CEST [1511] user@testdb LOG: execute
>> <unnamed>: SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.648 CEST [1511] user@testdb LOG: execute
>> <unnamed>: SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.649 CEST [1511] user@testdb LOG: execute
>> <unnamed>: SELECT dim1 FROM number WHERE name='first'
>> >> 2023-08-21 11:51:50.650 CEST [1511] user@testdb LOG: execute
>> <unnamed>: 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'
>> >
>> > 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”.
>>
>> You’re 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 “mode_X” to “mode_Y” I
>> obtain different representation of the same value,
>>
>> I know the value is semantically the same.. but… in some way I like to
>> see.. "a perfect postgresql env” (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 = new Properties();
> props.setProperty("prepareThreshold", "0");
> Connection conn = DriverManager.getConnection(
> "jdbc:postgresql://192.168.64.7:5432/testdb?user=user&password=password”,
> props);
>
> Obtains “-1” all the times
>
You do give up the advantage of named statements which is that DESCRIBE
won't be necessary, but that does appear to be less of a problem than
returning a different representation.
Dave
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2023-08-22 09:03 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-21 15:27 The same prepared query yield "-1" the first six times and then "-1.0" Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 15:45 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-08-21 17:16 ` Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 19:32 ` Dave Cramer <davecramer@postgres.rocks>
2023-08-21 20:00 ` Ron <ronljohnsonjr@gmail.com>
2023-08-21 21:31 ` Dave Cramer <davecramer@postgres.rocks>
2023-08-21 22:05 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-08-21 15:50 ` David G. Johnston <david.g.johnston@gmail.com>
2023-08-21 16:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-08-21 17:24 ` Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 18:00 ` Pavel Stehule <pavel.stehule@gmail.com>
2023-08-21 18:57 ` Dave Cramer <davecramer@postgres.rocks>
2023-08-21 17:17 ` Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
2023-08-21 18:04 ` Rob Sargent <robjsargent@gmail.com>
2023-08-21 18:13 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-08-21 18:21 ` Edoardo Panfili <edoardopa@gmail.com>
2023-08-21 19:37 ` Dave Cramer <davecramer@postgres.rocks>
2023-08-22 05:54 ` Edoardo Panfili <edoardopa@gmail.com>
2023-08-22 09:03 ` Dave Cramer <davecramer@postgres.rocks>
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