Skip site navigation (1) Skip section navigation (2)

Data corruption/loss when altering tables

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
Date: 2004-11-22 04:34:22
Message-ID: Pine.LNX.4.61.0411220420370.23789@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-bugs
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;

pgsql-bugs by date

Next:From: David SchweikertDate: 2004-11-22 06:50:59
Subject: Re: select to_number('1,000', '999,999');
Previous:From: Tom LaneDate: 2004-11-22 01:10:08
Subject: Re: select to_number('1,000', '999,999');

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group