BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1

Started by PG Bug reporting form24 days ago11 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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.

#2shihao zhong
zhong950419@gmail.com
In reply to: PG Bug reporting form (#1)
Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1

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:

t254467_2
v1-0001-plpython-Share-the-SD-dictionary-across-all-cache.patchapplication/octet-stream; name=v1-0001-plpython-Share-the-SD-dictionary-across-all-cache.patchDownload+130-7
#3Marcos Castedo
marcos.castedo@anachronics.com
In reply to: shihao zhong (#2)
Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1

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ó:

Show quoted text

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.

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: shihao zhong (#2)
Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1

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

#5Marcos Castedo
marcos.castedo@anachronics.com
In reply to: Tom Lane (#4)
Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1

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ó:

Show quoted text

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

#6shihao zhong
zhong950419@gmail.com
In reply to: Marcos Castedo (#5)
Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1

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

#7Tom Lane
tgl@sss.pgh.pa.us
In reply to: shihao zhong (#6)
Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1

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

#8Tom Lane
tgl@sss.pgh.pa.us
In reply to: Tom Lane (#7)
Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1

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

#9shihao zhong
zhong950419@gmail.com
In reply to: Tom Lane (#8)
Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1

Hi Tom,

Thanks for reviewing and providing feedback. v2 is attached with your
comments
addressed.

Thanks,
Shihao

Attachments:

t254467_9
v2-0001-plpython-Share-the-SD-dictionary-across-all-cache.patchapplication/octet-stream; name=v2-0001-plpython-Share-the-SD-dictionary-across-all-cache.patchDownload+121-7
#10Tom Lane
tgl@sss.pgh.pa.us
In reply to: shihao zhong (#9)
Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1

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

#11shihao zhong
zhong950419@gmail.com
In reply to: Tom Lane (#10)
Re: BUG #19816: PL/Python SD dictionary is split by input collation in 19rc1

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:

t254467_11
v1-0001-plpython-leave-input-collation-out-of-the-function-c.patchapplication/octet-stream; name=v1-0001-plpython-leave-input-collation-out-of-the-function-c.patchDownload+48-10