Skip site navigation (1) Skip section navigation (2)

Re: Data corruption/loss when altering tables (fwd)

From: Nicola Pero <n(dot)pero(at)mi(dot)flashnet(dot)it>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Data corruption/loss when altering tables (fwd)
Date: 2004-11-22 18:26:09
Message-ID: Pine.LNX.4.61.0411221819490.25116@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-bugs
>> 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

In response to

Responses

pgsql-bugs by date

Next:From: Michael FuhrDate: 2004-11-22 19:07:11
Subject: Re: Data corruption/loss when altering tables (fwd)
Previous:From: Nicola PeroDate: 2004-11-22 18:00:30
Subject: Data corruption/loss when altering tables (fwd)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group