BUG #5395: UPDATE on shutdown overwrites table

From: "Lothar Bongartz" <lotharbongartz(at)hotmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5395: UPDATE on shutdown overwrites table
Date: 2010-03-30 15:27:45
Message-ID: 201003301527.o2UFRj8e022515@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 5395
Logged by: Lothar Bongartz
Email address: lotharbongartz(at)hotmail(dot)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);

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Hiroshi Saito 2010-03-30 17:30:06 Re: UTF-8 encoding failure
Previous Message Kevin Grittner 2010-03-30 15:25:57 Re: dividing money by money