Re: whole-row functional index?

On 2010-02-02, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jasen Betts <jasen(at)xnet(dot)co(dot)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.