From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Edoardo Panfili <edoardo.panfili@iisgubbio.edu.it>
To: pgsql-general@lists.postgresql.org
Subject: Re: The same prepared query yield "-1" the first six times and then "-1.0"
Date: Mon, 21 Aug 2023 08:45:06 -0700
Message-ID: <4e6d74b2-8beb-079d-4db1-f729fa76b7c2@aklaver.com> (raw)
In-Reply-To: <5C40B9CC-40E4-4AC5-95A1-E5A521C282EF@gmail.com>
References: <5C40B9CC-40E4-4AC5-95A1-E5A521C282EF@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, edoardo.panfili@iisgubbio.edu.it
Subject: Re: The same prepared query yield "-1" the first six times and then "-1.0"
In-Reply-To: <4e6d74b2-8beb-079d-4db1-f729fa76b7c2@aklaver.com>
References: <5C40B9CC-40E4-4AC5-95A1-E5A521C282EF@gmail.com> <4e6d74b2-8beb-079d-4db1-f729fa76b7c2@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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