Обсуждение: whole-row functional index?
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
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
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.