ALTER TABLE / ADD COLUMN BUG

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : RP. Dumont
Your email address : dumont(at)gmx(dot)net

System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel
Pentium MMX

Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.5 ELF
(i386)

PostgreSQL version (example: PostgreSQL-6.5.1) : PostgreSQL-6.5.1

Compiler used (example: gcc 2.8.0) : gcc
egcs-2.91.66

Please enter a FULL description of your problem:
------------------------------------------------

When I add a column to a table using:

"ALTER TABLE mytest ADD COLUMN z INTEGER CHECK (z > 9);"

and after that doing a:

"SELECT * FROM mytest"

the new column is not displayed !!!

When I try to insert a new record in the altered table I get an error
message
saying that I specified more attribute values than the table contains (so
the newly
added column is not recognized) !

After that I tried a

"SELECT z FROM mytest"

and I got an error message saying that the backend server process terminated
abnormally
and no further processing is possible.

After restarting psql the newly added column is shown, insertions are
possible, but no
checking (z > 9) is performed.

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

First connect to a database:

psql mydb

Then create a table:

CREATE TABLE mytest (
x INTEGER,
y INTEGER
);

Then add some values:

INSERT INTO mytest
VALUES (12,165);
...

Then add a new column with a ´CHECK´:

ALTER TABLE mytest
ADD COLUMN z INTEGER CHECK (z > 9);

After that try a:

SELECT * FROM mytest;

The newly inserted column ´z´ is not shown!
Also it is not possible to insert a new record (an error message is
printed):

INSERT INTO mytest
VALUES (37,76,16);

Finally try a:

SELECT z FROM mytest;

The backend server process crashes!

Type again:

psql mydb

and then:

SELECT * FROM mytest;

and the third column is shown, but no checking is performed when insertions
are done:

INSERT INTO mytest
VALUES (26,18,5);

is done without error message, although "CHECK (z > 9)" was specified
when the new column was added!

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------