Postgres Pro
Downloads
Home   >   mailing lists

Thread: whole-row functional index?

whole-row functional index?

From
Jasen Betts
Date:
jasen=# update member set id=id where id=441;
UPDATE 1
jasen=# create index member_search on member using gin (
(get_search_text_internal(member)) );
CREATE INDEX
jasen=# update member set id=id where id=441;
ERROR:  table row type and query-specified row type do not match
DETAIL:  Physical storage mismatch on dropped attribute at ordinal
position 109.
jasen=# select version();
-[ RECORD 1 ]-----------------------------------------------------------------------------------------
version | PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2

the function get_text_search_internal returns type tsvector

Re: whole-row functional index?

From
Tom Lane
Date:
Jasen Betts <jasen@xnet.co.nz> writes:
> jasen=# update member set id=id where id=441;
> UPDATE 1
> jasen=# create index member_search on member using gin (
> (get_search_text_internal(member)) );
> CREATE INDEX
> jasen=# update member set id=id where id=441;
> ERROR:  table row type and query-specified row type do not match
> DETAIL:  Physical storage mismatch on dropped attribute at ordinal
> position 109.

http://archives.postgresql.org/pgsql-bugs/2010-01/msg00089.php

This is fixed for the next 8.4 update, but it's not going to be fixed in
8.3.x.  Suggest recreating the table without any dropped columns.

            regards, tom lane

Re: whole-row functional index?

From
Jasen Betts
Date:
On 2010-02-02, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jasen Betts <jasen@xnet.co.nz> writes:
>> jasen=# update member set id=id where id=441;
>> UPDATE 1
>> jasen=# create index member_search on member using gin (
>> (get_search_text_internal(member)) );
>> CREATE INDEX
>> jasen=# update member set id=id where id=441;
>> ERROR:  table row type and query-specified row type do not match
>> DETAIL:  Physical storage mismatch on dropped attribute at ordinal
>> position 109.
>
> http://archives.postgresql.org/pgsql-bugs/2010-01/msg00089.php
>
> This is fixed for the next 8.4 update, but it's not going to be fixed in
> 8.3.x.  Suggest recreating the table without any dropped columns.

If it breaks again next time someone drops a column that's not going
to be suitable.

It's a large table with several incices and relationships to and from other
tables, if I re-create it (eg using SELECT ... INTO) I'd also need to
redo all the triggers, indices, constraints, and relationships
and then the next time I'd have track them all down again (in case
they've been changed), there's too much risk of something breaking.

I'll continue to use an index on column that's maintained by a trigger
to store the tsvector (like the fulltextsearch docs suggest) until we're
ready to upgrade our several clients to 8.4 or 9.