Hi;
I figured I would report this as well, primarily because people
getting into table inheritance may try to use this to solve the set
exclusion problem (i.e. partly required to prevent duplicate key
values in an inheritance tree). I see this as minor because I don't
see a lot of people using these aspects of the software in this way
now.
or_examples=# create table cities (city text, state text, is_capital
bool, altitude int, check(not(is_capital and tableoid::regclass::text
= 'cities')));
The intent of the check constraint is to force rows in the parent
table to use only a part of the key domain, while another portion
(where is_capital is true) can be reserved for child tables.
or_examples=# insert into cities values ('Seattle', 'Washington', false, 100);
INSERT 0 1
or_examples=# insert into cities values ('Olympia', 'Washington', true, 100);
INSERT 0 1
Ok, note that the check constraint was violated by the second row but
apparently this wasn't caught.
or_examples=# select *, tableoid::regclass::text from cities;
city | state | is_capital | altitude | tableoid
---------+------------+------------+----------+----------
Seattle | Washington | f | 100 | cities
Olympia | Washington | t | 100 | cities
(2 rows)
And indeed if we try to add the constraint again over the top
PostgreSQL will complain loudly.
or_examples=# alter table cities add check(not(is_capital and
tableoid::regclass::name = 'cities'));
ERROR: check constraint "cities_check1" is violated by some row
My guess is that tableoid is not known when the check constraint is
checked. It seems to me one option would be to either disallow
checking tableoid in the check constraint or making this known.
However as it is, PostgreSQL will not raise an error until after the
insert has already been made and the check constraint is re-applied.
or_examples=# select version();
version
---------------------------------------------------------------------------------
--------------------------
PostgreSQL 9.1.4 on i386-redhat-linux-gnu, compiled by gcc (GCC)
4.7.0 20120507
(Red Hat 4.7.0-5), 32-bit
(1 row)
