Postgres Pro
Downloads
Home   >   mailing lists

Thread: Add check constraint bug

Add check constraint bug

From
Peter Eisentraut
Date:
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

Re: Add check constraint bug

From
Stephan Szabo
Date:
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.

Re: Add check constraint bug

From
"scott.marlowe"
Date:
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.

Re: Add check constraint bug

From
Tom Lane
Date:
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

Re: Add check constraint bug

From
Bruce Momjian
Date:
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,
Pennsylvania19073

Re: Add check constraint bug

From
Tom Lane
Date:
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