Sergey Konoplev <sergey.konoplev@xxxxxxxxxxxxxxxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
