Add check constraint bug
The following happens in latest CVS and a fresh database:
create table test (a int);
insert into test values (1);
alter table test add column b text check (b <> '');
alter table test add check (a > 0);
alter table test add check (a <> 1);
After the last command I get
ERROR: CheckConstraintFetch: unexpected record found for rel test
and then the table seems to be wedged because any access to it will get
the same error.
Also, psql seems to forget about check constraints in peculiar ways:
create table test (a int);
insert into test values (1);
alter table test add column b text check (b <> '');
\d test
alter table test add check (a > 0);
\d test
The first shows:
Table "public.test"
Spalte | Typ | Attribute
--------+---------+-----------
a | integer |
b | text |
The second shows:
Table "public.test"
Spalte | Typ | Attribute
--------+---------+-----------
a | integer |
b | text |
Check-Constraints: ￜtest_bￜ (b <> ''::text)
ￜ$1ￜ (a > 0)
Note the first one doesn't show any constraints.
--
Peter Eisentraut peter_e@gmx.net
On Thu, 5 Sep 2002, Peter Eisentraut wrote:
The following happens in latest CVS and a fresh database:
create table test (a int);
insert into test values (1);
alter table test add column b text check (b <> '');
alter table test add check (a > 0);
alter table test add check (a <> 1);After the last command I get
ERROR: CheckConstraintFetch: unexpected record found for rel test
and then the table seems to be wedged because any access to it will get
the same error.
I don't have reasonable access to the machine at home for code purposes,
but it looks to me that the add column line is the one that's causing
the bug. It's inserting a check constraint but not upping relchecks
which seems to work because it's zero and therefore doesn't even look, but
the add check is incrementing the count and inserting its constraint which
makes 2 real constraints and relchecks=1 which causes the error.
This is probably also why it forgets about the check constraint below
since relchecks is 0, but I didn't look.
Note that:
create table test(a int check (a>3));
alter table test add column b text check(b<>'');
select * from test;
will error.
On Thu, 5 Sep 2002, Stephan Szabo wrote:
On Thu, 5 Sep 2002, Peter Eisentraut wrote:
The following happens in latest CVS and a fresh database:
create table test (a int);
insert into test values (1);
alter table test add column b text check (b <> '');
alter table test add check (a > 0);
alter table test add check (a <> 1);After the last command I get
ERROR: CheckConstraintFetch: unexpected record found for rel test
and then the table seems to be wedged because any access to it will get
the same error.
Just fyi, 7.2.1 does this too.
Peter Eisentraut <peter_e@gmx.net> writes:
The following happens in latest CVS and a fresh database:
create table test (a int);
insert into test values (1);
alter table test add column b text check (b <> '');
This bug's been there awhile I fear. The failure occurs when
AlterTableAddColumn needs to add a check constraint AND the
new column causes AlterTableCreateToastTable to do its thing.
The reason there is a bug is that AlterTableCreateToastTable
gratuitously does a heap_mark4update, thereby selecting the un-updated
version of the pg_class tuple as its basis for modification (and
ignoring the HeapTupleSelfUpdated return code that warned that there
was a problem).
I've said before that I do not like heap_mark4update in catalog
manipulations, and here's a perfect example of why it's a bad idea.
regards, tom lane
Is there a TODO here?
---------------------------------------------------------------------------
Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
The following happens in latest CVS and a fresh database:
create table test (a int);
insert into test values (1);
alter table test add column b text check (b <> '');This bug's been there awhile I fear. The failure occurs when
AlterTableAddColumn needs to add a check constraint AND the
new column causes AlterTableCreateToastTable to do its thing.The reason there is a bug is that AlterTableCreateToastTable
gratuitously does a heap_mark4update, thereby selecting the un-updated
version of the pg_class tuple as its basis for modification (and
ignoring the HeapTupleSelfUpdated return code that warned that there
was a problem).I've said before that I do not like heap_mark4update in catalog
manipulations, and here's a perfect example of why it's a bad idea.regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073