Re: notify/listen disappearing data

Sim Zacks wrote:
> select version()
> "PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC
> i686-pc-linux-gnu-gcc (GCC) 3.3.5 (Gentoo Linux 3.3.5-r1, ssp-3.3.2-3,
> pie-8.7.7.1)"

Upgrade to 8.0.14 - you are missing 13 sets of bugfixes.

> Today I added 2 new fields to a single row table and populated them with
> values.
> I noticed that the values disappeared after a while and could not figure
> out why.
> After they disappeared a couple more times I started investigating.

> It can't be an issue of transactions, because fields that existed before
> the daemon was started are not reverted to any prior state.
> The table itself isn't referenced in the code here, it is only called in
> a function.

The function will have its query planned at the start of a connection.
Your daemon still had a query plan left over from before you added those
columns. You'll see similar discussion in the archives regarding
temporary tables and plpgsql.

Of course, if you had executed the SQL directly rather than through a
function it would have been planned each time and you'd not have noticed
any problems. Unless of course you had PREPAREd a query which, again,
would have frozen its plan.

More recent versions of PG have better plan invalidation, although I
don't know whether they address this particular scenario.

--
Richard Huxton
Archonet Ltd