BUG #5395: UPDATE on shutdown overwrites table - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5395
Logged by:          Lothar Bongartz
Email address:      lotharbongartz@hotmail.com
PostgreSQL version: 8.4.3
Operating system:   Windows Server 2003 R2
Description:        UPDATE on shutdown overwrites table
Details:

I have ported a community with about one million members from MS SQL to
Postgres.
While the overall performance is comparable, I have noticed, that
conditional bulk writing can be extremely slow in Postgres:
SELECT ... INTO ... WHERE
UPDATE ... FROM ... WHERE
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.
To repair this, I have created a table from a backup:
CREATE TEMP TABLE temp_onlineinfo_bak
(
  memb_id integer NOT NULL,
  date_end timestamp NOT NULL
 )
After filling the temporary table from the backup, I use it to repair the
overwritten table:
UPDATE onlineinfo SET date_end=B.date_end
 FROM onlineinfo O, temp_onlineinfo_bak B
 WHERE O.date_end<'2010-03-21' AND B.memb_id=O.memb_id;
The WHERE condition is needed to prevent overwriting rows, which are updated
in the meantime.

Unfortunately this command needs several hours to complete, since this is
again a conditional bulk writing.

I tried all kind of variations like removing the index on
<onlineinfo><date_end> before the update or setting up an index on
<temp_onlineinfo_bak><memb_id>

Nothing helps. These are the data of the table:
CREATE TABLE onlineinfo
(
  memb_id integer NOT NULL,
  log_count integer NOT NULL,
  date_end timestamp NOT NULL,
  ip integer NOT NULL,
  CONSTRAINT onlineinfo_pkey PRIMARY KEY (memb_id),
  CONSTRAINT fk__onlineinf__memb___55009f39 FOREIGN KEY (memb_id)
      REFERENCES member (memb_id) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE onlineinfo OWNER TO postgres;

-- Index: onlineinfo_date_end

-- DROP INDEX onlineinfo_date_end;

CREATE INDEX onlineinfo_date_end
  ON onlineinfo
  USING btree
  (date_end);