Subject: Re: SQLFunctionCache and generic plans

From: Tom Lane <[email-masked]>
Date: Fri, 04 Apr 2025 17:47:34 -0400

Alexander Lakhin <[email-masked]> writes:
> I've stumbled upon another defect introduced with 0dca5d68d:
> CREATE FUNCTION f(VARIADIC ANYARRAY) RETURNS ANYELEMENT AS $$ SELECT x FROM generate_series(1,1) g(i) $$ LANGUAGE SQL 
> IMMUTABLE;
> SELECT f(1);
> SELECT f(1);

Hmm, I see

regression=# CREATE FUNCTION f(VARIADIC ANYARRAY) RETURNS ANYELEMENT AS $$ SELECT x FROM generate_series(1,1) g(i) $$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION
regression=# SELECT f(1);
ERROR:  column "x" does not exist
LINE 1:  SELECT x FROM generate_series(1,1) g(i) 
                ^
QUERY:   SELECT x FROM generate_series(1,1) g(i) 
CONTEXT:  SQL function "f" statement 1
regression=# SELECT f(1);
ERROR:  unrecognized node type: 2139062143
CONTEXT:  SQL function "f" statement 1

Did you intend the typo?  The "unrecognized node type" does indicate
a problem, but your message doesn't seem to indicate that you're
expecting a syntax error.

			regards, tom lane

---

From: Alexander Lakhin <[email-masked]>
Date: Sat, 5 Apr 2025 01:00:01 +0300

05.04.2025 00:47, Tom Lane wrote:
> Alexander Lakhin<[email-masked]> writes:

Yes, the typo is intended. With Valgrind, I get the "column does not exist"
error on the first call and the Valgrind complaint on the second one.

Best regards,
Alexander Lakhin
Neon (https://neon.tech)

---

From: Tom Lane <[email-masked]>
Date: Wed, 09 Apr 2025 02:19:22 -0400

In the department of no-good-deed-goes-unpunished ...

I noticed that avocet and trilobite (two of our CLOBBER_CACHE_ALWAYS
animals) have started to fail on the deadlock-parallel isolation
test, with symptoms that look like they're timing out.  Poking at
it here with a somewhat faster machine (a Mac M4), I see that under
debug_discard_caches=1 that test went from

ok 27        - deadlock-parallel                       26362 ms

immediately before commit 0dca5d68d to

ok 27        - deadlock-parallel                      267869 ms

immediately after.  This is evidently because the test involves a lot
of evaluations of an intentionally-not-inline-able SQL function.
Previously we cached the plans for that function for the life of the
outer query, but now they're getting clobbered and rebuilt each time.

This is not wrong; the wrong behavior was hanging onto a
potentially-obsolete plan.  But it's pretty unfortunate for the
runtime of this test under debug_discard_caches=1.

The simplest fix is to force that test to use debug_discard_caches=0,
but I don't love that answer.  Anybody have a better idea?

It looks like the runtime of the infinite_recurse test on these
animals has taken a hit too for the same reason, and there may be
other places.

			regards, tom lane

---

From: Tom Lane <[email-masked]>
Date: Wed, 09 Apr 2025 17:01:56 -0400

I wrote:
> test, with symptoms that look like they're timing out.
> ...

I thought of a better way: we can bypass the need to use a non-inlined
SQL function by declaring internal-language aliases for the
auxiliary-lock functions that are falsely marked parallel-safe.
It's a little bit ugly (see [1]) but I think it beats not running
this test under debug_discard_caches at all.

			regards, tom lane

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=837cc73af29cd9c63515a6f2a36f54dd703a3a3f