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

Re: constraints & tableoid [pgsql8.1]

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: ??? ??? <jw(dot)pgsql(at)sduept(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: constraints & tableoid [pgsql8.1]
Date: 2006-04-11 08:43:50
Message-ID: 20060411084350.GA52915@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-bugs
On Tue, Apr 11, 2006 at 03:11:46PM +0800, ??? ??? wrote:
> jw=# CREATE TABLE base ( CHECK (tableoid = 'base'::regclass) );
> CREATE TABLE
> jw=# \d base
>     Table "public.base"
>  Column | Type | Modifiers
> --------+------+-----------
> Check constraints:
>     "base_tableoid_check" CHECK (tableoid = 'base'::regclass::oid)
> 
> jw=# INSERT INTO base DEFAULT VALUES ;
> ERROR:  new row for relation "base" violates check constraint
> "base_tableoid_check"

Check the constraint with a function that logs its arguments and
you'll see what's happening:

test=> CREATE FUNCTION toid_check(oid, oid) RETURNS boolean AS $$
test$> BEGIN
test$>     RAISE INFO 'toid_check(%, %)', $1, $2;
test$>     RETURN $1 = $2;
test$> END;
test$> $$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE FUNCTION

test=> CREATE TABLE base (CHECK(toid_check(tableoid, 'base'::regclass)));
CREATE TABLE

test=> INSERT INTO base DEFAULT VALUES;
INFO:  toid_check(0, 540339)
ERROR:  new row for relation "base" violates check constraint "base_tableoid_check"

Apparently a new row's tableoid isn't set until the row is actually
inserted.  Tableoid would be set in an AFTER trigger, but if the
intent is to prevent inheritance then enforcing the constraint with
a trigger on the base table wouldn't work because triggers aren't
inherited.

-- 
Michael Fuhr

In response to

Responses

pgsql-bugs by date

Next:From: christian.kothDate: 2006-04-11 12:47:33
Subject: BUG #2387: Incorrect sorting of timestamp with time zone
Previous:From: Richard HuxtonDate: 2006-04-11 08:26:58
Subject: Re: constraints & tableoid [pgsql8.1]

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