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

> david.g.johnston@gmail.com wrote:
>
>> bryn@yugabyte.com wrote:
>>
>>> david.g.johnston@gmail.com wrote:
>>>
>>>> bryn@yugabyte.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-sessionsbefore 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
expectedthe 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
Iposted, using %type? Why is the meaning of %type frozen at "create" time while (as it seems) %rowtype is re-evaluated
atruntime—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
isfirst 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
intendedis 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
andN2 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
representationof 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/pgSQLprogram 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
todo this. And this means quiescing use of the application and then re-starting it with new behavior. Is re-starting
theconnection pool before opening up the new app for use so expensive that it's worth trying to reason when it might be
safeto avoid this re-start?