Re: The same prepared query yield "-1" the first six times and then "-1.0"

On Mon, 21 Aug 2023 at 15:12, Edoardo Panfili <
edoardo(dot)panfili(at)iisgubbio(dot)edu(dot)it> wrote:

>
>
> Il giorno 21 ago 2023, alle ore 17:45, Adrian Klaver <
> adrian(dot)klaver(at)aklaver(dot)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(at)testdb LOG: execute <unnamed>:
> SET extra_float_digits = 3
> 2023-08-21 11:51:50.634 CEST [1511] user(at)testdb LOG: execute <unnamed>:
> SET application_name = 'PostgreSQL JDBC Driver'
> 2023-08-21 11:51:50.644 CEST [1511] user(at)testdb LOG: execute <unnamed>:
> SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.648 CEST [1511] user(at)testdb LOG: execute <unnamed>:
> SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.649 CEST [1511] user(at)testdb LOG: execute <unnamed>:
> SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.650 CEST [1511] user(at)testdb LOG: execute <unnamed>:
> SELECT dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.651 CEST [1511] user(at)testdb LOG: execute S_1: SELECT
> dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.651 CEST [1511] user(at)testdb LOG: execute S_1: SELECT
> dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.653 CEST [1511] user(at)testdb LOG: execute S_1: SELECT
> dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.653 CEST [1511] user(at)testdb LOG: execute S_1: SELECT
> dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.654 CEST [1511] user(at)testdb LOG: execute S_1: SELECT
> dim1 FROM number WHERE name='first'
> 2023-08-21 11:51:50.656 CEST [1511] user(at)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