Re: BUG #7763: "CREATE TABLE ... (LIKE ... INCLUDING INDEXES ...)" does not work with indexes on composite types

On 2012-12-20 12:40:44 +0000, norbi(at)nix(dot)hu wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7763
> Logged by: Norbert Buchmuller
> Email address: norbi(at)nix(dot)hu
> PostgreSQL version: 9.2.2
> Operating system: Linux 2.6.32, i386, Debian GNU/Linux 6.0.5
> Description:
>
> There's a table that has a B-Tree index on a composite type expression. When
> attempting to create another table just like the first table and with the
> indexes also "copied" using the "CREATE TABLE ... (LIKE ... INCLUDING
> INDEXES ...)" statement, it throws an error (see below) and the table is not
> created.
>
> I believe it's a bug, from the documentation i assumed that it should create
> the table with a similar index, no matter that the index is on a composite
> type expression.
>
> postgres(at)vger:~$ cat
> create_table_like_including_indexes-and-index_on_composite_type.sql
> \set VERBOSITY verbose
> \set ECHO all
> SELECT version();
> CREATE TYPE type1 AS (x int, y int);
> CREATE TABLE table1 (a int, b int);
> CREATE INDEX index1 ON table1 ( ( (a, b)::type1 ) );
> CREATE TABLE table2 ( LIKE table1 INCLUDING INDEXES );
> \d table2
> postgres(at)vger:~$ dropdb test1; createdb test1 && psql --no-align --tuples -d
> test1 -f create_table_like_including_indexes-and-index_on_composite_type.sql
>
> SELECT version();
> PostgreSQL 9.2.2 on i686-pc-linux-gnu, compiled by gcc-4.4.real (Debian
> 4.4.5-8) 4.4.5, 32-bit
> CREATE TYPE type1 AS (x int, y int);
> CREATE TYPE
> CREATE TABLE table1 (a int, b int);
> CREATE TABLE
> CREATE INDEX index1 ON table1 ( ( (a, b)::type1 ) );
> CREATE INDEX
> CREATE TABLE table2 ( LIKE table1 INCLUDING INDEXES );
> psql:create_table_like_including_indexes-and-index_on_composite_type.sql:7:
> ERROR: 42P16: column "" has pseudo-type record
> LOCATION: CheckAttributeType, heap.c:496
> \d table2
> Did not find any relation named "table2".

Concretely that seems to be transformRowExpr's fault. It overwrites
row_typeid even though its marked as COERCE_EXPLICIT_CAST.

Now the original problem seems to be that we are transforming an already
transformed expression. generateClonedIndexStmt gets the expression from
the old index via nodeToString, remaps some attnos, but thats about
it.
ISTM IndexElem grow a cooked_expr member.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services