Re: LIKE INCLUDING CONSTRAINTS is broken - Mailing list pgsql-hackers

From Ashutosh Bapat
Subject Re: LIKE INCLUDING CONSTRAINTS is broken
Date
Msg-id CAFjFpRce+wwRsmXNPHzGitSo6KOOFrQvX0TUtqba9g1tN6Bw2Q@mail.gmail.com
Whole thread Raw
In response to LIKE INCLUDING CONSTRAINTS is broken  (Alvaro Herrera <alvherre@2ndquadrant.com>)
List pgsql-hackers
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@2ndquadrant.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@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

pgsql-hackers by date:

Previous
From: Dean Rasheed
Date:
Subject: Re: WIP patch (v2) for updatable security barrier views
Next
From: Christian Convey
Date:
Subject: Re: Custom Scan APIs (Re: Custom Plan node)