pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19816: PL/Python SD dictionary is split by input collation in 19rc1
11+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2026-11-12 b74de75cc8 c13c414c6e plpython: share the SD dictionary across cache entries of a function.
2026-11-11 37f3fa9ca7 d4c302f547 d1c10bdf23 c9da042ec1 0e112315f6 b560839108 Doc: fix imprecise description of PL/Python's "SD" dictionary.
* BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1
@ 2026-10-29 22:25 PG Bug reporting form <noreply@postgresql.org>
2026-11-09 05:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: PG Bug reporting form @ 2026-10-29 22:25 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: marcos.castedo@anachronics.com
The following bug has been logged on the website:
Bug reference: 19816
Logged by: Marcos Castedo
Email address: marcos.castedo@anachronics.com
PostgreSQL version: 19rc1
Operating system: Debian GNU/Linux (x86_64)
Description:
Version: 19rc1, built from the postgresql-19rc1.tar.bz2 release tarball,
unmodified.
In 19rc1 PL/Python uses the generic function cache (funccache.c), so a
function gets one compiled entry per CachedFunctionHashKey, and that key
includes the call's input collation. Each entry builds its own PLyProcedure
and therefore its own SD dictionary. As a result, SD is no longer shared
between calls to the same function when the calls resolve to different input
collations.
The documentation describes SD as private data "between repeated calls to
the same function", and that is how it behaves in 17, where PL/Python's own
cache is keyed by function OID (plus the trigger relation). I have not
checked 18. Code that stores state in SD with one call and reads it back
with another now loses it whenever the two call sites differ in input
collation. The most common way to hit this is an optional text argument: a
call that passes it has the default collation, and a call that omits it
Steps to reproduce:
CREATE FUNCTION tt(set_ boolean, a text DEFAULT NULL) RETURNS int AS $$
if set_: SD['x'] = 1
return SD.get('x')
$$ LANGUAGE plpython3u;
SELECT tt(true, 'z'); -- 1
SELECT tt(false); -- 19rc1: NULL 17: 1
SELECT tt(false, 'z' COLLATE "C"); -- 19rc1: NULL
SELECT tt(false, 'w'); -- 1 (same collation as the first
call)
SELECT tt(false, NULL); -- 1 (unknown literal resolves to
text,
-- default collation)
Calls that agree in input collation see the same SD. With an optional
argument of a non-collatable type (int), all calls share SD. GD is not
affected.
Expected: SD is shared by all calls to the function in the session, as in
17.
Actual: SD is partitioned by input collation.
The collation is part of the key for good reason in PL/pgSQL, where it
affects the compiled code. PL/Python does not use the input collation when
compiling, so splitting the entry there only splits SD. A possible fix is to
keep the SD dictionary per function OID, outside the funccache entry, and
have every PLyProcedure for that function share it. Another is to leave the
collation out of the hash key for PL/Python. Plans that callers cache in SD
with plpy.prepare() also get duplicated per collation, but that only costs
memory; losing stored state is the visible regression.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1
2026-10-29 22:25 BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 PG Bug reporting form <noreply@postgresql.org>
@ 2026-11-09 05:21 ` shihao zhong <zhong950419@gmail.com>
2026-11-09 18:31 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Marcos Castedo <marcos.castedo@anachronics.com>
2026-11-09 19:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 11+ messages in thread
From: shihao zhong @ 2026-11-09 05:21 UTC (permalink / raw)
To: marcos.castedo@anachronics.com; pgsql-bugs@lists.postgresql.org; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
Hi,
Thanks for reporting that.
The attached patch keeps SD in its own hash keyed by function OID and
trigger relation, as 18 did. So every cache entry of a function shares
it.
Variables set with "global" in the function body are still split, since
sharing t
hose would also mean sharing the call stack state. I left that alone, but I
can look at it if you think it matters.
This is a 19 regression, I'll add it to the open items.
Attachments:
v1-0001-plpython-Share-the-SD-dictionary-across-all-cache.patch (application/octet-stream, 8.3K) download
From 51052e81757d12e661d2caff353c3a2b96bbfe30 Mon Sep 17 00:00:00 2001
From: shihao zhong <zhong950419@gmail.com>
Date: Sun, 8 Nov 2026 23:43:22 -0500
Subject: [PATCH v1] plpython: Share the SD dictionary across all cache entries
of a function.
Since commit 9bcb8a694b3 moved PL/Python onto funccache.c, each cache
entry gets its own SD dictionary. funccache.c builds separate entries
for the same function when the input collation differs, when a trigger
function is used by several triggers, and when a function returning
record is called with different column definition lists. So values
stored in SD by one call could be missing in the next one, which
contradicts the documentation and the behavior up to v18.
Keep the SD dictionaries in a separate hash table, keyed by function OID
and, for DML triggers, the relation, as the old PL/Python cache did.
All cache entries built from the same pg_proc row share one dictionary,
and a new definition of the function starts with an empty one.
Reported-by: Marcos Castedo
Discussion: https://postgr.es/m/19816-921b5cbc3ee63146@postgresql.org
Backpatch-through: 19
---
src/pl/plpython/expected/plpython_global.out | 24 +++++
src/pl/plpython/plpy_procedure.c | 95 ++++++++++++++++++--
src/pl/plpython/sql/plpython_global.sql | 15 ++++
src/tools/pgindent/typedefs.list | 2 +
4 files changed, 130 insertions(+), 6 deletions(-)
diff --git a/src/pl/plpython/expected/plpython_global.out b/src/pl/plpython/expected/plpython_global.out
index a4cfb1483f9..2e9b3f840db 100644
--- a/src/pl/plpython/expected/plpython_global.out
+++ b/src/pl/plpython/expected/plpython_global.out
@@ -50,3 +50,27 @@ SELECT global_test_two();
SD: set by global_test_two, GD: set by global_test_one
(1 row)
+-- SD is shared by all calls of a function, whatever the input collation,
+-- and starts over when the function is redefined
+CREATE FUNCTION static_test_coll(t text) RETURNS int4
+AS $$
+SD["n"] = SD.get("n", 0) + 1
+return SD["n"]
+$$ LANGUAGE plpython3u;
+SELECT static_test_coll('a'), static_test_coll('a' COLLATE "C");
+ static_test_coll | static_test_coll
+------------------+------------------
+ 1 | 2
+(1 row)
+
+CREATE OR REPLACE FUNCTION static_test_coll(t text) RETURNS int4
+AS $$
+SD["n"] = SD.get("n", 0) + 1
+return SD["n"]
+$$ LANGUAGE plpython3u;
+SELECT static_test_coll('a');
+ static_test_coll
+------------------
+ 1
+(1 row)
+
diff --git a/src/pl/plpython/plpy_procedure.c b/src/pl/plpython/plpy_procedure.c
index ce8b1e099e9..ec1c88750f9 100644
--- a/src/pl/plpython/plpy_procedure.c
+++ b/src/pl/plpython/plpy_procedure.c
@@ -6,6 +6,7 @@
#include "postgres.h"
+#include "access/htup_details.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/event_trigger.h"
@@ -18,12 +19,38 @@
#include "plpy_util.h"
#include "utils/builtins.h"
#include "utils/funccache.h"
+#include "utils/hsearch.h"
+#include "utils/rel.h"
#include "utils/syscache.h"
+/*
+ * SD dictionaries, one per function (and per relation, for DML triggers).
+ * funccache.c may build several PLyProcedures for one function, for example
+ * one per input collation, and they must all see the same SD.
+ */
+typedef struct PLyStaticsKey
+{
+ Oid fn_oid; /* function OID */
+ Oid fn_rel; /* triggered-on relation or InvalidOid */
+} PLyStaticsKey;
+
+typedef struct PLyStaticsEntry
+{
+ PLyStaticsKey key; /* hash key, must be first */
+ TransactionId fn_xmin; /* xmin of the pg_proc row */
+ ItemPointerData fn_tid; /* TID of the pg_proc row */
+ PyObject *statics; /* the SD dictionary */
+} PLyStaticsEntry;
+
+static HTAB *PLy_statics_hash = NULL;
+
static void PLy_procedure_create(PLyProcedure *proc,
HeapTuple procTup,
Oid fn_oid,
+ Oid fn_rel,
PLyTrigType is_trigger);
+static PyObject *PLy_procedure_get_statics(HeapTuple procTup,
+ Oid fn_oid, Oid fn_rel);
static char *PLy_procedure_munge_source(const char *name, const char *src);
static void PLy_compile_callback(FunctionCallInfo fcinfo,
HeapTuple procTup,
@@ -145,6 +172,7 @@ static void
PLy_procedure_create(PLyProcedure *proc,
HeapTuple procTup,
Oid fn_oid,
+ Oid fn_rel,
PLyTrigType is_trigger)
{
char procName[NAMEDATALEN + 256];
@@ -302,6 +330,9 @@ PLy_procedure_create(PLyProcedure *proc,
Anum_pg_proc_prosrc);
procSource = TextDatumGetCString(prosrcdatum);
+ /* SD is shared with other PLyProcedures of the function */
+ proc->statics = PLy_procedure_get_statics(procTup, fn_oid, fn_rel);
+
PLy_procedure_compile(proc, procSource);
pfree(procSource);
@@ -331,11 +362,14 @@ PLy_procedure_compile(PLyProcedure *proc, const char *src)
/*
* SD is private preserved data between calls. GD is global data shared by
- * all functions
+ * all functions. The caller may have supplied SD already.
*/
- proc->statics = PyDict_New();
- if (!proc->statics)
- PLy_elog(ERROR, NULL);
+ if (proc->statics == NULL)
+ {
+ proc->statics = PyDict_New();
+ if (!proc->statics)
+ PLy_elog(ERROR, NULL);
+ }
PyDict_SetItemString(proc->globals, "SD", proc->statics);
/*
@@ -445,20 +479,69 @@ PLy_compile_callback(FunctionCallInfo fcinfo,
{
PLyProcedure *proc = (PLyProcedure *) cfunc;
Oid fn_oid = fcinfo->flinfo->fn_oid;
+ Oid fn_rel = InvalidOid;
PLyTrigType is_trigger;
/*
* Derive the trigger type from the call context, matching what
- * plpython3_call_handler dispatches on.
+ * plpython3_call_handler dispatches on. DML triggers get one SD per
+ * relation. The validator has no relation.
*/
if (CALLED_AS_TRIGGER(fcinfo))
+ {
is_trigger = PLPY_TRIGGER;
+ if (!forValidator)
+ fn_rel = RelationGetRelid(((TriggerData *) fcinfo->context)->tg_relation);
+ }
else if (CALLED_AS_EVENT_TRIGGER(fcinfo))
is_trigger = PLPY_EVENT_TRIGGER;
else
is_trigger = PLPY_NOT_TRIGGER;
- PLy_procedure_create(proc, procTup, fn_oid, is_trigger);
+ PLy_procedure_create(proc, procTup, fn_oid, fn_rel, is_trigger);
+}
+
+/*
+ * Get the SD dictionary of a function, as a new reference.
+ */
+static PyObject *
+PLy_procedure_get_statics(HeapTuple procTup, Oid fn_oid, Oid fn_rel)
+{
+ PLyStaticsKey key;
+ PLyStaticsEntry *entry;
+ bool found;
+
+ if (PLy_statics_hash == NULL)
+ {
+ HASHCTL ctl;
+
+ ctl.keysize = sizeof(PLyStaticsKey);
+ ctl.entrysize = sizeof(PLyStaticsEntry);
+ PLy_statics_hash = hash_create("PL/Python SD dictionaries", 32,
+ &ctl, HASH_ELEM | HASH_BLOBS);
+ }
+
+ key.fn_oid = fn_oid;
+ key.fn_rel = fn_rel;
+ entry = hash_search(PLy_statics_hash, &key, HASH_ENTER, &found);
+ if (!found)
+ entry->statics = NULL;
+
+ /* Start over with an empty SD if the function was redefined */
+ if (entry->statics == NULL ||
+ entry->fn_xmin != HeapTupleHeaderGetRawXmin(procTup->t_data) ||
+ !ItemPointerEquals(&entry->fn_tid, &procTup->t_self))
+ {
+ Py_CLEAR(entry->statics);
+ entry->statics = PyDict_New();
+ if (entry->statics == NULL)
+ PLy_elog(ERROR, NULL);
+ entry->fn_xmin = HeapTupleHeaderGetRawXmin(procTup->t_data);
+ entry->fn_tid = procTup->t_self;
+ }
+
+ Py_INCREF(entry->statics);
+ return entry->statics;
}
/*
diff --git a/src/pl/plpython/sql/plpython_global.sql b/src/pl/plpython/sql/plpython_global.sql
index 96d20492861..b57c6fc4e2a 100644
--- a/src/pl/plpython/sql/plpython_global.sql
+++ b/src/pl/plpython/sql/plpython_global.sql
@@ -36,3 +36,18 @@ SELECT static_test();
SELECT static_test();
SELECT global_test_one();
SELECT global_test_two();
+
+-- SD is shared by all calls of a function, whatever the input collation,
+-- and starts over when the function is redefined
+CREATE FUNCTION static_test_coll(t text) RETURNS int4
+AS $$
+SD["n"] = SD.get("n", 0) + 1
+return SD["n"]
+$$ LANGUAGE plpython3u;
+SELECT static_test_coll('a'), static_test_coll('a' COLLATE "C");
+CREATE OR REPLACE FUNCTION static_test_coll(t text) RETURNS int4
+AS $$
+SD["n"] = SD.get("n", 0) + 1
+return SD["n"]
+$$ LANGUAGE plpython3u;
+SELECT static_test_coll('a');
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 553162c0810..c919e89e20a 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2060,6 +2060,8 @@ PLyResultObject
PLySRFState
PLySavedArgs
PLyScalarToOb
+PLyStaticsEntry
+PLyStaticsKey
PLySubtransactionData
PLySubtransactionObject
PLyTransformToOb
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1
2026-10-29 22:25 BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 PG Bug reporting form <noreply@postgresql.org>
2026-11-09 05:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
@ 2026-11-09 18:31 ` Marcos Castedo <marcos.castedo@anachronics.com>
1 sibling, 0 replies; 11+ messages in thread
From: Marcos Castedo @ 2026-11-09 18:31 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Hi,
Thanks for the quick patch. We applied it on top of 19rc1 and the plpython
suite passes. A
trigger function used by two triggers on the same table, and a function
returning record called
with two column definition lists, now share SD as in 18, and the same
trigger function on another
table keeps its own SD, also as in 18.
As for variables set with "global", our code does not use them, so that
does not matter to us.
Just in case it is useful: while testing we noticed that after a
redefinition that is rolled back,
a cache entry created later in the same session gets its own SD. It only
affects the session that
rolled back and we don't run into it in practice, so it's up to you whether
it matters:
CREATE FUNCTION g(t text) RETURNS int AS $$
SD["n"] = SD.get("n", 0) + 1
return SD["n"]
$$ LANGUAGE plpython3u;
SELECT g('a'); -- 1
BEGIN;
CREATE OR REPLACE FUNCTION g(t text) RETURNS int AS $$
SD["n"] = SD.get("n", 0) + 1
return SD["n"]
$$ LANGUAGE plpython3u;
ROLLBACK;
SELECT g('a'), g('a' COLLATE "C"); -- 2 | 1
Also, a minor note on the new regression test: the CREATE OR REPLACE part
returns 1 with or
without the patch, and the trigger case isn't covered.
Thanks again,
Marcos
El lun, 9 nov 2026 a la(s) 2:21 a.m., shihao zhong (zhong950419@gmail.com)
escribió:
> Hi,
>
> Thanks for reporting that.
>
> The attached patch keeps SD in its own hash keyed by function OID and
> trigger relation, as 18 did. So every cache entry of a function shares
> it.
>
> Variables set with "global" in the function body are still split, since
> sharing t
> hose would also mean sharing the call stack state. I left that alone, but
> I
> can look at it if you think it matters.
>
> This is a 19 regression, I'll add it to the open items.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1
2026-10-29 22:25 BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 PG Bug reporting form <noreply@postgresql.org>
2026-11-09 05:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
@ 2026-11-09 19:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-09 20:04 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Marcos Castedo <marcos.castedo@anachronics.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2026-11-09 19:21 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: marcos.castedo@anachronics.com; pgsql-bugs@lists.postgresql.org
shihao zhong <zhong950419@gmail.com> writes:
> The attached patch keeps SD in its own hash keyed by function OID and
> trigger relation, as 18 did. So every cache entry of a function shares
> it.
I'm kind of unconvinced about both this bug report and this patch.
It seems fundamentally schizophrenic, with no real design theory
beyond "let's be bug-compatible with the old behavior". If the
idea of SD is to be shared across all instances of the same function,
why is it OK to have a different SD per trigger relation?
The thing that is primarily worrying me here is what shall we do when
plpython starts supporting polymorphic arguments/results? (Maybe
that's not ever gonna happen, but I think it's just something nobody's
gotten around to.) We will want a separate function cache entry for
each argument type combination seen in a session, for the same reason
as other PLs: so we can cache type-appropriate conversion functions.
With the code as it stands today, that would result in a different SD
for each argument type combination. Maybe that's sane, maybe it is
not. You could argue that f(anyelement) is just an abstract prototype
for a bunch of different concrete functions f(int), f(int8), etc, and
that users would rather those concrete functions not share SDs. If
what was being kept in SD was at all argument-type-specific then you
likely would want that behavior.
So basically, I see four potential contributors to a cache key for SD:
1. Function/procedure OID
2. Trigger relation OID, if a trigger
3. Actual argument types
4. Collation
It's not very apparent to me why #2 is a legitimate cache key but
#3 and #4 are not. Maybe that's the right thing, but is there any
defensible reasoning behind it?
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1
2026-10-29 22:25 BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 PG Bug reporting form <noreply@postgresql.org>
2026-11-09 05:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
2026-11-09 19:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-11-09 20:04 ` Marcos Castedo <marcos.castedo@anachronics.com>
2026-11-09 22:59 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Marcos Castedo @ 2026-11-09 20:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: shihao zhong <zhong950419@gmail.com>; pgsql-bugs@lists.postgresql.org
Hi Tom,
Fair question. I should say first that we don't use non-default collations
anywhere. What breaks
for us is an optional text argument: a call that passes it has the default
collation as input
collation, and a call that omits it has none, so the two calls get
different cache entries and
different SDs. We have a function shaped like this, which stores a value
with one call and reads
it back with another:
CREATE DOMAIN ver AS text;
CREATE FUNCTION installing_version(p_id int4, p_version ver DEFAULT NULL)
RETURNS ver AS $$
if p_version is not None:
SD[p_id] = p_version
return p_version
return SD.get(p_id)
$$ LANGUAGE plpython3u;
SELECT installing_version(1, '9.0.2'); -- 9.0.2
SELECT installing_version(1); -- 19rc1: NULL; with the patch:
9.0.2
Nothing in PL/Python exposes the input collation to the function body, so
the function's author
has no way to see or work around the split. The trigger relation and
argument types differ in
that respect, since the code sees the relation in TD, and with polymorphic
arguments it would get
values of different Python types. Whether that is the right line to draw is
your call; none of our
functions that use SD are triggers or polymorphic, so we don't depend on #2
or #3 either way.
Regards,
Marcos
El lun, 9 nov 2026 a la(s) 4:21 p.m., Tom Lane (tgl@sss.pgh.pa.us) escribió:
> shihao zhong <zhong950419@gmail.com> writes:
> > The attached patch keeps SD in its own hash keyed by function OID and
> > trigger relation, as 18 did. So every cache entry of a function shares
> > it.
>
> I'm kind of unconvinced about both this bug report and this patch.
> It seems fundamentally schizophrenic, with no real design theory
> beyond "let's be bug-compatible with the old behavior". If the
> idea of SD is to be shared across all instances of the same function,
> why is it OK to have a different SD per trigger relation?
>
> The thing that is primarily worrying me here is what shall we do when
> plpython starts supporting polymorphic arguments/results? (Maybe
> that's not ever gonna happen, but I think it's just something nobody's
> gotten around to.) We will want a separate function cache entry for
> each argument type combination seen in a session, for the same reason
> as other PLs: so we can cache type-appropriate conversion functions.
> With the code as it stands today, that would result in a different SD
> for each argument type combination. Maybe that's sane, maybe it is
> not. You could argue that f(anyelement) is just an abstract prototype
> for a bunch of different concrete functions f(int), f(int8), etc, and
> that users would rather those concrete functions not share SDs. If
> what was being kept in SD was at all argument-type-specific then you
> likely would want that behavior.
>
> So basically, I see four potential contributors to a cache key for SD:
>
> 1. Function/procedure OID
> 2. Trigger relation OID, if a trigger
> 3. Actual argument types
> 4. Collation
>
> It's not very apparent to me why #2 is a legitimate cache key but
> #3 and #4 are not. Maybe that's the right thing, but is there any
> defensible reasoning behind it?
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1
2026-10-29 22:25 BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 PG Bug reporting form <noreply@postgresql.org>
2026-11-09 05:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
2026-11-09 19:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
2026-11-09 20:04 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Marcos Castedo <marcos.castedo@anachronics.com>
@ 2026-11-09 22:59 ` shihao zhong <zhong950419@gmail.com>
2026-11-11 16:40 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: shihao zhong @ 2026-11-09 22:59 UTC (permalink / raw)
To: Marcos Castedo <marcos.castedo@anachronics.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
Hi Tom,
I have a proposal. SD stays with the cache entry, and plpython leaves
the input collation out of its cache key.
The reason is that an entry should only split on what changes the
compiled code. The trigger relation does, and so would the actual
argument types once plpython has polymorphic arguments. The collation
does not, since plpython never uses it.
If that sounds right, I'll send a v2.
Thanks,
Shihao
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1
2026-10-29 22:25 BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 PG Bug reporting form <noreply@postgresql.org>
2026-11-09 05:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
2026-11-09 19:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
2026-11-09 20:04 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Marcos Castedo <marcos.castedo@anachronics.com>
2026-11-09 22:59 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
@ 2026-11-11 16:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-11 17:35 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2026-11-11 16:40 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: Marcos Castedo <marcos.castedo@anachronics.com>; pgsql-bugs@lists.postgresql.org
shihao zhong <zhong950419@gmail.com> writes:
> I have a proposal. SD stays with the cache entry, and plpython leaves
> the input collation out of its cache key.
Yeah, I had been wondering if we should go in that direction.
However ...
It occurred to me to consult the fine manual, and I read this:
The global dictionary SD is available to store private data
between repeated calls to the same function.
Full stop. So that's already a lie, and we need to fix it.
However, this would certainly not leave a reader with the
impression that different argument types or collations would
give you different SDs. In fact, it leaves the impression
that different uses of the same trigger function should share
SD. But that's never been true, and I'm sure there are users
out there who depend on the per-trigger-relation behavior.
So I'm now coming around to the conclusion that bug compatibility
with the old behavior is the way to go. Maybe we will rethink
that when/if we get around to supporting polymorphic arguments,
but we don't have to make a decision about that today. If we
split off a separate hashtable as Shihao's v1 patch does, then
we've successfully decoupled the SD behavior from funccache.c's
rules, and we will be free to go either way when the time comes.
So I think Shihao's patch is the right thing (at least approximately,
I haven't read it in detail), and I'll move forward with that.
The manual's text needs to be updated to something like
The global dictionary SD is available to store private data
between repeated calls to the same function within a session.
Each PL/Python function has its own instance of SD, except
that for trigger functions, there is one instance of SD
per function per triggered-on relation.
As a separate matter that is an optimization not a bug fix,
it might be interesting to suppress collation from plpython's
funccache keys, just so we don't get useless redundant cache
entries. But we can leave that for another day.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1
2026-10-29 22:25 BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 PG Bug reporting form <noreply@postgresql.org>
2026-11-09 05:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
2026-11-09 19:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
2026-11-09 20:04 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Marcos Castedo <marcos.castedo@anachronics.com>
2026-11-09 22:59 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
2026-11-11 16:40 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-11-11 17:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-12 04:52 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2026-11-11 17:35 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: Marcos Castedo <marcos.castedo@anachronics.com>; pgsql-bugs@lists.postgresql.org
I wrote:
> So I'm now coming around to the conclusion that bug compatibility
> with the old behavior is the way to go. Maybe we will rethink
> that when/if we get around to supporting polymorphic arguments,
> but we don't have to make a decision about that today. If we
> split off a separate hashtable as Shihao's v1 patch does, then
> we've successfully decoupled the SD behavior from funccache.c's
> rules, and we will be free to go either way when the time comes.
> So I think Shihao's patch is the right thing (at least approximately,
> I haven't read it in detail), and I'll move forward with that.
Okay, so I started looking at the patch and immediately realized
that it's set up to replace SD any time the function is redefined.
I think that's taking bug-compatibility with the old behavior a
bit too far: you certainly would not get that impression from
the manual's description. I propose we drop that behavior and
just run with the function OID and trigger relation as hash keys.
As a minor criticism, I find it odd to make PLy_procedure_compile
sometimes create the SD dictionary and sometimes not. There are
only two callers, so I'm more inclined to require both callers
to handle that.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1
2026-10-29 22:25 BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 PG Bug reporting form <noreply@postgresql.org>
2026-11-09 05:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
2026-11-09 19:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
2026-11-09 20:04 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Marcos Castedo <marcos.castedo@anachronics.com>
2026-11-09 22:59 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
2026-11-11 16:40 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
2026-11-11 17:35 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-11-12 04:52 ` shihao zhong <zhong950419@gmail.com>
2026-11-12 16:48 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: shihao zhong @ 2026-11-12 04:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Marcos Castedo <marcos.castedo@anachronics.com>; pgsql-bugs@lists.postgresql.org
Hi Tom,
Thanks for reviewing and providing feedback. v2 is attached with your
comments
addressed.
Thanks,
Shihao
Attachments:
v2-0001-plpython-Share-the-SD-dictionary-across-all-cache.patch (application/octet-stream, 8.4K) download
From 4ae1dd138f620684bf762ceabf03a80964e6f0e1 Mon Sep 17 00:00:00 2001
From: shihao zhong <zhong950419@gmail.com>
Date: Sun, 8 Nov 2026 23:43:22 -0500
Subject: [PATCH v2] plpython: Share the SD dictionary across all cache entries
of a function.
Since commit 9bcb8a694b3 moved PL/Python onto funccache.c, each cache
entry gets its own SD dictionary. funccache.c builds separate entries
for the same function when the input collation differs, when a trigger
function is used by several triggers, and when a function returning
record is called with different column definition lists. So values
stored in SD by one call could be missing in the next one, which
contradicts the documentation and the behavior up to v18.
Keep the SD dictionaries in a separate hash table, keyed by function OID
and, for DML triggers, the relation, as the old PL/Python cache did.
Unlike before, SD now survives CREATE OR REPLACE FUNCTION, which matches
the documentation better. Both callers of PLy_procedure_compile now
supply the SD dictionary.
Reported-by: Marcos Castedo <marcos.castedo@anachronics.com>
Reviewed-by: Marcos Castedo <marcos.castedo@anachronics.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/19816-921b5cbc3ee63146@postgresql.org
Backpatch-through: 19
---
src/pl/plpython/expected/plpython_global.out | 24 ++++++
src/pl/plpython/plpy_main.c | 5 ++
src/pl/plpython/plpy_procedure.c | 81 ++++++++++++++++++--
src/pl/plpython/sql/plpython_global.sql | 15 ++++
src/tools/pgindent/typedefs.list | 2 +
5 files changed, 121 insertions(+), 6 deletions(-)
diff --git a/src/pl/plpython/expected/plpython_global.out b/src/pl/plpython/expected/plpython_global.out
index a4cfb1483f9..3c4f2a58b78 100644
--- a/src/pl/plpython/expected/plpython_global.out
+++ b/src/pl/plpython/expected/plpython_global.out
@@ -50,3 +50,27 @@ SELECT global_test_two();
SD: set by global_test_two, GD: set by global_test_one
(1 row)
+-- SD is shared by all calls of a function, whatever the input collation,
+-- and survives CREATE OR REPLACE
+CREATE FUNCTION static_test_coll(t text) RETURNS int4
+AS $$
+SD["n"] = SD.get("n", 0) + 1
+return SD["n"]
+$$ LANGUAGE plpython3u;
+SELECT static_test_coll('a'), static_test_coll('a' COLLATE "C");
+ static_test_coll | static_test_coll
+------------------+------------------
+ 1 | 2
+(1 row)
+
+CREATE OR REPLACE FUNCTION static_test_coll(t text) RETURNS int4
+AS $$
+SD["n"] = SD.get("n", 0) + 1
+return SD["n"]
+$$ LANGUAGE plpython3u;
+SELECT static_test_coll('a');
+ static_test_coll
+------------------
+ 3
+(1 row)
+
diff --git a/src/pl/plpython/plpy_main.c b/src/pl/plpython/plpy_main.c
index ce0e7044d9d..e305f2a848a 100644
--- a/src/pl/plpython/plpy_main.c
+++ b/src/pl/plpython/plpy_main.c
@@ -359,6 +359,11 @@ plpython3_inline_handler(PG_FUNCTION_ARGS)
plerrcontext.previous = error_context_stack;
error_context_stack = &plerrcontext;
+ /* An inline block has its own SD, used for this call only */
+ proc.statics = PyDict_New();
+ if (!proc.statics)
+ PLy_elog(ERROR, NULL);
+
PLy_procedure_compile(&proc, codeblock->source_text);
exec_ctx->curr_proc = &proc;
PLy_exec_function(fake_fcinfo, &pcache);
diff --git a/src/pl/plpython/plpy_procedure.c b/src/pl/plpython/plpy_procedure.c
index ce8b1e099e9..96d50b6dada 100644
--- a/src/pl/plpython/plpy_procedure.c
+++ b/src/pl/plpython/plpy_procedure.c
@@ -18,12 +18,35 @@
#include "plpy_util.h"
#include "utils/builtins.h"
#include "utils/funccache.h"
+#include "utils/hsearch.h"
+#include "utils/rel.h"
#include "utils/syscache.h"
+/*
+ * SD dictionaries, one per function (and per relation, for DML triggers).
+ * funccache.c may build several PLyProcedures for one function, for example
+ * one per input collation, and they must all see the same SD.
+ */
+typedef struct PLyStaticsKey
+{
+ Oid fn_oid; /* function OID */
+ Oid fn_rel; /* triggered-on relation or InvalidOid */
+} PLyStaticsKey;
+
+typedef struct PLyStaticsEntry
+{
+ PLyStaticsKey key; /* hash key, must be first */
+ PyObject *statics; /* the SD dictionary */
+} PLyStaticsEntry;
+
+static HTAB *PLy_statics_hash = NULL;
+
static void PLy_procedure_create(PLyProcedure *proc,
HeapTuple procTup,
Oid fn_oid,
+ Oid fn_rel,
PLyTrigType is_trigger);
+static PyObject *PLy_procedure_get_statics(Oid fn_oid, Oid fn_rel);
static char *PLy_procedure_munge_source(const char *name, const char *src);
static void PLy_compile_callback(FunctionCallInfo fcinfo,
HeapTuple procTup,
@@ -145,6 +168,7 @@ static void
PLy_procedure_create(PLyProcedure *proc,
HeapTuple procTup,
Oid fn_oid,
+ Oid fn_rel,
PLyTrigType is_trigger)
{
char procName[NAMEDATALEN + 256];
@@ -302,6 +326,9 @@ PLy_procedure_create(PLyProcedure *proc,
Anum_pg_proc_prosrc);
procSource = TextDatumGetCString(prosrcdatum);
+ /* SD is shared with other PLyProcedures of the function */
+ proc->statics = PLy_procedure_get_statics(fn_oid, fn_rel);
+
PLy_procedure_compile(proc, procSource);
pfree(procSource);
@@ -331,11 +358,9 @@ PLy_procedure_compile(PLyProcedure *proc, const char *src)
/*
* SD is private preserved data between calls. GD is global data shared by
- * all functions
+ * all functions. The caller supplies SD.
*/
- proc->statics = PyDict_New();
- if (!proc->statics)
- PLy_elog(ERROR, NULL);
+ Assert(proc->statics != NULL);
PyDict_SetItemString(proc->globals, "SD", proc->statics);
/*
@@ -445,20 +470,64 @@ PLy_compile_callback(FunctionCallInfo fcinfo,
{
PLyProcedure *proc = (PLyProcedure *) cfunc;
Oid fn_oid = fcinfo->flinfo->fn_oid;
+ Oid fn_rel = InvalidOid;
PLyTrigType is_trigger;
/*
* Derive the trigger type from the call context, matching what
- * plpython3_call_handler dispatches on.
+ * plpython3_call_handler dispatches on. DML triggers get one SD per
+ * relation. The validator has no relation.
*/
if (CALLED_AS_TRIGGER(fcinfo))
+ {
is_trigger = PLPY_TRIGGER;
+ if (!forValidator)
+ fn_rel = RelationGetRelid(((TriggerData *) fcinfo->context)->tg_relation);
+ }
else if (CALLED_AS_EVENT_TRIGGER(fcinfo))
is_trigger = PLPY_EVENT_TRIGGER;
else
is_trigger = PLPY_NOT_TRIGGER;
- PLy_procedure_create(proc, procTup, fn_oid, is_trigger);
+ PLy_procedure_create(proc, procTup, fn_oid, fn_rel, is_trigger);
+}
+
+/*
+ * Get the SD dictionary of a function, as a new reference.
+ */
+static PyObject *
+PLy_procedure_get_statics(Oid fn_oid, Oid fn_rel)
+{
+ PLyStaticsKey key;
+ PLyStaticsEntry *entry;
+ bool found;
+
+ if (PLy_statics_hash == NULL)
+ {
+ HASHCTL ctl;
+
+ ctl.keysize = sizeof(PLyStaticsKey);
+ ctl.entrysize = sizeof(PLyStaticsEntry);
+ PLy_statics_hash = hash_create("PL/Python SD dictionaries", 32,
+ &ctl, HASH_ELEM | HASH_BLOBS);
+ }
+
+ key.fn_oid = fn_oid;
+ key.fn_rel = fn_rel;
+ entry = hash_search(PLy_statics_hash, &key, HASH_ENTER, &found);
+ if (!found)
+ entry->statics = NULL;
+
+ /* A failed PyDict_New leaves the entry empty, so retry it here */
+ if (entry->statics == NULL)
+ {
+ entry->statics = PyDict_New();
+ if (entry->statics == NULL)
+ PLy_elog(ERROR, NULL);
+ }
+
+ Py_INCREF(entry->statics);
+ return entry->statics;
}
/*
diff --git a/src/pl/plpython/sql/plpython_global.sql b/src/pl/plpython/sql/plpython_global.sql
index 96d20492861..b2a4fe10969 100644
--- a/src/pl/plpython/sql/plpython_global.sql
+++ b/src/pl/plpython/sql/plpython_global.sql
@@ -36,3 +36,18 @@ SELECT static_test();
SELECT static_test();
SELECT global_test_one();
SELECT global_test_two();
+
+-- SD is shared by all calls of a function, whatever the input collation,
+-- and survives CREATE OR REPLACE
+CREATE FUNCTION static_test_coll(t text) RETURNS int4
+AS $$
+SD["n"] = SD.get("n", 0) + 1
+return SD["n"]
+$$ LANGUAGE plpython3u;
+SELECT static_test_coll('a'), static_test_coll('a' COLLATE "C");
+CREATE OR REPLACE FUNCTION static_test_coll(t text) RETURNS int4
+AS $$
+SD["n"] = SD.get("n", 0) + 1
+return SD["n"]
+$$ LANGUAGE plpython3u;
+SELECT static_test_coll('a');
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 8f65cee41b1..f10eb469266 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2063,6 +2063,8 @@ PLyResultObject
PLySRFState
PLySavedArgs
PLyScalarToOb
+PLyStaticsEntry
+PLyStaticsKey
PLySubtransactionData
PLySubtransactionObject
PLyTransformToOb
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1
2026-10-29 22:25 BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 PG Bug reporting form <noreply@postgresql.org>
2026-11-09 05:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
2026-11-09 19:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
2026-11-09 20:04 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Marcos Castedo <marcos.castedo@anachronics.com>
2026-11-09 22:59 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
2026-11-11 16:40 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
2026-11-11 17:35 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
2026-11-12 04:52 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
@ 2026-11-12 16:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-15 05:02 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2026-11-12 16:48 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: Marcos Castedo <marcos.castedo@anachronics.com>; pgsql-bugs@lists.postgresql.org
shihao zhong <zhong950419@gmail.com> writes:
> Thanks for reviewing and providing feedback. v2 is attached with your
> comments addressed.
v2 looks good, pushed with very minor editing.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1
2026-10-29 22:25 BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 PG Bug reporting form <noreply@postgresql.org>
2026-11-09 05:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
2026-11-09 19:21 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
2026-11-09 20:04 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Marcos Castedo <marcos.castedo@anachronics.com>
2026-11-09 22:59 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
2026-11-11 16:40 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
2026-11-11 17:35 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
2026-11-12 04:52 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 shihao zhong <zhong950419@gmail.com>
2026-11-12 16:48 ` Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-11-15 05:02 ` shihao zhong <zhong950419@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: shihao zhong @ 2026-11-15 05:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Marcos Castedo <marcos.castedo@anachronics.com>; pgsql-bugs@lists.postgresql.org
Hi Tom,
Thanks for pushing v2.
> As a separate matter that is an optimization not a bug fix,
> it might be interesting to suppress collation from plpython's
> funccache keys, just so we don't get useless redundant cache
> entries. But we can leave that for another day.
Let's do it today, I do not like to leave things open and forget it
eventually.
That should be master only
Thanks,
Shihao
Attachments:
v1-0001-plpython-leave-input-collation-out-of-the-function-c.patch (application/octet-stream, 8.0K) download
From eaa08a98efc3103d49172c89eef705c713f5e230 Mon Sep 17 00:00:00 2001
From: shihao zhong <zhong950419@gmail.com>
Date: Sat, 14 Nov 2026 23:17:17 -0500
Subject: [PATCH] plpython: leave input collation out of the function cache
key.
Nothing plpython compiles uses the collation, so keying on it only
built duplicate cache entries.
Discussion: https://postgr.es/m/3245516.1794415242@sss.pgh.pa.us
---
src/backend/executor/functions.c | 1 +
src/backend/utils/cache/funccache.c | 15 +++++++++++----
src/include/utils/funccache.h | 8 +++++---
src/pl/plpgsql/src/pl_comp.c | 1 +
src/pl/plpython/expected/plpython_global.out | 14 ++++++++++++++
src/pl/plpython/plpy_procedure.c | 7 +++++--
src/pl/plpython/sql/plpython_global.sql | 11 +++++++++++
7 files changed, 48 insertions(+), 9 deletions(-)
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 8fec79a2f76..5eda27d7d25 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -605,6 +605,7 @@ init_sql_fcache(FunctionCallInfo fcinfo, bool lazyEvalOK)
sql_delete_callback,
sizeof(SQLFunctionHashEntry),
true,
+ true,
false);
/*
diff --git a/src/backend/utils/cache/funccache.c b/src/backend/utils/cache/funccache.c
index 7b3422a07bf..82a7c960159 100644
--- a/src/backend/utils/cache/funccache.c
+++ b/src/backend/utils/cache/funccache.c
@@ -252,6 +252,7 @@ compute_function_hashkey(FunctionCallInfo fcinfo,
CachedFunctionHashKey *hashkey,
Size cacheEntrySize,
bool includeResultType,
+ bool includeInputCollation,
bool forValidator)
{
/* Make sure pad bytes within fixed part of the struct are zero */
@@ -286,8 +287,9 @@ compute_function_hashkey(FunctionCallInfo fcinfo,
hashkey->trigOid = trigdata->tg_trigger->tgoid;
}
- /* get input collation, if known */
- hashkey->inputCollation = fcinfo->fncollation;
+ /* get input collation, if known and wanted */
+ if (includeInputCollation)
+ hashkey->inputCollation = fcinfo->fncollation;
/*
* We include only input arguments in the hash key, since output argument
@@ -487,6 +489,10 @@ delete_function(CachedFunction *func)
* If includeResultType is true and the function returns composite,
* include the actual result descriptor in the cache lookup key.
*
+ * If includeInputCollation is true, include the call's input collation in
+ * the cache lookup key. Languages whose compiled form doesn't depend on the
+ * collation should pass false, to avoid building redundant cache entries.
+ *
* If forValidator is true, we're only compiling for validation purposes,
* and so some checks are skipped. In this case the passed fcinfo need
* only provide the function OID and indications of whether the function
@@ -507,6 +513,7 @@ cached_function_compile(FunctionCallInfo fcinfo,
CachedFunctionDeleteCallback dcallback,
Size cacheEntrySize,
bool includeResultType,
+ bool includeInputCollation,
bool forValidator)
{
Oid funcOid = fcinfo->flinfo->fn_oid;
@@ -535,7 +542,7 @@ recheck:
/* Compute hashkey using function signature and actual arg types */
compute_function_hashkey(fcinfo, procStruct, &hashkey,
cacheEntrySize, includeResultType,
- forValidator);
+ includeInputCollation, forValidator);
hashkey_valid = true;
/* And do the lookup */
@@ -591,7 +598,7 @@ recheck:
if (!hashkey_valid)
compute_function_hashkey(fcinfo, procStruct, &hashkey,
cacheEntrySize, includeResultType,
- forValidator);
+ includeInputCollation, forValidator);
/*
* Create the new function struct, if not done already. The function
diff --git a/src/include/utils/funccache.h b/src/include/utils/funccache.h
index ee8f03ee997..38c4b251f7d 100644
--- a/src/include/utils/funccache.h
+++ b/src/include/utils/funccache.h
@@ -75,9 +75,10 @@ typedef struct CachedFunctionHashKey
Oid trigOid;
/*
- * We must include the input collation as part of the hash key too,
- * because we have to generate different plans (with different Param
- * collations) for different collation settings.
+ * Usually we must include the input collation as part of the hash key
+ * too, because we have to generate different plans (with different Param
+ * collations) for different collation settings. Languages whose compiled
+ * form doesn't depend on the collation can ask to leave this zero.
*/
Oid inputCollation;
@@ -123,6 +124,7 @@ extern CachedFunction *cached_function_compile(FunctionCallInfo fcinfo,
CachedFunctionDeleteCallback dcallback,
Size cacheEntrySize,
bool includeResultType,
+ bool includeInputCollation,
bool forValidator);
extern void cfunc_resolve_polymorphic_argtypes(int numargs,
Oid *argtypes,
diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index 180a20f868a..19e3bb538da 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -122,6 +122,7 @@ plpgsql_compile(FunctionCallInfo fcinfo, bool forValidator)
plpgsql_delete_callback,
sizeof(PLpgSQL_function),
false,
+ true,
forValidator);
/*
diff --git a/src/pl/plpython/expected/plpython_global.out b/src/pl/plpython/expected/plpython_global.out
index a3f33f9cba0..baf51f48869 100644
--- a/src/pl/plpython/expected/plpython_global.out
+++ b/src/pl/plpython/expected/plpython_global.out
@@ -74,3 +74,17 @@ SELECT static_test_coll('a');
3
(1 row)
+-- Python global variables live in the compiled function, so this checks
+-- that we compile only once no matter what the input collation is
+CREATE FUNCTION global_var_coll(t text) RETURNS int4
+AS $$
+global n
+n = globals().get("n", 0) + 1
+return n
+$$ LANGUAGE plpython3u;
+SELECT global_var_coll('a'), global_var_coll('a' COLLATE "C");
+ global_var_coll | global_var_coll
+-----------------+-----------------
+ 1 | 2
+(1 row)
+
diff --git a/src/pl/plpython/plpy_procedure.c b/src/pl/plpython/plpy_procedure.c
index 76fee7974a2..15681df03a5 100644
--- a/src/pl/plpython/plpy_procedure.c
+++ b/src/pl/plpython/plpy_procedure.c
@@ -24,7 +24,7 @@
/*
* SD dictionaries, one per function (and per relation, for DML triggers).
* funccache.c may build several PLyProcedures for one function, for example
- * one per input collation, and they must all see the same SD.
+ * one per trigger or per result rowtype, and they must all see the same SD.
*/
typedef struct PLyStaticsKey
{
@@ -129,7 +129,9 @@ PLy_procedure_get(FunctionCallInfo fcinfo, bool forValidator)
* functions, make the hash key depend on the result of
* get_call_result_type() when that's composite, so that we can safely
* assume that we'll build a new hash entry if the composite rowtype
- * changes.
+ * changes. Nothing we build depends on the input collation, so leave
+ * that out of the hash key; otherwise we'd compile the function again for
+ * each collation it's called with.
*/
proc = (PLyProcedure *)
cached_function_compile(fcinfo,
@@ -138,6 +140,7 @@ PLy_procedure_get(FunctionCallInfo fcinfo, bool forValidator)
PLy_delete_callback,
sizeof(PLyProcedure),
true,
+ false,
forValidator);
/*
diff --git a/src/pl/plpython/sql/plpython_global.sql b/src/pl/plpython/sql/plpython_global.sql
index 6f59600b455..47b11defc77 100644
--- a/src/pl/plpython/sql/plpython_global.sql
+++ b/src/pl/plpython/sql/plpython_global.sql
@@ -54,3 +54,14 @@ return SD["n"]
$$ LANGUAGE plpython3u;
SELECT static_test_coll('a');
+
+-- Python global variables live in the compiled function, so this checks
+-- that we compile only once no matter what the input collation is
+CREATE FUNCTION global_var_coll(t text) RETURNS int4
+AS $$
+global n
+n = globals().get("n", 0) + 1
+return n
+$$ LANGUAGE plpython3u;
+
+SELECT global_var_coll('a'), global_var_coll('a' COLLATE "C");
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2026-11-15 05:02 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-29 22:25 BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1 PG Bug reporting form <noreply@postgresql.org>
2026-11-09 05:21 ` shihao zhong <zhong950419@gmail.com>
2026-11-09 18:31 ` Marcos Castedo <marcos.castedo@anachronics.com>
2026-11-09 19:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-09 20:04 ` Marcos Castedo <marcos.castedo@anachronics.com>
2026-11-09 22:59 ` shihao zhong <zhong950419@gmail.com>
2026-11-11 16:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-11 17:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-12 04:52 ` shihao zhong <zhong950419@gmail.com>
2026-11-12 16:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-15 05:02 ` shihao zhong <zhong950419@gmail.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