pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19862: Replacing a cast leaves prepared statements using the old cast function
3+ messages / 3 participants
[nested] [flat]

* BUG #19862: Replacing a cast leaves prepared statements using the old cast function
@ 2026-11-11 02:26  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 2 replies; 3+ messages in thread

From: PG Bug reporting form @ 2026-11-11 02:26 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: imchifan@mail.com

The following bug has been logged on the website:

Bug reference:      19862
Logged by:          Faye
Email address:      imchifan@mail.com
PostgreSQL version: Unsupported/Unknown
Operating system:   Linux/amd64
Description:        

Dropping and recreating a user-defined cast with a different cast function
does not invalidate a statement prepared while the original cast existed.
After the replacement, direct execution uses the new function and returns
v2, while the prepared statement silently continues to use the old function
and returns v1.

The prepared statement should use the current cast definition, as direct
execution does. Both executions should therefore return v2 after the cast is
replaced.

How to repeat:

\set ON_ERROR_STOP on

DROP TYPE IF EXISTS cast_src CASCADE;
DROP TYPE IF EXISTS cast_dst CASCADE;

CREATE TYPE cast_src AS ENUM ('x');
CREATE TYPE cast_dst AS ENUM ('v1', 'v2');

CREATE FUNCTION cast_v1(cast_src)
RETURNS cast_dst
LANGUAGE SQL IMMUTABLE
AS $$ SELECT 'v1'::cast_dst $$;

CREATE FUNCTION cast_v2(cast_src)
RETURNS cast_dst
LANGUAGE SQL IMMUTABLE
AS $$ SELECT 'v2'::cast_dst $$;

CREATE CAST (cast_src AS cast_dst)
WITH FUNCTION cast_v1(cast_src);

PREPARE cast_stmt(cast_src) AS
SELECT $1::cast_dst AS value;

EXECUTE cast_stmt('x') \gset before_

DROP CAST (cast_src AS cast_dst);
CREATE CAST (cast_src AS cast_dst)
WITH FUNCTION cast_v2(cast_src);

SELECT ('x'::cast_src)::cast_dst AS value \gset direct_
EXECUTE cast_stmt('x') \gset prepared_

SELECT :'before_value' AS initial_prepared_value,
       :'direct_value' AS direct_value_after_cast_replacement,
       :'prepared_value' AS prepared_value_after_cast_replacement,
       :'direct_value' = :'prepared_value' AS prepared_matches_direct;

DEALLOCATE cast_stmt;
DROP CAST (cast_src AS cast_dst);
DROP FUNCTION cast_v1(cast_src);
DROP FUNCTION cast_v2(cast_src);
DROP TYPE cast_src;
DROP TYPE cast_dst;

The recorded result is:

 initial_prepared_value | direct_value_after_cast_replacement |
prepared_value_after_cast_replacement | prepared_matches_direct
------------------------+-------------------------------------+---------------------------------------+-------------------------
 v1                     | v2                                  | v1
| f
(1 row)

This is consistent with parse analysis retaining the originally selected
cast function without invalidating the prepared statement when the cast
catalog entry changes.

The issue was reproduced on PostgreSQL 20devel. It was also reproduced
separately on PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2). Both environments
were Linux/amd64. No server interruption or server-log error occurred. This
occurs when a user-defined cast is replaced with one backed by a different
function after a statement using that cast has been prepared.








^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #19862: Replacing a cast leaves prepared statements using the old cast function
@ 2026-11-11 12:56  Kirill Reshke <reshkekirill@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Kirill Reshke @ 2026-11-11 12:56 UTC (permalink / raw)
  To: imchifan@mail.com; pgsql-bugs@lists.postgresql.org

On Wed, 11 Nov 2026 at 15:32, PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      19862
> Logged by:          Faye
> Email address:      imchifan@mail.com
> PostgreSQL version: Unsupported/Unknown
> Operating system:   Linux/amd64
> Description:
>
> Dropping and recreating a user-defined cast with a different cast function
> does not invalidate a statement prepared while the original cast existed.
> After the replacement, direct execution uses the new function and returns
> v2, while the prepared statement silently continues to use the old function
> and returns v1.
>
> The prepared statement should use the current cast definition, as direct
> execution does. Both executions should therefore return v2 after the cast is
> replaced.
>
> How to repeat:
>
> \set ON_ERROR_STOP on
>
> DROP TYPE IF EXISTS cast_src CASCADE;
> DROP TYPE IF EXISTS cast_dst CASCADE;
>
> CREATE TYPE cast_src AS ENUM ('x');
> CREATE TYPE cast_dst AS ENUM ('v1', 'v2');
>
> CREATE FUNCTION cast_v1(cast_src)
> RETURNS cast_dst
> LANGUAGE SQL IMMUTABLE
> AS $$ SELECT 'v1'::cast_dst $$;
>
> CREATE FUNCTION cast_v2(cast_src)
> RETURNS cast_dst
> LANGUAGE SQL IMMUTABLE
> AS $$ SELECT 'v2'::cast_dst $$;
>
> CREATE CAST (cast_src AS cast_dst)
> WITH FUNCTION cast_v1(cast_src);
>
> PREPARE cast_stmt(cast_src) AS
> SELECT $1::cast_dst AS value;
>
> EXECUTE cast_stmt('x') \gset before_
>
> DROP CAST (cast_src AS cast_dst);
> CREATE CAST (cast_src AS cast_dst)
> WITH FUNCTION cast_v2(cast_src);
>
> SELECT ('x'::cast_src)::cast_dst AS value \gset direct_
> EXECUTE cast_stmt('x') \gset prepared_
>
> SELECT :'before_value' AS initial_prepared_value,
>        :'direct_value' AS direct_value_after_cast_replacement,
>        :'prepared_value' AS prepared_value_after_cast_replacement,
>        :'direct_value' = :'prepared_value' AS prepared_matches_direct;
>
> DEALLOCATE cast_stmt;
> DROP CAST (cast_src AS cast_dst);
> DROP FUNCTION cast_v1(cast_src);
> DROP FUNCTION cast_v2(cast_src);
> DROP TYPE cast_src;
> DROP TYPE cast_dst;
>
> The recorded result is:
>
>  initial_prepared_value | direct_value_after_cast_replacement |
> prepared_value_after_cast_replacement | prepared_matches_direct
> ------------------------+-------------------------------------+---------------------------------------+-------------------------
>  v1                     | v2                                  | v1
> | f
> (1 row)
>
> This is consistent with parse analysis retaining the originally selected
> cast function without invalidating the prepared statement when the cast
> catalog entry changes.
>
> The issue was reproduced on PostgreSQL 20devel. It was also reproduced
> separately on PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2). Both environments
> were Linux/amd64. No server interruption or server-log error occurred. This
> occurs when a user-defined cast is replaced with one backed by a different
> function after a statement using that cast has been prepared.
>


Hi! Yes, reproduced cast issue here. I also reported something similar
here[0]. There are some more catalog objects which miss catalog
invalidate hooks. Among those, there are 6 types of objects for which
object altering can make cached plan wrong:

  CASTSOURCETARGET
  AMPROCNUM
  COLLOID
  TSCONFIGOID
  TSCONFIGMAP
  TSDICTOID

Patch contains only these six fixes. In v2-0002 there are examples on
how to make cached plan to behave wrong for each object type. It is
not intended to commit.


[0] https://www.postgresql.org/message-id/CALdSSPjeif5xn405aEPs_PxRimYoHdodcwuwEfOfqmkRU4Gqmg%40mail.gma...


-- 
Best regards,
Kirill Reshke

Attachments: download all (2 files, .tar.gz)

v2-0001-Invalidate-plans-after-catalog-changes.patch (application/octet-stream, 1.5K) download From 44849f44892f06204c99ac088298a6e418d7276d Mon Sep 17 00:00:00 2001 From: root <root@localhost> Date: Wed, 11 Nov 2026 12:32:56 +0000 Subject: [PATCH v2 1/2] Invalidate plans after catalog changes --- src/backend/utils/cache/plancache.c | 6 ++++++ 1 file changed, 6 insertions(+) diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c index a1b406cee2..20d7ccc313 100644 --- a/src/backend/utils/cache/plancache.c +++ b/src/backend/utils/cache/plancache.c @@ -159,6 +159,12 @@ InitPlanCache(void) CacheRegisterSyscacheCallback(NAMESPACEOID, PlanCacheSysCallback, (Datum) 0); CacheRegisterSyscacheCallback(OPEROID, PlanCacheSysCallback, (Datum) 0); CacheRegisterSyscacheCallback(AMOPOPID, PlanCacheSysCallback, (Datum) 0); + CacheRegisterSyscacheCallback(AMPROCNUM, PlanCacheSysCallback, (Datum) 0); + CacheRegisterSyscacheCallback(CASTSOURCETARGET, PlanCacheSysCallback, (Datum) 0); + CacheRegisterSyscacheCallback(COLLOID, PlanCacheSysCallback, (Datum) 0); + CacheRegisterSyscacheCallback(TSCONFIGOID, PlanCacheSysCallback, (Datum) 0); + CacheRegisterSyscacheCallback(TSCONFIGMAP, PlanCacheSysCallback, (Datum) 0); + CacheRegisterSyscacheCallback(TSDICTOID, PlanCacheSysCallback, (Datum) 0); CacheRegisterSyscacheCallback(FOREIGNSERVEROID, PlanCacheSysCallback, (Datum) 0); CacheRegisterSyscacheCallback(FOREIGNDATAWRAPPEROID, PlanCacheSysCallback, (Datum) 0); CacheRegisterSyscacheCallback(AUTHMEMROLEMEM, PlanCacheRoleCallback, (Datum) 0); -- 2.43.0
v2-0002-Test-plan-invalidation-after-catalog-changes.patch (application/octet-stream, 18.1K) download From 67e601cc0dc0538c8522b106eebe11a3aac9607c Mon Sep 17 00:00:00 2001 From: root <root@localhost> Date: Wed, 11 Nov 2026 12:32:56 +0000 Subject: [PATCH v2 2/2] Test plan invalidation after catalog changes --- src/test/regress/expected/plancache_inval.out | 266 ++++++++++++++++++ src/test/regress/parallel_schedule | 3 + src/test/regress/sql/plancache_inval.sql | 172 +++++++++++ 3 files changed, 441 insertions(+) create mode 100644 src/test/regress/expected/plancache_inval.out create mode 100644 src/test/regress/sql/plancache_inval.sql diff --git a/src/test/regress/expected/plancache_inval.out b/src/test/regress/expected/plancache_inval.out new file mode 100644 index 0000000000..f23784205b --- /dev/null +++ b/src/test/regress/expected/plancache_inval.out @@ -0,0 +1,266 @@ +-- Check plan invalidation when catalog definitions change. +-- Replacing a cast must invalidate queries using its old function. Create +-- both functions first, so only the cast changes after preparing the queries. +CREATE TYPE prepare_cast_src AS ENUM ('x'); +CREATE TYPE prepare_cast_dst AS ENUM ('v1', 'v2'); +CREATE FUNCTION prepare_cast_v1(prepare_cast_src) RETURNS prepare_cast_dst + LANGUAGE SQL IMMUTABLE AS $$ SELECT 'v1'::prepare_cast_dst $$; +CREATE FUNCTION prepare_cast_v2(prepare_cast_src) RETURNS prepare_cast_dst + LANGUAGE SQL IMMUTABLE AS $$ SELECT 'v2'::prepare_cast_dst $$; +CREATE CAST (prepare_cast_src AS prepare_cast_dst) + WITH FUNCTION prepare_cast_v1(prepare_cast_src); +SET plan_cache_mode = force_generic_plan; +PREPARE cast_generic(prepare_cast_src) AS SELECT $1::prepare_cast_dst AS value; +EXECUTE cast_generic('x'); + value +------- + v1 +(1 row) + +SET plan_cache_mode = force_custom_plan; +PREPARE cast_custom(prepare_cast_src) AS SELECT $1::prepare_cast_dst AS value; +EXECUTE cast_custom('x'); + value +------- + v1 +(1 row) + +DROP CAST (prepare_cast_src AS prepare_cast_dst); +CREATE CAST (prepare_cast_src AS prepare_cast_dst) + WITH FUNCTION prepare_cast_v2(prepare_cast_src); +-- Re-analyze the queries for both generic and custom plans. +SET plan_cache_mode = force_generic_plan; +EXECUTE cast_generic('x'); + value +------- + v2 +(1 row) + +SET plan_cache_mode = force_custom_plan; +EXECUTE cast_custom('x'); + value +------- + v2 +(1 row) + +-- Fresh analysis must agree with the revalidated prepared statements. +SELECT ('x'::prepare_cast_src)::prepare_cast_dst AS value; + value +------- + v2 +(1 row) + +PREPARE cast_fresh(prepare_cast_src) AS SELECT $1::prepare_cast_dst AS value; +EXECUTE cast_fresh('x'); + value +------- + v2 +(1 row) + +DEALLOCATE cast_generic; +DEALLOCATE cast_custom; +DEALLOCATE cast_fresh; +RESET plan_cache_mode; +DROP CAST (prepare_cast_src AS prepare_cast_dst); +DROP FUNCTION prepare_cast_v1(prepare_cast_src); +DROP FUNCTION prepare_cast_v2(prepare_cast_src); +DROP TYPE prepare_cast_src; +DROP TYPE prepare_cast_dst; +-- Replacing an operator-family support function must invalidate analyzed +-- queries as well as generic plans. RANGE frame offsets retain the selected +-- in_range function OID in their WindowClause. Create both functions before +-- preparing the queries, so pg_proc invalidations cannot mask the dependency. +CREATE SCHEMA amproc_plan; +SET search_path = amproc_plan, pg_catalog; +CREATE FUNCTION range1(int, int, int, boolean, boolean) RETURNS boolean + LANGUAGE sql IMMUTABLE STRICT + RETURN pg_catalog.in_range($1, $2, $3, $4, $5); +CREATE FUNCTION range2(int, int, int, boolean, boolean) RETURNS boolean + LANGUAGE sql IMMUTABLE STRICT + RETURN pg_catalog.in_range($1, $2, 2 * $3, $4, $5); +CREATE OPERATOR <^ (LEFTARG = int, RIGHTARG = int, FUNCTION = int4lt); +CREATE OPERATOR >^ (LEFTARG = int, RIGHTARG = int, FUNCTION = int4gt); +CREATE OPERATOR FAMILY range_ops USING btree; +ALTER OPERATOR FAMILY range_ops USING btree ADD + OPERATOR 1 <^ (int, int), + OPERATOR 2 <= (int, int), + OPERATOR 3 = (int, int), + OPERATOR 4 >= (int, int), + OPERATOR 5 >^ (int, int), + FUNCTION 1 (int, int) btint4cmp(int, int), + FUNCTION 3 (int, int) range1(int, int, int, boolean, boolean); +PREPARE amproc_generic(int) AS +SELECT array_agg(preceding ORDER BY x) AS preceding, + array_agg(following ORDER BY x) AS following +FROM (SELECT x, + sum(x) OVER (ORDER BY x USING <^ + RANGE BETWEEN $1 PRECEDING AND CURRENT ROW) AS preceding, + sum(x) OVER (ORDER BY x USING <^ + RANGE BETWEEN CURRENT ROW AND $1 FOLLOWING) AS following + FROM (VALUES (1), (2), (3), (4)) AS v(x)) AS s; +PREPARE amproc_custom(int) AS +SELECT array_agg(preceding ORDER BY x) AS preceding, + array_agg(following ORDER BY x) AS following +FROM (SELECT x, + sum(x) OVER (ORDER BY x USING <^ + RANGE BETWEEN $1 PRECEDING AND CURRENT ROW) AS preceding, + sum(x) OVER (ORDER BY x USING <^ + RANGE BETWEEN CURRENT ROW AND $1 FOLLOWING) AS following + FROM (VALUES (1), (2), (3), (4)) AS v(x)) AS s; +SET plan_cache_mode = force_generic_plan; +EXECUTE amproc_generic(1); + preceding | following +-----------+----------- + {1,3,5,7} | {3,5,7,4} +(1 row) + +SET plan_cache_mode = force_custom_plan; +EXECUTE amproc_custom(1); + preceding | following +-----------+----------- + {1,3,5,7} | {3,5,7,4} +(1 row) + +BEGIN; +ALTER OPERATOR FAMILY range_ops USING btree DROP FUNCTION 3 (int, int); +ALTER OPERATOR FAMILY range_ops USING btree ADD + FUNCTION 3 (int, int) range2(int, int, int, boolean, boolean); +COMMIT; +-- Both old prepared queries should agree with a newly analyzed query. +SET plan_cache_mode = force_generic_plan; +EXECUTE amproc_generic(1); + preceding | following +-----------+----------- + {1,3,6,9} | {6,9,7,4} +(1 row) + +SET plan_cache_mode = force_custom_plan; +EXECUTE amproc_custom(1); + preceding | following +-----------+----------- + {1,3,6,9} | {6,9,7,4} +(1 row) + +SELECT array_agg(preceding ORDER BY x) AS preceding, + array_agg(following ORDER BY x) AS following +FROM (SELECT x, + sum(x) OVER (ORDER BY x USING <^ + RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS preceding, + sum(x) OVER (ORDER BY x USING <^ + RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) AS following + FROM (VALUES (1), (2), (3), (4)) AS v(x)) AS s; + preceding | following +-----------+----------- + {1,3,6,9} | {6,9,7,4} +(1 row) + +DEALLOCATE amproc_generic; +DEALLOCATE amproc_custom; +RESET plan_cache_mode; +DROP OPERATOR FAMILY range_ops USING btree; +DROP OPERATOR <^ (int, int); +DROP OPERATOR >^ (int, int); +DROP FUNCTION range1(int, int, int, boolean, boolean); +DROP FUNCTION range2(int, int, int, boolean, boolean); +DROP SCHEMA amproc_plan; +RESET search_path; +-- Check that collation DDL invalidates cached plans. +CREATE COLLATION public.pcachecoll FROM pg_catalog."C"; +PREPARE pcachecollstmt AS + SELECT pg_collation_for('a' COLLATE public.pcachecoll) = 'pcachecoll' AS matches; +EXECUTE pcachecollstmt; + matches +--------- + t +(1 row) + +ALTER COLLATION public.pcachecoll RENAME TO pcachecoll_old; +CREATE COLLATION public.pcachecoll FROM pg_catalog."C"; +EXECUTE pcachecollstmt; + matches +--------- + t +(1 row) + +SELECT pg_collation_for('a' COLLATE public.pcachecoll) = 'pcachecoll' AS matches; + matches +--------- + t +(1 row) + +DEALLOCATE pcachecollstmt; +DROP COLLATION public.pcachecoll; +DROP COLLATION public.pcachecoll_old; +-- Recompute constant text search results when configuration mappings change. +CREATE TEXT SEARCH CONFIGURATION regress_ts_plan_config + (COPY = pg_catalog.english); +PREPARE regress_ts_plan_query AS + SELECT to_tsvector('regress_ts_plan_config'::regconfig, 'running') AS value; +EXECUTE regress_ts_plan_query; + value +--------- + 'run':1 +(1 row) + +ALTER TEXT SEARCH CONFIGURATION regress_ts_plan_config + ALTER MAPPING FOR asciiword WITH pg_catalog.simple; +EXECUTE regress_ts_plan_query; + value +------------- + 'running':1 +(1 row) + +SELECT to_tsvector('regress_ts_plan_config'::regconfig, 'running') AS value; + value +------------- + 'running':1 +(1 row) + +DEALLOCATE regress_ts_plan_query; +DROP TEXT SEARCH CONFIGURATION regress_ts_plan_config; +-- Changing dictionary options also invalidates results folded into plans. +CREATE TEXT SEARCH DICTIONARY regress_ts_plan_dict + (TEMPLATE = pg_catalog.simple, ACCEPT = false); +CREATE TEXT SEARCH CONFIGURATION regress_ts_plan_config + (COPY = pg_catalog.english); +ALTER TEXT SEARCH CONFIGURATION regress_ts_plan_config + ALTER MAPPING FOR asciiword WITH regress_ts_plan_dict, pg_catalog.english_stem; +PREPARE regress_ts_plan_query AS + SELECT to_tsvector('regress_ts_plan_config'::regconfig, 'running') AS value; +EXECUTE regress_ts_plan_query; + value +--------- + 'run':1 +(1 row) + +ALTER TEXT SEARCH DICTIONARY regress_ts_plan_dict (ACCEPT = true); +EXECUTE regress_ts_plan_query; + value +------------- + 'running':1 +(1 row) + +SELECT to_tsvector('regress_ts_plan_config'::regconfig, 'running') AS value; + value +------------- + 'running':1 +(1 row) + +DEALLOCATE regress_ts_plan_query; +DROP TEXT SEARCH CONFIGURATION regress_ts_plan_config; +DROP TEXT SEARCH DICTIONARY regress_ts_plan_dict; +-- Dropping a configuration without mappings must invalidate plans too. +CREATE TEXT SEARCH CONFIGURATION regress_ts_plan_empty + (PARSER = pg_catalog.default); +PREPARE regress_ts_plan_query AS + SELECT to_tsvector('regress_ts_plan_empty'::regconfig, 'running') AS value; +EXECUTE regress_ts_plan_query; + value +------- + +(1 row) + +DROP TEXT SEARCH CONFIGURATION regress_ts_plan_empty; +EXECUTE regress_ts_plan_query; +ERROR: text search configuration "regress_ts_plan_empty" does not exist +DEALLOCATE regress_ts_plan_query; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index bb13a7b6b8..fd0cbb5fb9 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -148,6 +148,9 @@ test: event_trigger_login # this test also uses event triggers, so likewise run it by itself test: fast_default +# Keep unrelated DDL from invalidating plans during these checks. +test: plancache_inval + # run tablespace test at the end because it drops the tablespace created during # setup that other tests may use. test: tablespace diff --git a/src/test/regress/sql/plancache_inval.sql b/src/test/regress/sql/plancache_inval.sql new file mode 100644 index 0000000000..a3a14b0d4d --- /dev/null +++ b/src/test/regress/sql/plancache_inval.sql @@ -0,0 +1,172 @@ +-- Check plan invalidation when catalog definitions change. +-- Replacing a cast must invalidate queries using its old function. Create +-- both functions first, so only the cast changes after preparing the queries. +CREATE TYPE prepare_cast_src AS ENUM ('x'); +CREATE TYPE prepare_cast_dst AS ENUM ('v1', 'v2'); +CREATE FUNCTION prepare_cast_v1(prepare_cast_src) RETURNS prepare_cast_dst + LANGUAGE SQL IMMUTABLE AS $$ SELECT 'v1'::prepare_cast_dst $$; +CREATE FUNCTION prepare_cast_v2(prepare_cast_src) RETURNS prepare_cast_dst + LANGUAGE SQL IMMUTABLE AS $$ SELECT 'v2'::prepare_cast_dst $$; +CREATE CAST (prepare_cast_src AS prepare_cast_dst) + WITH FUNCTION prepare_cast_v1(prepare_cast_src); + +SET plan_cache_mode = force_generic_plan; +PREPARE cast_generic(prepare_cast_src) AS SELECT $1::prepare_cast_dst AS value; +EXECUTE cast_generic('x'); +SET plan_cache_mode = force_custom_plan; +PREPARE cast_custom(prepare_cast_src) AS SELECT $1::prepare_cast_dst AS value; +EXECUTE cast_custom('x'); + +DROP CAST (prepare_cast_src AS prepare_cast_dst); +CREATE CAST (prepare_cast_src AS prepare_cast_dst) + WITH FUNCTION prepare_cast_v2(prepare_cast_src); + +-- Re-analyze the queries for both generic and custom plans. +SET plan_cache_mode = force_generic_plan; +EXECUTE cast_generic('x'); +SET plan_cache_mode = force_custom_plan; +EXECUTE cast_custom('x'); + +-- Fresh analysis must agree with the revalidated prepared statements. +SELECT ('x'::prepare_cast_src)::prepare_cast_dst AS value; +PREPARE cast_fresh(prepare_cast_src) AS SELECT $1::prepare_cast_dst AS value; +EXECUTE cast_fresh('x'); + +DEALLOCATE cast_generic; +DEALLOCATE cast_custom; +DEALLOCATE cast_fresh; +RESET plan_cache_mode; +DROP CAST (prepare_cast_src AS prepare_cast_dst); +DROP FUNCTION prepare_cast_v1(prepare_cast_src); +DROP FUNCTION prepare_cast_v2(prepare_cast_src); +DROP TYPE prepare_cast_src; +DROP TYPE prepare_cast_dst; + +-- Replacing an operator-family support function must invalidate analyzed +-- queries as well as generic plans. RANGE frame offsets retain the selected +-- in_range function OID in their WindowClause. Create both functions before +-- preparing the queries, so pg_proc invalidations cannot mask the dependency. +CREATE SCHEMA amproc_plan; +SET search_path = amproc_plan, pg_catalog; +CREATE FUNCTION range1(int, int, int, boolean, boolean) RETURNS boolean + LANGUAGE sql IMMUTABLE STRICT + RETURN pg_catalog.in_range($1, $2, $3, $4, $5); +CREATE FUNCTION range2(int, int, int, boolean, boolean) RETURNS boolean + LANGUAGE sql IMMUTABLE STRICT + RETURN pg_catalog.in_range($1, $2, 2 * $3, $4, $5); +CREATE OPERATOR <^ (LEFTARG = int, RIGHTARG = int, FUNCTION = int4lt); +CREATE OPERATOR >^ (LEFTARG = int, RIGHTARG = int, FUNCTION = int4gt); +CREATE OPERATOR FAMILY range_ops USING btree; +ALTER OPERATOR FAMILY range_ops USING btree ADD + OPERATOR 1 <^ (int, int), + OPERATOR 2 <= (int, int), + OPERATOR 3 = (int, int), + OPERATOR 4 >= (int, int), + OPERATOR 5 >^ (int, int), + FUNCTION 1 (int, int) btint4cmp(int, int), + FUNCTION 3 (int, int) range1(int, int, int, boolean, boolean); + +PREPARE amproc_generic(int) AS +SELECT array_agg(preceding ORDER BY x) AS preceding, + array_agg(following ORDER BY x) AS following +FROM (SELECT x, + sum(x) OVER (ORDER BY x USING <^ + RANGE BETWEEN $1 PRECEDING AND CURRENT ROW) AS preceding, + sum(x) OVER (ORDER BY x USING <^ + RANGE BETWEEN CURRENT ROW AND $1 FOLLOWING) AS following + FROM (VALUES (1), (2), (3), (4)) AS v(x)) AS s; +PREPARE amproc_custom(int) AS +SELECT array_agg(preceding ORDER BY x) AS preceding, + array_agg(following ORDER BY x) AS following +FROM (SELECT x, + sum(x) OVER (ORDER BY x USING <^ + RANGE BETWEEN $1 PRECEDING AND CURRENT ROW) AS preceding, + sum(x) OVER (ORDER BY x USING <^ + RANGE BETWEEN CURRENT ROW AND $1 FOLLOWING) AS following + FROM (VALUES (1), (2), (3), (4)) AS v(x)) AS s; +SET plan_cache_mode = force_generic_plan; +EXECUTE amproc_generic(1); +SET plan_cache_mode = force_custom_plan; +EXECUTE amproc_custom(1); + +BEGIN; +ALTER OPERATOR FAMILY range_ops USING btree DROP FUNCTION 3 (int, int); +ALTER OPERATOR FAMILY range_ops USING btree ADD + FUNCTION 3 (int, int) range2(int, int, int, boolean, boolean); +COMMIT; + +-- Both old prepared queries should agree with a newly analyzed query. +SET plan_cache_mode = force_generic_plan; +EXECUTE amproc_generic(1); +SET plan_cache_mode = force_custom_plan; +EXECUTE amproc_custom(1); +SELECT array_agg(preceding ORDER BY x) AS preceding, + array_agg(following ORDER BY x) AS following +FROM (SELECT x, + sum(x) OVER (ORDER BY x USING <^ + RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS preceding, + sum(x) OVER (ORDER BY x USING <^ + RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) AS following + FROM (VALUES (1), (2), (3), (4)) AS v(x)) AS s; + +DEALLOCATE amproc_generic; +DEALLOCATE amproc_custom; +RESET plan_cache_mode; +DROP OPERATOR FAMILY range_ops USING btree; +DROP OPERATOR <^ (int, int); +DROP OPERATOR >^ (int, int); +DROP FUNCTION range1(int, int, int, boolean, boolean); +DROP FUNCTION range2(int, int, int, boolean, boolean); +DROP SCHEMA amproc_plan; +RESET search_path; +-- Check that collation DDL invalidates cached plans. +CREATE COLLATION public.pcachecoll FROM pg_catalog."C"; +PREPARE pcachecollstmt AS + SELECT pg_collation_for('a' COLLATE public.pcachecoll) = 'pcachecoll' AS matches; +EXECUTE pcachecollstmt; +ALTER COLLATION public.pcachecoll RENAME TO pcachecoll_old; +CREATE COLLATION public.pcachecoll FROM pg_catalog."C"; +EXECUTE pcachecollstmt; +SELECT pg_collation_for('a' COLLATE public.pcachecoll) = 'pcachecoll' AS matches; +DEALLOCATE pcachecollstmt; +DROP COLLATION public.pcachecoll; +DROP COLLATION public.pcachecoll_old; +-- Recompute constant text search results when configuration mappings change. +CREATE TEXT SEARCH CONFIGURATION regress_ts_plan_config + (COPY = pg_catalog.english); +PREPARE regress_ts_plan_query AS + SELECT to_tsvector('regress_ts_plan_config'::regconfig, 'running') AS value; +EXECUTE regress_ts_plan_query; +ALTER TEXT SEARCH CONFIGURATION regress_ts_plan_config + ALTER MAPPING FOR asciiword WITH pg_catalog.simple; +EXECUTE regress_ts_plan_query; +SELECT to_tsvector('regress_ts_plan_config'::regconfig, 'running') AS value; +DEALLOCATE regress_ts_plan_query; +DROP TEXT SEARCH CONFIGURATION regress_ts_plan_config; + +-- Changing dictionary options also invalidates results folded into plans. +CREATE TEXT SEARCH DICTIONARY regress_ts_plan_dict + (TEMPLATE = pg_catalog.simple, ACCEPT = false); +CREATE TEXT SEARCH CONFIGURATION regress_ts_plan_config + (COPY = pg_catalog.english); +ALTER TEXT SEARCH CONFIGURATION regress_ts_plan_config + ALTER MAPPING FOR asciiword WITH regress_ts_plan_dict, pg_catalog.english_stem; +PREPARE regress_ts_plan_query AS + SELECT to_tsvector('regress_ts_plan_config'::regconfig, 'running') AS value; +EXECUTE regress_ts_plan_query; +ALTER TEXT SEARCH DICTIONARY regress_ts_plan_dict (ACCEPT = true); +EXECUTE regress_ts_plan_query; +SELECT to_tsvector('regress_ts_plan_config'::regconfig, 'running') AS value; +DEALLOCATE regress_ts_plan_query; +DROP TEXT SEARCH CONFIGURATION regress_ts_plan_config; +DROP TEXT SEARCH DICTIONARY regress_ts_plan_dict; + +-- Dropping a configuration without mappings must invalidate plans too. +CREATE TEXT SEARCH CONFIGURATION regress_ts_plan_empty + (PARSER = pg_catalog.default); +PREPARE regress_ts_plan_query AS + SELECT to_tsvector('regress_ts_plan_empty'::regconfig, 'running') AS value; +EXECUTE regress_ts_plan_query; +DROP TEXT SEARCH CONFIGURATION regress_ts_plan_empty; +EXECUTE regress_ts_plan_query; +DEALLOCATE regress_ts_plan_query; -- 2.43.0
^ permalink raw reply [nested|flat] 3+ messages in thread

* Re: BUG #19862: Replacing a cast leaves prepared statements using the old cast function
@ 2026-11-11 12:58  Heikki Linnakangas <hlinnaka@iki.fi>
  parent: PG Bug reporting form <noreply@postgresql.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Heikki Linnakangas @ 2026-11-11 12:58 UTC (permalink / raw)
  To: imchifan@mail.com; pgsql-bugs@lists.postgresql.org

> Dropping and recreating a user-defined cast with a different cast function
> does not invalidate a statement prepared while the original cast existed.
> After the replacement, direct execution uses the new function and returns
> v2, while the prepared statement silently continues to use the old function
> and returns v1.

There are gaps in invalidating prepared statements, like this one, but I 
wouldn't consider it a bug. Patches are welcome to improve it though.

- Heikki







^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2026-11-11 12:58 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-11 02:26 BUG #19862: Replacing a cast leaves prepared statements using the old cast function PG Bug reporting form <noreply@postgresql.org>
2026-11-11 12:56 ` Kirill Reshke <reshkekirill@gmail.com>
2026-11-11 12:58 ` Heikki Linnakangas <hlinnaka@iki.fi>

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