>> I've been experiencing data corruption/loss in Postgresql 7.4.2.
>
>> I tracked this down to altering tables. After I alter a table, I get data loss
>> (fields becoming NULL with no reason!) unless I restart immediately the
>> database server.
>
> The problem shown in your example is simply that the UPDATE in the
> plpgsql function has cached a plan that applies to the original format
> of the table. You don't need to restart the server, but you do need to
> start a fresh session to get rid of the obsolete cached plan.
>
> There's a TODO item to cause cached plans to be flushed automatically
> when a table changes, but I don't know when it will get done (not for
> 8.0, for sure).
Thanks - very good to know. :-)
In practice / real world, what's the best workaround ? Is there any way
to control/prevent/flush the cached plans without restarting sessions or
restarting the server ?
I mean, the real world is a bit more complex than my example because you
have some processes accessing the database (web server / messaging
systems) through stored procedures while another process modifies the
table at random times.
How do I prevent the cached plans in the stored procedures used by the
other processes from messing things up when the other process modifies the
table ?
Is there a way to explictly flush cached plans ? Is there a way to
prevent the caching of the plan in the stored procedure ?
Thanks