Skip site navigation (1) Skip section navigation (2)

Re: whole-row functional index?

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: whole-row functional index?
Date: 2010-02-03 05:53:37
Message-ID: hkb311$9u6$1@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-bugs
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.


In response to

pgsql-bugs by date

Next:From: Joe ConwayDate: 2010-02-03 06:01:13
Subject: Re: BUG #5304: psql using conninfo fails in connecting to the server
Previous:From: Pavel StehuleDate: 2010-02-03 03:27:37
Subject: Re: BUG #5308: How to disable Case sensitivity on naming identifiers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group