Re: Stored function signature incompatibility in index (probably a bug)

Hi,

When I was restoring a dump on 9.1.4 (created on 9.0.7) I got this error:

ERROR: function
imported_credentials_generalized_external_id(imported_email3) does not
exist
LINE 1: ...ed_external_i_idx ON imported_email3 USING btree (imported_c...
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.

On this index creation statement:

CREATE INDEX imported_email3_imported_credentials_generalized_external_i_idx
ON imported_email3 USING btree
(imported_credentials_generalized_external_id(imported_email3.*));

Looking on the function and index in the original database I found a
very strange situation when the argument data type of the function
differs from the type of the argument in the function's signature in
the index.

mirtesen-0-3=# \df imported_credentials_generalized_external_id
List of functions
-[ RECORD 1 ]-------+---------------------------------------------
Schema | public
Name | imported_credentials_generalized_external_id
Result data type | text
Argument data types | i_row imported_email
Type | normal

mirtesen-0-3=# \d
imported_email3_imported_credentials_generalized_external_i_idx
Index
"public.imported_email3_imported_credentials_generalized_external_i_idx"
Column | Type |
Definition
----------------------------------------------+------+-----------------------------------------------------------------
imported_credentials_generalized_external_id | text |
imported_credentials_generalized_external_id(imported_email3.*)
btree, for table "public.imported_email3"

I managed to reproduce this issue by creating another table with LIKE.

mirtesen-0-3=# CREATE TABLE imported_email4 (LIKE imported_email3
INCLUDING ALL);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"imported_email4_pkey" for table "imported_email4"
CREATE TABLE

And what I have found is that it just renamed the table name in the
function's signature in the index.

mirtesen-0-3=# \d
imported_email4_imported_credentials_generalized_external_i_idx
Index
"public.imported_email4_imported_credentials_generalized_external_i_idx"
Column | Type |
Definition
----------------------------------------------+------+-----------------------------------------------------------------
imported_credentials_generalized_external_id | text |
imported_credentials_generalized_external_id(imported_email4.*)
btree, for table "public.imported_email4"

I think it would be useful to do some checks here.

Thank you.

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204

Sergey Konoplev <sergey(dot)konoplev(at)postgresql-consulting(dot)com> writes:
> When I was restoring a dump on 9.1.4 (created on 9.0.7) I got this error:
> ERROR: function imported_credentials_generalized_external_id(imported_email3) does not exist

Yeah. Here's a more complete example on HEAD:

regression=# create table foo(f1 int, f2 int);
CREATE TABLE
regression=# create function foosum(foo) returns int language sql as 'select $1.f1 + $1.f2' immutable ;
CREATE FUNCTION
regression=# create index fooi on foo (foosum(foo.*));
CREATE INDEX
regression=# \d fooi
Index "public.fooi"
Column | Type | Definition
--------+---------+---------------
foosum | integer | foosum(foo.*)
btree, for table "public.foo"

regression=# create table foobar (like foo including indexes);
CREATE TABLE
regression=# \d foobar
Table "public.foobar"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
f2 | integer |
Indexes:
"foobar_foosum_idx" btree (foosum(foobar.*))

regression=# \d foobar_foosum_idx
Index "public.foobar_foosum_idx"
Column | Type | Definition
--------+---------+------------------
foosum | integer | foosum(foobar.*)
btree, for table "public.foobar"

While foobar_foosum_idx looks alright on first glance, it cannot
be duplicated:

regression=# create index foobari on foobar (foosum(foobar.*));
ERROR: function foosum(foobar) does not exist
LINE 1: create index foobari on foobar (foosum(foobar.*));
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

So the CREATE TABLE LIKE code is being far too cavalier about dealing
with whole-row Vars in index definitions (and who knows where else).
They don't have the same type in the cloned table as they did in the
original. Some cases would probably still work all right, but not this
usage.

Also, a look into the pg_index entry shows that the whole-row
Var for foobar.* is claimed to have vartype equal to foo's rowtype,
which is flat out wrong, and could easily lead to crashes once either
table had been altered to be different from the other.

A potential workaround is illustrated by:

regression=# create index foobari on foobar (foosum(row(foobar.*)::foo));
CREATE INDEX
regression=# \d foobari
Index "public.foobari"
Column | Type | Definition
--------+---------+--------------------------
foosum | integer | foosum(ROW(f1, f2)::foo)
btree, for table "public.foobar"

but this seems like kind of a kluge. I'm not sure that we ought to
insert such a thing rather than just throwing an error.

regards, tom lane

On Thu, Jun 28, 2012 at 8:59 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> A potential workaround is illustrated by:
>
> regression=# create index foobari on foobar (foosum(row(foobar.*)::foo));
> CREATE INDEX
> regression=# \d foobari
>           Index "public.foobari"
>  Column |  Type   |        Definition
> --------+---------+--------------------------
>  foosum | integer | foosum(ROW(f1, f2)::foo)
> btree, for table "public.foobar"

Thank you, Tom. It is a good idea. I need it for partitioning. So I am
going to create a parent table and the first partition with this
whole-row casted to the parent's type in index. Other partitions will
be created by CREATE...LIKE.

> but this seems like kind of a kluge.  I'm not sure that we ought to
> insert such a thing rather than just throwing an error.

May be this issue and workaround is worth to be mentioned in docs
besides throwing an error?

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204