Re: constraints & tableoid [pgsql8.1]

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