"Lothar Bongartz" <lotharbongartz@hotmail.com> writes:
> For this reason the database is stalling from time to time and I have to
> restart the server. For the second time I have detected, that Postgres
> overwrites a table when shutting down. The table <onlineinfo> is only
> updated with NOW() and only for a single matching row:
> UPDATE onlineinfo SET date_end=NOW() WHERE memb_id=v_id
> When this command is executed while Postgres shuts down, all rows contain a
> "random" date like '2007-06-25' in the <date_end> column afterwards.
This is quite hard to believe. Can you provide a reproducible test
case?
I have seen cases where someone wrote what he thought was a single-row
update, but it turned out to be a whole-table update because the WHERE
clause actually reduced to constant TRUE. Your reference to "v_id"
makes me think that you are issuing this query inside a plpgsql
function. One of the common ways to shoot yourself in the foot like
that is to be careless about whether a name could match both a table
column and a plpgsql variable or parameter; could that have happened in
your situation?
BTW, there are easier ways to cancel a single query than restarting the
whole server.
regards, tom lane