Обсуждение: 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=#
=E7=BB=B4 =E5=A7=9C wrote: > jw=3D# CREATE TABLE base ( CHECK (tableoid =3D 'base'::regclass) ); > CREATE TABLE > jw=3D# \d base > Table "public.base" > Column | Type | Modifiers > --------+------+----------- > Check constraints: > "base_tableoid_check" CHECK (tableoid =3D 'base'::regclass::oid) >=20 > jw=3D# INSERT INTO base DEFAULT VALUES ; > ERROR: new row for relation "base" violates check constraint > "base_tableoid_check" > jw=3D# The CHECK tests the tuple that is being inserted, which doesn't include=20 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=20 clear what you're trying to do). --=20 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@fuhr.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@fuhr.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