While transforming the LIKE clause in transformTableLikeClause(), the
function does remap the varattnos of the constraint expression.
838│
839│ ccbin_node =
map_variable_attnos(stringToNode(ccbin),
840│
1, 0,
841│
attmap, tupleDesc-natts,
842│
found_whole_row);
So, upto this point, the attribute numbers in the constraint expression
string are in sync with the table schema definition.
But when it comes to treating inheritance in
DefineRelation-MergeAttributes(), the inherited attributes are added
before the table specific attributes (including the attributed included
because of LIKE clause). At this point the attribute numbers in constraint
expressions get out of sync with the table's schema and are never corrected
later. In AddRelationNewConstraints() we have following code and comment
2231 else
2232 {
2233 Assert(cdef-cooked_expr != NULL);
2234
2235 /*
2236 * Here, we assume the parser will only pass us valid CHECK
2237 * expressions, so we do no particular checking.
2238 */
2239 expr = stringToNode(cdef-cooked_expr);
2240 }
So, either in MergeAttributes or in AddRelationNewConstraints, we need to
restamp the attribute numbers in the constraints, so that they are in sync
with the table schema after adding the inherited columns.
The other possibility is to add the inherited columns after the table
specific columns (including those included because of LIKE clause), but
that would break lot of other things (including backward compatibility) I
guess.
On Sat, Jan 25, 2014 at 1:36 AM, Alvaro Herrera alvhe...@2ndquadrant.comwrote:
It seems CREATE TABLE ... (LIKE INCLUDING CONSTRAINTS) doesn't work
cleanly when there's also regular inheritance; my guess is that attnums
get messed up at some point after the constraints are generated.
Here's a trivial test case:
create table b (b1 int unique check (b1 100));
CREATE TABLE c (c1 int not null references b (b1));
create table d (d1 int, d2 point not null);
create table a (a1 int not null,
a2 text primary key,
a3 timestamptz(6),
like b including constraints,
like c)
inherits (d);
You can see the broken state:
alvherre=# \d [ab]
Tabla «public.a»
Columna |Tipo | Modificadores
-+-+---
d1 | integer |
d2 | point | not null
a1 | integer | not null
a2 | text| not null
a3 | timestamp(6) with time zone |
b1 | integer |
c1 | integer | not null
Índices:
a_pkey PRIMARY KEY, btree (a2)
Restricciones CHECK:
b_b1_check CHECK (a2 100)
Hereda: d
Tabla «public.b»
Columna | Tipo | Modificadores
-+-+---
b1 | integer |
Índices:
b_b1_key UNIQUE CONSTRAINT, btree (b1)
Restricciones CHECK:
b_b1_check CHECK (b1 100)
Referenciada por:
TABLE c CONSTRAINT c_c1_fkey FOREIGN KEY (c1) REFERENCES b(b1)
Notice how the CHECK constraint in table b points to column b1, but in
table a it is mentioning column a2, even though that one is not even of
the correct datatype. In fact if you try an insert, you get a weird
error message:
alvherre=# insert into a (d2, a2, a1, c1) values ('(1, 0)', '1', 1, 1);
ERROR: attribute 4 has wrong type
DETALLE: Table has type text, but query expects integer.
If I take out the INHERITS clause in table a, the error disappears.
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
