> david.g.johnston@xxxxxxxxx wrote: > >> bryn@xxxxxxxxxxxx wrote: >> >>> david.g.johnston@xxxxxxxxx wrote: >>> >>>> bryn@xxxxxxxxxxxx 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?
