[BUGS] Data corruption/loss when altering tables (fwd)

[Nicola Pero]

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 <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
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;
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org