Обсуждение: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

> david.g.johnston@gmail.com wrote:
>
>> bryn@yugabyte.com wrote:
>>
>> create table s.t(k serial primary key, v text);
>> create function s.f(k_in in int)
>> select s.f(1);
>
> text, function is now compiled with that type resolution fixed.
> I think mostly attributable to:
>
> > The mutable nature of record variables presents another problem in this connection. When fields of a record
variableare used in expressions or statements, the data types of the fields must not change from one call of the
functionto the next, since each expression will be analyzed using the data type that is present when the expression is
firstreached. 
>
> Though possibly… variable declarations [are] considered structural:
>
> > The instruction tree fully translates the PL/pgSQL statement structure,
>
>> drop table s.t cascade;
>> create table s.t(k serial primary key, v varchar(10));
>> select s.f(1);
>
> still text as the compiled artifact is re-executed
>
>> \c d0 d0$u0
>> select s.f(1);
>
> now varchar as the function is recompiled during its first use in this session.
>
> Restarting everything is an approach to dealing with uncertainty. This particular use case, though, isn't one that
I'dbe overly worried about. Actually making DDL changes of this nature should be rare if not forbidden. Once live
on-the-flycolumn type changes just shouldn't happen so having a plan in place that accommodates them is adding cost for
noreal benefit. 

Thanks. I believe that you and I agree on the proper practice, paraphrased here slightly w.r.t. what I wrote in my
point#6 in my email that started this thread: 

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. 

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> 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.

            regards, tom lane

> 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? 

tgl@sss.pgh.pa.us wrote:

david.g.johnston@gmail.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.

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);

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.