From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Jan Behrens <jbe-mlist@magnetkern.de>,
        "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: search_path for PL/pgSQL functions partially cached?
In-reply-to: <CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
References: <20241227205025.1d059f72c7c08d23c9648c26@magnetkern.de> <CAKFQuwb4hgHH=Z6cx5Hh_qc10TCYMb1QVfP3099X1Psmyw0r3Q@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Fri, 27 Dec 2024 13:26:28 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2547820.1735333397.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Fri, 27 Dec 2024 16:03:17 -0500
Message-ID: <2547821.1735333397@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2547821.1735333397%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> It is what it is - and if one is not careful one can end up writing
> hard-to-understand and possibly buggy code due to the various execution
> environments and caches involved.

Yeah, I don't see this changing.  The actual answer is that we have
search_path-aware caching of expressions and query plans within a
plpgsql function, which is why the call to foo() reacts to the current
search path.  But the types of plpgsql variables are only looked up
on the first use (within a session).  Perhaps we ought to work harder
on that, but it seems like a lot of overhead to add for something that
will benefit next to nobody.

> I’ve never really understood why “%TYPE’ exists…

Compatibility with Oracle, I imagine.  I agree it's a bizarre feature.
But you could get the same behavior without %TYPE, just by referencing
some other type that has different declarations in different schemas.

> Add qualification or attach a “set search_path” clause to “create
> function”.  Code stored in the server should not rely on the session
> search_path.

Yeah, adding "set search_path" is recommendable if you don't want to
think hard about this stuff.

			regards, tom lane