Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> david.g.johnston@xxxxxxxxx wrote:
> 
>> bryn@xxxxxxxxxxxx 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 variable are used in expressions or statements, the data types of the fields must not change from one call of the function to the next, since each expression will be analyzed using the data type that is present when the expression is first reached.
> 
> 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'd be overly worried about. Actually making DDL changes of this nature should be rare if not forbidden. Once live on-the-fly column type changes just shouldn't happen so having a plan in place that accommodates them is adding cost for no real 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-sessions before doing such a DDL and re-start them only when all such DDLs are done successfully.







[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux