[PATCH v1] Fix stale composite type OIDs in cached PL/pgSQL expressions

From: yuansong <yyuansong(at)126(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Cc: yuansong <yyuansong(at)126(dot)com>, yang lei <yanglei(at)halodbtech(dot)com>
Subject: [PATCH v1] Fix stale composite type OIDs in cached PL/pgSQL expressions
Date: 2026-11-20 15:37:56
Message-ID: 20261120153756.3135-1-yyuansong@126.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

When a composite type is dropped and recreated, or altered (ALTER TYPE ...
ALTER ATTRIBUTE), within the same session, a PL/pgSQL routine that uses a
variable of that type starts failing on subsequent calls with either

ERROR: cache lookup failed for type <old oid>

or

ERROR: type of parameter N (<type>) does not match that when preparing
the plan (<type>)

Root cause
----------
PL/pgSQL caches simple expressions (expr->expr_simple_expr) containing
PARAM_EXTERN nodes whose paramtype is the OID resolved at the time the
expression was first compiled. revalidate_rectypeid() refreshes the
variable's current type OID, but the cached expression still holds the
old OID. After the type is replaced that OID no longer exists in the
syscache, so reusing the cached expression fails. Rebuilding the SPI
plan unconditionally would fix it but defeats the simple-expression fast
path, so the replan must be targeted.

Fix
---
- Call revalidate_rectypeid() at the three points where a record datum's
type is reported (exec_eval_datum, plpgsql_exec_get_datum_type,
plpgsql_exec_get_datum_type_info), so the current type OID is always
refreshed before use.
- Before reusing a cached simple expression, walk its expression tree
(new expr_simple_param_types_stale() / param_type_check_walker()) and
compare every PARAM_EXTERN's paramtype against the type of the current
datum. If any no longer matches, free the SPI plan and reprepare via
exec_prepare_plan(), which regenerates fresh Param nodes with the
current types and re-saves the simple expression.

The check is a cheap tree walk done once per expression evaluation and
only forces a replan when a type actually changed, so the common path is
unchanged. No measurable performance impact.

Tests
-----
Add three regression tests in plpgsql.sql / expected/plpgsql.out covering
DROP/CREATE with RAISE NOTICE, DROP/CREATE with assignment, and ALTER TYPE
with field access. Each calls the routine twice around the type change
and asserts identical NOTICE output with no error.

Verified on master at 555d97c318 (--enable-debug --enable-cassert): the
three cases pass with the patch and fail without it; the plpgsql
regression test passes; the rest of the regression suite shows no new
failures.

Note
----
The plain SQL PREPARE/EXECUTE case from the original report is a separate
core plancache issue (EvaluateParams() coerces arguments using the OID
fixed at PREPARE time, and plancache.c assumes parameter types never
change). Upstream has previously declined to alter that behavior for
semantic/security reasons, so it is intentionally out of scope here and
should be tracked separately.

Bug: #19399
Reported-by: yang lei <yanglei(at)halodbtech(dot)com>
Signed-off-by: yuansong <yyuansong(at)126(dot)com>
---
src/pl/plpgsql/src/pl_exec.c | 102 ++++++++++++++++++++++++++
src/test/regress/expected/plpgsql.out | 48 ++++++++++++
src/test/regress/sql/plpgsql.sql | 49 +++++++++++++
3 files changed, 199 insertions(+)

diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 7540c8c565..a13abb9cf4 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -351,6 +351,8 @@ static void exec_check_rw_parameter(PLpgSQL_expr *expr, int paramid);
static bool count_param_references(Node *node,
count_param_references_context *context);
static void exec_check_assignable(PLpgSQL_execstate *estate, int dno);
+static bool expr_simple_param_types_stale(PLpgSQL_execstate *estate,
+ PLpgSQL_expr *expr);
static bool exec_eval_simple_expr(PLpgSQL_execstate *estate,
PLpgSQL_expr *expr,
Datum *result,
@@ -5507,6 +5509,13 @@ exec_eval_datum(PLpgSQL_execstate *estate,
{
PLpgSQL_rec *rec = (PLpgSQL_rec *) datum;

+ /*
+ * BUG #19399: refresh a possibly-stale composite type OID
+ * before reporting it (type may be dropped/recreated).
+ */
+ if (rec->rectypeid != RECORDOID)
+ revalidate_rectypeid(rec);
+
if (rec->erh == NULL)
{
/* Treat uninstantiated record as a simple NULL */
@@ -5624,6 +5633,13 @@ plpgsql_exec_get_datum_type(PLpgSQL_execstate *estate,
{
PLpgSQL_rec *rec = (PLpgSQL_rec *) datum;

+ /*
+ * BUG #19399: refresh a possibly-stale composite type OID
+ * before reporting it (type may be dropped/recreated).
+ */
+ if (rec->rectypeid != RECORDOID)
+ revalidate_rectypeid(rec);
+
if (rec->erh == NULL || rec->rectypeid != RECORDOID)
{
/* Report variable's declared type */
@@ -5710,6 +5726,13 @@ plpgsql_exec_get_datum_type_info(PLpgSQL_execstate *estate,
{
PLpgSQL_rec *rec = (PLpgSQL_rec *) datum;

+ /*
+ * BUG #19399: refresh a possibly-stale composite type OID
+ * before reporting it (type may be dropped/recreated).
+ */
+ if (rec->rectypeid != RECORDOID)
+ revalidate_rectypeid(rec);
+
if (rec->erh == NULL || rec->rectypeid != RECORDOID)
{
/* Report variable's declared type */
@@ -5829,6 +5852,68 @@ exec_eval_boolean(PLpgSQL_execstate *estate,
* NOTE: caller must do exec_eval_cleanup when done with the Datum.
* ----------
*/
+/*
+ * BUG #19399 support: detect PARAM_EXTERN nodes in a cached simple-expression
+ * tree whose paramtype no longer matches the current type of the datum they
+ * refer to. This happens when a record variable's named composite type is
+ * dropped/recreated or altered in-session: the Param node freezes the old OID
+ * at first planning, and later evaluation fails with "cache lookup failed".
+ */
+typedef struct ParamTypeCheckContext
+{
+ PLpgSQL_execstate *estate;
+ bool stale;
+} ParamTypeCheckContext;
+
+static bool
+param_type_check_walker(Node *node, ParamTypeCheckContext *context)
+{
+ if (node == NULL)
+ return false;
+ else if (IsA(node, Param))
+ {
+ Param *param = (Param *) node;
+ PLpgSQL_execstate *estate = context->estate;
+
+ if (param->paramkind == PARAM_EXTERN)
+ {
+ int dno = param->paramid - 1;
+
+ if (dno >= 0 && dno < estate->ndatums)
+ {
+ Oid curtype;
+ int32 curtypmod;
+ Oid curcoll;
+
+ plpgsql_exec_get_datum_type_info(estate, estate->datums[dno],
+ &curtype, &curtypmod, &curcoll);
+ if (curtype != param->paramtype)
+ {
+ context->stale = true;
+ return true; /* abort traversal */
+ }
+ }
+ }
+ return false;
+ }
+ else
+ return expression_tree_walker(node, param_type_check_walker, context);
+}
+
+static bool
+expr_simple_param_types_stale(PLpgSQL_execstate *estate, PLpgSQL_expr *expr)
+{
+ ParamTypeCheckContext context;
+
+ if (expr->expr_simple_expr == NULL)
+ return false;
+
+ context.estate = estate;
+ context.stale = false;
+ (void) param_type_check_walker((Node *) expr->expr_simple_expr, &context);
+ return context.stale;
+}
+
static Datum
exec_eval_expr(PLpgSQL_execstate *estate,
PLpgSQL_expr *expr,
@@ -5842,7 +5927,24 @@ exec_eval_expr(PLpgSQL_execstate *estate,

/*
* If first time through, create a plan for this expression.
+ *
+ * BUG #19399: a cached simple expression can hold a stale
+ * composite-type OID in one of its PARAM_EXTERN nodes. Rebuild
+ * the plan when that happens, so the Param nodes are recreated
+ * with the variables' current types.
*/
+ if (expr->plan != NULL &&
+ expr_simple_param_types_stale(estate, expr))
+ {
+ SPI_freeplan(expr->plan);
+ expr->plan = NULL;
+ expr->expr_simple_expr = NULL;
+ expr->expr_simple_plansource = NULL;
+ expr->expr_simple_plan = NULL;
+ expr->expr_simple_plan_lxid = InvalidLocalTransactionId;
+ expr->expr_rw_param = NULL;
+ expr->expr_rwopt = PLPGSQL_RWOPT_UNKNOWN;
+ }
if (expr->plan == NULL)
exec_prepare_plan(estate, expr, CURSOR_OPT_PARALLEL_OK);

diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 8f3e214bb4..c9a5d31734 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -5832,3 +5832,51 @@ END; $$ LANGUAGE plpgsql;
ERROR: "x" is not a scalar variable
LINE 3: GET DIAGNOSTICS x = ROW_COUNT;
^
+--
+-- BUG #19399: composite type replaced/altered in-session must not leave
+-- stale parameter types in cached PL/pgSQL expressions.
+--
+create type bug19399type as (f int);
+create or replace procedure bug19399_raise() as $$
+declare a bug19399type;
+begin
+ a.f = 1;
+ raise notice 'bug19399 raise: %', a;
+end;
+$$ language plpgsql;
+call bug19399_raise();
+NOTICE: bug19399 raise: (1)
+drop type bug19399type;
+create type bug19399type as (f int);
+call bug19399_raise();
+NOTICE: bug19399 raise: (1)
+create type bug19399type2 as (f int);
+create or replace procedure bug19399_assign() as $$
+declare a bug19399type2; b bug19399type2;
+begin
+ b.f = 1; a = b;
+end;
+$$ language plpgsql;
+call bug19399_assign();
+drop type bug19399type2;
+create type bug19399type2 as (f int);
+call bug19399_assign();
+create type bug19399type3 as (f text);
+create or replace procedure bug19399_field() as $$
+declare a bug19399type3;
+begin
+ a.f = 'x';
+ raise notice 'bug19399 field: %', a.f;
+end;
+$$ language plpgsql;
+call bug19399_field();
+NOTICE: bug19399 field: x
+alter type bug19399type3 alter attribute f type varchar(10);
+call bug19399_field();
+NOTICE: bug19399 field: x
+drop procedure bug19399_raise();
+drop procedure bug19399_assign();
+drop procedure bug19399_field();
+drop type bug19399type;
+drop type bug19399type2;
+drop type bug19399type3 cascade;
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 9d8de5aaaf..3dfa8e4b92 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -4768,3 +4768,52 @@ BEGIN
GET DIAGNOSTICS x = ROW_COUNT;
RETURN;
END; $$ LANGUAGE plpgsql;
+
+
+--
+-- BUG #19399: composite type replaced/altered in-session must not leave
+-- stale parameter types in cached PL/pgSQL expressions.
+--
+create type bug19399type as (f int);
+create or replace procedure bug19399_raise() as $$
+declare a bug19399type;
+begin
+ a.f = 1;
+ raise notice 'bug19399 raise: %', a;
+end;
+$$ language plpgsql;
+call bug19399_raise();
+drop type bug19399type;
+create type bug19399type as (f int);
+call bug19399_raise();
+
+create type bug19399type2 as (f int);
+create or replace procedure bug19399_assign() as $$
+declare a bug19399type2; b bug19399type2;
+begin
+ b.f = 1; a = b;
+end;
+$$ language plpgsql;
+call bug19399_assign();
+drop type bug19399type2;
+create type bug19399type2 as (f int);
+call bug19399_assign();
+
+create type bug19399type3 as (f text);
+create or replace procedure bug19399_field() as $$
+declare a bug19399type3;
+begin
+ a.f = 'x';
+ raise notice 'bug19399 field: %', a.f;
+end;
+$$ language plpgsql;
+call bug19399_field();
+alter type bug19399type3 alter attribute f type varchar(10);
+call bug19399_field();
+
+drop procedure bug19399_raise();
+drop procedure bug19399_assign();
+drop procedure bug19399_field();
+drop type bug19399type;
+drop type bug19399type2;
+drop type bug19399type3 cascade;
--
2.47.3

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Fujii Masao 2026-11-20 15:43:36 Re: BUG #19631: currtid2() on a view with GROUP BY ctid crashes with XX000
Previous Message Laurenz Albe 2026-11-20 15:29:04 Re: BUG #19927: PostgreSQL 16: SQL dump adds experimental/incompatible options like \restrict

Browse pgsql-hackers by date

  From Date Subject
Next Message Virender Singla 2026-11-20 15:40:29 Re: Allow pg_read_all_stats to read replication origin status
Previous Message Peter Eisentraut 2026-11-20 15:34:53 Re: compiler attribute alloc_size