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

> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com wrote:
>>
>>> david(dot)g(dot)johnston(at)gmail(dot)com wrote:
>>>
>>>> bryn(at)yugabyte(dot)com wrote:
>>>>
>>>> Regard a DDL on any object that an application uses as unsafe while the app is in use. You must terminate all client-sessions before doing such a DDL and re-start them only when all such DDLs are done successfully.
>>>
>>> No. If you simply "ADD COLUMN" to an existing table the "terminate all client-sessions" action is excessive, IMO.
>>
>> I tried another test. The results surprised me:
>>
>> create table s.t(k int primary key, c1 text);
>> insert into s.t(k, c1) values (1, 'cat');
>>
>> create function s.f(k_in in int)
>> returns text
>> security definer
>> set search_path = pg_catalog, pg_temp
>> language plpgsql
>> as $body$
>> declare
>> r s.t%rowtype;
>> begin
>> select * from s.t into strict r where t.k = k_in;
>> return r::text;
>> end;
>> $body$;
>>
>> select s.f(1);
>>
>> This is the result (no surprises yet):
>>
>> (1,cat)
>>
>> Now, still in the same session:
>>
>> alter table s.t add c2 text;
>> update s.t set c2 = 'dog' where k = 1;
>> select s.f(1);
>>
>> This is the new result. It surprised me:
>>
>> (1,cat,dog)
>>
>> I had expected that %rowtype would be translated, and frozen, at "create" time into the columns "k" and "c1". So I expected the second execution of "s.f()" give some flavor of wrong answer.
>>
>> Where can I read what I need in order to understand the difference here, using %rowtype, and in the first test that I posted, using %type? Why is the meaning of %type frozen at "create" time while (as it seems) %rowtype is re-evaluated at runtime—presumably on every execution of the subprogram?
>>
>> I discovered a new surprise in this general space with this test:
>>
>> create function s.g()
>> returns text
>> security definer
>> set search_path = pg_catalog, pg_temp
>> language plpgsql
>> as $body$
>> declare
>> c1 text;
>> c2 text;
>> begin
>> select 'cat', 'dog', 'mouse' into c1, c2;
>> return c1||' '||c2;
>> end;
>> $body$;
>>
>> select s.g();
>>
>> It runs without error and shows this:
>>
>> cat dog
>>
>> Why don't I get a runtime error telling me that I have more "select list" items than "into" targets?
>
> You may want to send this to the mailing list too, for posterity.

Oops… I somehow slipped up and replied only to David. Here it is, now, for the archive.

I also slipped up by saying « frozen, at "create" time ». Thanks for pointing this out, David. I did indeed mean to write « frozen, in a particular session and for the remainder of that session's duration, when the PL/pgSQL subprogram is first executed. »

I read the replies from David and Tom. But I must confess that I can't work out what the current consensus on what's intended is w.r.t. load-time versus execution-time response to a change definition of %type and %rowtype.

(Never mind yet whether, or to what extent, this is currently documented.)

I believe that I'm hearing that there is thought to be a genuine bug, orthogonal to the main thing that I was asking about, thus: an attempt to select N1 items into N2 targets, where N1 and N2 differ, should cause a run-time error. (N1 and N2 might differ, as I demonstrated, simply because of a programmer-authored error. Or they might differ now, in some session, where they earlier didn't, because of changes in the environment with which this session's in-memory representation of the PL/pgSQL program has lost currency).

Returning to David's earlier comment, thus:

> If you simply "ADD COLUMN" to an existing table the "terminate all client-sessions" action is excessive, IMO.

Why not err on the side of caution and (I trust) guaranteed currency of each session's in-memory representation of a PL/pgSQL program with the environment in which it executes?

After all, you add a column in order to use it. And this means that at the very least client-side code must be changed to do this. And this means quiescing use of the application and then re-starting it with new behavior. Is re-starting the connection pool before opening up the new app for use so expensive that it's worth trying to reason when it might be safe to avoid this re-start?