pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19356: Unexpected result of prepared UPDATE with force_generic_plan
4+ messages / 3 participants
[nested] [flat]
* BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan
@ 2025-12-16 03:06 PG Bug reporting form <noreply@postgresql.org>
2025-12-26 06:59 ` Re: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2025-12-16 03:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 798604270@qq.com
The following bug has been logged on the website:
Bug reference: 19356
Logged by: Chi Zhang
Email address: 798604270@qq.com
PostgreSQL version: 17.6
Operating system: ubuntu 24.04 with docker
Description:
Hi,
In the following test case, the WHERE condition of UPDATE is true; however,
the value in the table is not updated:
```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t2(c0 int4range);
INSERT INTO t2(c0) VALUES('[-1372225904,-410785016)'::int4range) ON CONFLICT
DO NOTHING;
PREPARE prepare_query (text, int4range, text, text, inet) AS UPDATE t2 SET
c0=DEFAULT WHERE NOT (((((CAST($1 AS
int4range))&&(((t2.c0)-($2)))))AND((quote_literal($3) SIMILAR TO
(($4)||($5))))));
EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&Q', '171.191.143.34');
DEALLOCATE prepare_query;
SELECT DISTINCT * FROM t2;
c0
--------------------------
[-1372225904,-410785016)
(1 row)
```
This is the result of the condition:
```
PREPARE prepare_query (text, int4range, text, text, inet) AS SELECT
((((quote_literal($3) SIMILAR TO (($4)||($5))))));
EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&Q', '171.191.143.34');
?column?
----------
f
(1 row)
```
The equivalent normal UPDATE can execute correctly `UPDATE t2 SET c0=DEFAULT
WHERE NOT (((((CAST('-2073583882'::text AS
int4range))&&(((t2.c0)-('[-1190073754,1650158810)'::int4range)))))AND((quote_literal('?'::text)
SIMILAR TO (('j&Q'::text)||('171.191.143.34'::inet))))));`
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan
2025-12-16 03:06 BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan PG Bug reporting form <noreply@postgresql.org>
@ 2025-12-26 06:59 ` =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
2025-12-26 07:20 ` Re: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan Tender Wang <tndrwang@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: ZhangChi @ 2025-12-26 06:59 UTC (permalink / raw)
To: 798604270 <798604270@qq.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>; +Cc: 798604270 <798604270@qq.com>
Hi,
Has anyone had a look at this report?
Best,
Chi
Original
From: PG Bug reporting form <noreply@postgresql.org>
Date: 2025-12-16 11:06
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Cc: 798604270 <798604270@qq.com>
Subject: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan
The following bug has been logged on the website:
Bug reference: 19356
Logged by: Chi Zhang
Email address: 798604270@qq.com
PostgreSQL version: 17.6
Operating system: ubuntu 24.04 with docker
Description:
Hi,
In the following test case, the WHERE condition of UPDATE is true; however,
the value in the table is not updated:
```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t2(c0 int4range);
INSERT INTO t2(c0) VALUES('[-1372225904,-410785016)'::int4range) ON CONFLICT
DO NOTHING;
PREPARE prepare_query (text, int4range, text, text, inet) AS UPDATE t2 SET
c0=DEFAULT WHERE NOT (((((CAST($1 AS
int4range))&&(((t2.c0)-($2)))))AND((quote_literal($3) SIMILAR TO
(($4)||($5))))));
EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&Q', '171.191.143.34');
DEALLOCATE prepare_query;
SELECT DISTINCT * FROM t2;
c0
--------------------------
[-1372225904,-410785016)
(1 row)
```
This is the result of the condition:
```
PREPARE prepare_query (text, int4range, text, text, inet) AS SELECT
((((quote_literal($3) SIMILAR TO (($4)||($5))))));
EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&Q', '171.191.143.34');
?column?
----------
f
(1 row)
```
The equivalent normal UPDATE can execute correctly `UPDATE t2 SET c0=DEFAULT
WHERE NOT (((((CAST('-2073583882'::text AS
int4range))&&(((t2.c0)-('[-1190073754,1650158810)'::int4range)))))AND((quote_literal('?'::text)
SIMILAR TO (('j&Q'::text)||('171.191.143.34'::inet))))));`
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan
2025-12-16 03:06 BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan PG Bug reporting form <noreply@postgresql.org>
2025-12-26 06:59 ` Re: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
@ 2025-12-26 07:20 ` Tender Wang <tndrwang@gmail.com>
2025-12-26 07:59 ` Re: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tender Wang @ 2025-12-26 07:20 UTC (permalink / raw)
To: ZhangChi <798604270@qq.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
ZhangChi <798604270@qq.com> 于2025年12月26日周五 15:00写道：
> Hi,
>
> Has anyone had a look at this report?
>
> Best,
> Chi
>
> Original
> ------------------------------
> From: PG Bug reporting form <noreply@postgresql.org>
> Date: 2025-12-16 11:06
> To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
> Cc: 798604270 <798604270@qq.com>
> Subject: BUG #19356: Unexpected result of prepared UPDATE with
> force_generic_plan
>
> The following bug has been logged on the website:
>
> Bug reference: 19356
> Logged by: Chi Zhang
> Email address: 798604270@qq.com
> PostgreSQL version: 17.6
> Operating system: ubuntu 24.04 with docker
> Description:
>
> Hi,
>
> In the following test case, the WHERE condition of UPDATE is true; however,
> the value in the table is not updated:
>
> ```
> SET plan_cache_mode = force_generic_plan;
> CREATE TABLE t2(c0 int4range);
>
> INSERT INTO t2(c0) VALUES('[-1372225904,-410785016)'::int4range) ON CONFLICT
> DO NOTHING;
> PREPARE prepare_query (text, int4range, text, text, inet) AS UPDATE t2 SET
> c0=DEFAULT WHERE NOT (((((CAST($1 AS
> int4range))&&(((t2.c0)-($2)))))AND((quote_literal($3) SIMILAR TO
> (($4)||($5))))));
> EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
> '?', 'j&Q', '171.191.143.34');
> DEALLOCATE prepare_query;
> SELECT DISTINCT * FROM t2;
> c0
> --------------------------
> [-1372225904,-410785016)
> (1 row)
> ```
>
> This is the result of the condition:
> ```
> PREPARE prepare_query (text, int4range, text, text, inet) AS SELECT
> ((((quote_literal($3) SIMILAR TO (($4)||($5))))));
> EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
> '?', 'j&Q', '171.191.143.34');
> ?column?
> ----------
> f
> (1 row)
> ```
>
>
> The equivalent normal UPDATE can execute correctly `UPDATE t2 SET c0=DEFAULT
> WHERE NOT (((((CAST('-2073583882'::text AS
>
> int4range))&&(((t2.c0)-('[-1190073754,1650158810)'::int4range)))))AND((quote_literal('?'::text)
> SIMILAR TO (('j&Q'::text)||('171.191.143.34'::inet))))));`
>
>
>
I run your SQL on 17.6, I got this:
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
11.4.0-1ubuntu1~22.04.2) 11.4.0, 64-bit
(1 row)
postgres=# SET plan_cache_mode = force_generic_plan;
CREATE TABLE t2(c0 int4range);
INSERT INTO t2(c0) VALUES('[-1372225904,-410785016)'::int4range) ON CONFLICT
DO NOTHING;
PREPARE prepare_query (text, int4range, text, text, inet) AS UPDATE t2 SET
c0=DEFAULT WHERE NOT (((((CAST($1 AS
int4range))&&(((t2.c0)-($2)))))AND((quote_literal($3) SIMILAR TO
(($4)||($5))))));
EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&Q', '171.191.143.34');
DEALLOCATE prepare_query;
SELECT DISTINCT * FROM t2;
SET
CREATE TABLE
INSERT 0 1
PREPARE
ERROR: malformed range literal: "-2073583882"
DETAIL: Missing left parenthesis or bracket.
DEALLOCATE
c0
--------------------------
[-1372225904,-410785016)
(1 row)
EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&Q', '171.191.143.34');
This SQL reported error: malformed range literal: "-2073583882", so it did
nothing.
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan
2025-12-16 03:06 BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan PG Bug reporting form <noreply@postgresql.org>
2025-12-26 06:59 ` Re: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
2025-12-26 07:20 ` Re: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan Tender Wang <tndrwang@gmail.com>
@ 2025-12-26 07:59 ` =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
0 siblings, 0 replies; 4+ messages in thread
From: ZhangChi @ 2025-12-26 07:59 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Hi Tender Wang,
Thank you very much for your reply. When I first ran this test case, I did not observe any error. However, I am currently unable to reproduce the issue. I apologize for submitting a false report.
Best,
Chi
ZhangChi
798604270@qq.com
Original
From: Tender Wang <tndrwang@gmail.com>
Date: 2025-12-26 15:20
To: ZhangChi <798604270@qq.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan
ZhangChi <798604270@qq.com> 于2025年12月26日周五 15:00写道：
Hi,
Has anyone had a look at this report?
Best,
Chi
Original
From: PG Bug reporting form <noreply@postgresql.org>
Date: 2025-12-16 11:06
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Cc: 798604270 <798604270@qq.com>
Subject: BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan
The following bug has been logged on the website:
Bug reference: 19356
Logged by: Chi Zhang
Email address: 798604270@qq.com
PostgreSQL version: 17.6
Operating system: ubuntu 24.04 with docker
Description:
Hi,
In the following test case, the WHERE condition of UPDATE is true; however,
the value in the table is not updated:
```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t2(c0 int4range);
INSERT INTO t2(c0) VALUES('[-1372225904,-410785016)'::int4range) ON CONFLICT
DO NOTHING;
PREPARE prepare_query (text, int4range, text, text, inet) AS UPDATE t2 SET
c0=DEFAULT WHERE NOT (((((CAST($1 AS
int4range))&&(((t2.c0)-($2)))))AND((quote_literal($3) SIMILAR TO
(($4)||($5))))));
EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&Q', '171.191.143.34');
DEALLOCATE prepare_query;
SELECT DISTINCT * FROM t2;
c0
--------------------------
[-1372225904,-410785016)
(1 row)
```
This is the result of the condition:
```
PREPARE prepare_query (text, int4range, text, text, inet) AS SELECT
((((quote_literal($3) SIMILAR TO (($4)||($5))))));
EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&Q', '171.191.143.34');
?column?
----------
f
(1 row)
```
The equivalent normal UPDATE can execute correctly `UPDATE t2 SET c0=DEFAULT
WHERE NOT (((((CAST('-2073583882'::text AS
int4range))&&(((t2.c0)-('[-1190073754,1650158810)'::int4range)))))AND((quote_literal('?'::text)
SIMILAR TO (('j&Q'::text)||('171.191.143.34'::inet))))));`
I run your SQL on 17.6, I got this:
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.2) 11.4.0, 64-bit
(1 row)
postgres=# SET plan_cache_mode = force_generic_plan;
CREATE TABLE t2(c0 int4range);
INSERT INTO t2(c0) VALUES('[-1372225904,-410785016)'::int4range) ON CONFLICT
DO NOTHING;
PREPARE prepare_query (text, int4range, text, text, inet) AS UPDATE t2 SET
c0=DEFAULT WHERE NOT (((((CAST($1 AS
int4range))&&(((t2.c0)-($2)))))AND((quote_literal($3) SIMILAR TO
(($4)||($5))))));
EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&Q', '171.191.143.34');
DEALLOCATE prepare_query;
SELECT DISTINCT * FROM t2;
SET
CREATE TABLE
INSERT 0 1
PREPARE
ERROR: malformed range literal: "-2073583882"
DETAIL: Missing left parenthesis or bracket.
DEALLOCATE
c0
--------------------------
[-1372225904,-410785016)
(1 row)
EXECUTE prepare_query('-2073583882', '[-1190073754,1650158810)'::int4range,
'?', 'j&Q', '171.191.143.34');
This SQL reported error: malformed range literal: "-2073583882", so it did nothing.
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-12-26 07:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-16 03:06 BUG #19356: Unexpected result of prepared UPDATE with force_generic_plan PG Bug reporting form <noreply@postgresql.org>
2025-12-26 06:59 ` =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
2025-12-26 07:20 ` Tender Wang <tndrwang@gmail.com>
2025-12-26 07:59 ` =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
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