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


Thu, 5 Sep 2002 23:37:47 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Add check constraint bug

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.



Thu, 5 Sep 2002 15:49:40 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Add check constraint bug
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.


Thu, 5 Sep 2002 16:57:32 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: Add check constraint bug
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

Thu, 05 Sep 2002 19:52:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Add check constraint bug 

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

Thu, 5 Sep 2002 20:44:05 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Add check constraint bug
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is there a TODO here?

I've committed a fix for the immediate problem.  I want to take a very
hard look at the other heap_mark4update calls, though.

			regards, tom lane

Thu, 05 Sep 2002 21:22:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Add check constraint bug 