The following bug has been logged online:
Bug reference: 5395
Logged by: Lothar Bongartz
Email address: lotharbonga...@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);
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
