Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

st 8. 3. 2023 v 22:29 odesílatel Bryn Llewellyn <bryn(at)yugabyte(dot)com> napsal:

> tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:
>
> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
> So I found where this difference in behavior is at least explicitly noted:
>
> /*
> * If it's a named composite type (or domain over one), find the typcache
> * entry and record the current tupdesc ID, so we can detect changes
> * (including drops). We don't currently support on-the-fly replacement
> * of non-composite types, else we might want to do this for them too.
> */
>
>
> I'm not quite sure that that's related, really. That code is
> concerned with detecting changes to an already-identified type (that is,
> type OID NNN has different details now than it did before). It seemed to me
> that Bryn's question was more about reacting to cases where a given string
> of source code would resolve to a different type OID than it did a moment
> ago. We don't have a great story on that, I'll agree. You can get into that
> sort of problem without anywhere near the amount of complexity embodied in
> this example --- for instance, I'm pretty sure we don't re-parse type
> references just because somebody else executed an ALTER TYPE RENAME
> somewhere.
>
>
> I tried a new test, inspired by what Tom wrote:
>
> create table s.t(k int primary key, c1 int, c2 int, c3 int);
> insert into s.t(k, c1, c2, c3) values(1, 17, 42, 57);
> create type s.x as (c1 int, c2 int, c3 int);
>
> create function s.f()
> returns text
> security definer
> set search_path = pg_catalog, pg_temp
> language plpgsql
> as $body$
> declare
> r s.x;
> begin
> r := (select (a.c1, a.c2, a.c3)::s.x from s.t as a where a.k = 1);
> return r::text;
> end;
> $body$;
>
> select s.f();
>
> It produced the expected result:
>
> (17,42,57)
>
> Then I did this (still in the same session):
>
> alter type s.x drop attribute c3 cascade;
> select s.f();
>
> It produced this new result (with no reported error):
>
> (17,42)
>
> Then I reconnected as the same user to the same database to force a fresh
> analysis of the the source code of "s.f()" on it's first execution:
>
> \c - :the_user
> select s.f();
>
> Now I got a 42846 error, "cannot cast type record to s.x", with the
> detail "Input has too many columns".
>
> Here's my conclusion. It's for the scenario that you have PL/pgSQL
> subprograms among the objects that your client-side app uses. It's rather
> obvious.
>
> (1) If you do any DDLs that affect any of the objects that an application
> uses, then you should exit all of the client sessions (presumably this
> means stopping the connection pool for most apps) before you do the
> patching. The reasoning is simple. A few spot tests show how things can go
> wrong if you don't do this. And there's no doc to tell you what, if any,
> DDLs you might safely do without stopping all but the session(s) that do
> the patching.
>
> (2) You have to take full responsibility for the impact analysis so that
> you can make all the changes that are needed to take you from the pre-patch
> mutually consistent state of all objects to the new post-patch mutually
> consistent state during the window when only the session(s) doing the
> patching are active. Native PG doesn't provide much metadata or tooling to
> help you here. You need your own reliable humanly written external doc of
> your system.
>
> (3) The same general thinking extends to client-side code. Carefully
> specified and executed testing, using a dedicated and realistic test env,
> is critical.
>

PL/pgSQL currently doesn't try to synchronize the structure of row
variables. Row variables hold description of structure until end of
session. Now, plpgsql code is "recompiled" after change of pg_proc record.
Theoretically there can be used the same mechanism like plan cache does,
and recompilation can be forced after change of related data types. Or only
declaration of row variables can be recompiled. This is just about the
possibility of invalidating some local cache.

Now, with using record type, the code should be tolerant against these
changes. I think this behaviour will be identical - and plpgsql_check can
work with the record's type almost well.

Regards

Pavel