Data corruption/loss when altering tables (fwd)

This is my third attempt at posting to the list ... hopefully it will make
it this time

---------- Forwarded message ----------
Date: Mon, 22 Nov 2004 04:34:22 +0000 (GMT)
From: Nicola Pero <n(dot)pero(at)mi(dot)flashnet(dot)it>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Data corruption/loss when altering tables

I've been experiencing data corruption/loss in Postgresql 7.4.2.

I tracked this down to altering tables. After I alter a table, I get data loss
(fields becoming NULL with no reason!) unless I restart immediately the
database server. After the restart all seems OK (but the data which was lost,
is gone, I just don't loose any more). I'm a bit scared though that this might
not be enough, so I'd appreciate comments. Is this a known bug which has been
fixed in later releases ?

I managed to extract a simple sequence of SQL instructions which reliably
causes data corruption/loss on my 7.4.2 (fedora core 2 default install). I
found that all machines I've access to are 7.4.2, so I couldn't test it on
later releases. Apologies if it has already been fixed, I didn't find
references to a bug fix for it.

Thanks

/*
* Nicola Pero, November 2004
*
* Test which demonstrates data corruption in Postgres
* when modifying tables then using plpgsql functions.
*/

/* We create a basic table. */
CREATE TABLE MyTable (

/* An index. */
ID SERIAL,
PRIMARY KEY (ID),

/* A value. */
CountA INT DEFAULT 0

);

/* We create a stored procedure to manipulate a record in the table. */
CREATE FUNCTION DO_SOMETHING(integer,integer)
RETURNS INTEGER AS '
DECLARE
BEGIN
UPDATE MyTable SET CountA = CountA + $1 WHERE ID = $2;
RETURN 0;
END' LANGUAGE 'plpgsql';

/* We insert a single record in the table. */
INSERT INTO MyTable (CountA) VALUES (1);

/* We try out that the stored procedure works. */
SELECT DO_SOMETHING (1, 1);

/* Print out the table now. */
/* On my system, I get:
* id | counta
* ----+--------
* 1 | 2
* (1 row)
*/ SELECT * FROM MyTable;

/* OK. Now we modify the table. We add a column with value 0. */
ALTER TABLE MyTable ADD COLUMN CountB INT;
ALTER TABLE MyTable ALTER COLUMN CountB SET DEFAULT 0;
UPDATE MyTable SET CountB = 0;

/* Now put a value in CountB. */
UPDATE MyTable SET CountB = 12;

/* Print out the table with the new column added. */
/* On my system, I get:
* id | counta | countb
* ----+--------+--------
* 1 | 2 | 12
* (1 row)
*/
SELECT * FROM MyTable;

/* Alter the value in the CountA column using the stored procedure. */
SELECT DO_SOMETHING (1, 1);

/* Print out the table again. */
/* On my system, I get:
* id | counta | countb
* ----+--------+--------
* 1 | 3 |
* (1 row)
*
* note how the value in the CountB column has been
* destroyed/corrupted into NULL!!
*/
SELECT * FROM MyTable;