BUG #19862: Replacing a cast leaves prepared statements using the old cast function
BUG #19862: Replacing a cast leaves prepared statements using the old cast function
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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.
Re: BUG #19862: Replacing a cast leaves prepared statements using the old cast function
On Wed, 11 Nov 2026 at 15:32, PG Bug reporting form
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.gmail.com
--
Best regards,
Kirill Reshke
Re: BUG #19862: Replacing a cast leaves prepared statements using the old cast function
От:
Heikki Linnakangas <hlinnaka@iki.fi>
Дата:
> 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