constraints & tableoid [pgsql8.1]

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"
jw=#

维 姜 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"
> jw=#

The CHECK tests the tuple that is being inserted, which doesn't include
tableoid. I'm not sure if this counts as a bug or not.

You might be able to do this with a trigger function (although I'm not
clear what you're trying to do).

--
Richard Huxton
Archonet Ltd

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

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> Apparently a new row's tableoid isn't set until the row is actually
> inserted.

I believe that's true of all the system columns. If you're using oid,
for example, that's not assigned either until heap_insert().

This behavior doesn't seem unreasonable to me. A candidate row is not a
member of the table until *after* it's passed its constraint checks ---
until then, it's just some values sitting in memory.

regards, tom lane

Tom Lane 写道:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
>
>> Apparently a new row's tableoid isn't set until the row is actually
>> inserted.
>>
>
> I believe that's true of all the system columns. If you're using oid,
> for example, that's not assigned either until heap_insert().
>
> This behavior doesn't seem unreasonable to me. A candidate row is not a
> member of the table until *after* it's passed its constraint checks ---
> until then, it's just some values sitting in memory.
>
> regards, tom lane
>
>
jw=# ALTER TABLE base DROP CONSTRAINT base_tableoid_check;
ALTER TABLE
jw=# ALTER TABLE base ADD CHECK (tableoid = 0);
ALTER TABLE
jw=# INSERT INTO base DEFAULT VALUES ;
INSERT 0 1
jw=# INSERT INTO base DEFAULT VALUES ;
INSERT 0 1
jw=# INSERT INTO base DEFAULT VALUES ;
INSERT 0 1
jw=# select *,tableoid from base;
tableoid
----------
301146
301146
301146
(3 rows)
jw=# \d+ base
Table "public.base"
Column | Type | Modifiers | Description
--------+------+-----------+-------------
Check constraints:
"base_tableoid_check" CHECK (tableoid = 0::oid)
Has OIDs: no