Re: LIKE INCLUDING CONSTRAINTS is broken

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 <alvherre(at)2ndquadrant(dot)com>wrote:

> 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 Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company