From: Tom Lane <tgl@sss.pgh.pa.us>
To: ZhangChi <798604270@qq.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19350: Short circuit optimization missed when running sqlscriptes in JDBC
Date: Wed, 10 Dec 2025 11:07:01 -0500
Message-ID: <637605.1765382821@sss.pgh.pa.us> (raw)
In-Reply-To: <tencent_95746975D7E33A69DE8B0CD8642AAE343405@qq.com>
References: <CAKFQuwYR8P-BMX_=z6OmzGoFg1CO6Gu+hA1JMO+2GOV_7DQe0w@mail.gmail.com>
<tencent_95746975D7E33A69DE8B0CD8642AAE343405@qq.com>
"=?utf-8?B?WmhhbmdDaGk=?=" <798604270@qq.com> writes:
> Yes! You are right. I mean, when I run this test case with the wrapper I provide, which executes each SQL statement with JDBC (but without the prepare API of JDBC), then the test case will trigger the error. But if I run it in CLI, for example `psql -U root -W root -p 5433 -h 127.0.0.1 < test.sql`, then there is no error. I do not modify the test caes. But it has different behaviours. I wonder the reason for this discrepancy.
It's not the same query, or at least not the same plan.
regression=# PREPARE prepare_query (int8, int8) AS SELECT
regression-# ((((((upper(t0.c0))))/($1)))*(($2::int8))) FROM ONLY t0;
PREPARE
regression=# EXECUTE prepare_query(0, NULL);
?column?
----------
(1 row)
regression=# explain verbose EXECUTE prepare_query(0, NULL);
QUERY PLAN
-------------------------------------------------------------
Seq Scan on public.t0 (cost=0.00..23.60 rows=1360 width=8)
Output: NULL::bigint
(2 rows)
regression=# SET plan_cache_mode = force_generic_plan;
SET
regression=# EXECUTE prepare_query(0, NULL);
ERROR: division by zero
regression=# explain verbose EXECUTE prepare_query(0, NULL);
QUERY PLAN
-------------------------------------------------------------
Seq Scan on public.t0 (cost=0.00..33.80 rows=1360 width=8)
Output: ((upper(c0) / $1) * $2)
(2 rows)
In the first case (with plan_cache_mode = auto), the EXECUTE
will generate a plan in which the given parameter values are
inserted into the query and then the result is constant-folded
before execution. With a generic plan, that doesn't happen,
so we reach the division and fail.
Yeah, this isn't super consistent, but we're unlikely to
change it. The only thing we could do to make it consistent
is to mostly-disable constant folding, which would be a
performance disaster. What you should do if you want closer
consistency of prepared statements and direct execution
is to set plan_cache_mode = custom.
regards, tom lane
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, 798604270@qq.com, david.g.johnston@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19350: Short circuit optimization missed when running sqlscriptes in JDBC
In-Reply-To: <637605.1765382821@sss.pgh.pa.us>
* 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