Обсуждение: Cache loolup failed problems with tsearch following pg_restore
Hi, I'm am having real problems restoring a database which uses tsearch2. I've followed the dump/restore notes in the tseach2 documents. After much fiddling, I have managed to restore the database. However, as soon as I call a SP which updates a table which uses ts_vector. I get the following error: 2004-03-23 11:53:03 [27000] ERROR: fmgr_info: function 534990: cache lookup failed The following is from the pg errorlog: 2004-03-23 11:53:03 [27000] LOG: query: INSERT INTO article_content ( active , article_id , lang_id , active_date , expire_date , revision_date , revision_days , content_type_id , contribution_receipt_date , version , article_type_id , override_template_id , name , copyright , summary , comment , content , search1 ) VALUES ( true , $1 , $2 , $3 , $4 , $5 , $6 ::interval , $7 , $8 , DEFAULT , $9 , $10 , $11 , $12 , $13 , $14 , $15 , to_tsvector('default', coalesce( $16 ,'') || ' ' || coalesce( $17 ,'') || ' ' || coalesce(strip_tags( $18 ),'')) ) 2004-03-23 11:53:03 [27000] LOG: query: select oid from pg_ts_cfg where ts_name = $1 2004-03-23 11:53:03 [27000] LOG: query: select prs_name from pg_ts_cfg where oid = $1 2004-03-23 11:53:03 [27000] LOG: query: select lt.tokid, pg_ts_cfgmap.dict_name from pg_ts_cfgmap, pg_ts_cfg, token_type( $1 ) as lt where lt.alias = pg_ts_cfgmap.tok_alias and pg_ts_cfgmap.ts_name = pg_ts_cfg.ts_name and pg_ts_cfg.oid= $2 order by lt.tokid desc; 2004-03-23 11:53:03 [27000] LOG: query: select oid from pg_ts_parser where prs_name = $1 2004-03-23 11:53:03 [27000] LOG: query: select prs_start, prs_nexttoken, prs_end, prs_lextype, prs_headline from pg_ts_parser where oid = $1 2004-03-23 11:53:03 [27000] ERROR: fmgr_info: function 534990: cache lookup failed PostgreSQL 7.3.3 on i386-redhat-linux-gnu, compiled by GCC 2.96 I'm totallly stuck - I have seen pg postings which refer to droping and re-creating SPs but I don't have the knowledge of tseach2 to understand all the dependencies. Hope someone can help!! Cheers Mike -- Dr Mike Bell 07973 524 446
Mike, I think the problem is that tsearch2 is referenced by your SP (stored procedure) and your SP should be recreated *after* tsearch2 installed because functions are referenced by OID and your SP try to lookup tsearch2 functions using old OID. Oleg On Tue, 23 Mar 2004, Mike Bell wrote: > Hi, > > I'm am having real problems restoring a database which uses tsearch2. > I've followed the dump/restore notes in the tseach2 documents. After > much fiddling, I have managed to restore the database. However, as soon > as I call a SP which updates a table which uses ts_vector. I get the > following error: > 2004-03-23 11:53:03 [27000] ERROR: fmgr_info: function 534990: cache > lookup failed > > The following is from the pg errorlog: > > 2004-03-23 11:53:03 [27000] LOG: query: INSERT INTO article_content ( > active , article_id , lang_id , active_date , expire_date , > revision_date , revision_days , content_type_id , > contribution_receipt_date , version , article_type_id , > override_template_id , name , copyright , summary , comment , content , > search1 ) VALUES ( true , $1 , $2 , $3 , $4 , $5 , $6 ::interval , $7 , > $8 , DEFAULT , $9 , $10 , $11 , $12 , $13 , $14 , $15 , > to_tsvector('default', coalesce( $16 ,'') || ' ' || coalesce( $17 ,'') > || ' ' || coalesce(strip_tags( $18 ),'')) ) > 2004-03-23 11:53:03 [27000] LOG: query: select oid from pg_ts_cfg where > ts_name = $1 > 2004-03-23 11:53:03 [27000] LOG: query: select prs_name from pg_ts_cfg > where oid = $1 > 2004-03-23 11:53:03 [27000] LOG: query: select lt.tokid, > pg_ts_cfgmap.dict_name from pg_ts_cfgmap, pg_ts_cfg, token_type( $1 ) as > lt where lt.alias = pg_ts_cfgmap.tok_alias and pg_ts_cfgmap.ts_name = > pg_ts_cfg.ts_name and pg_ts_cfg.oid= $2 order by lt.tokid desc; > 2004-03-23 11:53:03 [27000] LOG: query: select oid from pg_ts_parser > where prs_name = $1 > 2004-03-23 11:53:03 [27000] LOG: query: select prs_start, prs_nexttoken, > prs_end, prs_lextype, prs_headline from pg_ts_parser where oid = $1 > 2004-03-23 11:53:03 [27000] ERROR: fmgr_info: function 534990: cache > lookup failed > > PostgreSQL 7.3.3 on i386-redhat-linux-gnu, compiled by GCC 2.96 > > I'm totallly stuck - I have seen pg postings which refer to droping and > re-creating SPs but I don't have the knowledge of tseach2 to understand > all the dependencies. > Hope someone can help!! > > Cheers > > Mike > > > > -- > Dr Mike Bell > 07973 524 446 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg, Thanks for your suggestion but I'm not sure how this will help. The function is created _after_ the tsearch installation (script below). I'm backing up on one machine and trying to restore to another. Cheers Mike #!/bin/bash createdb cms psql cms < /var/lib/pgsql/backups/globalobjects.sql psql cms < /var/lib/pgsql/backups/extra.sql psql cms < /var/lib/pgsql/backups/tsearch2.sql psql -e cms < /var/lib/pgsql/backups/db_schema_new.sql > errors 2>&1 pg_restore -N -a -d cms /var/lib/pgsql/backups/2004-03-16-23-10-cms_241103.dmp > errors2 2>&1 -----Original Message----- From: Oleg Bartunov [mailto:oleg@sai.msu.su] Sent: 23 March 2004 15:20 To: Mike Bell Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Cache loolup failed problems with tsearch following pg_restore Mike, I think the problem is that tsearch2 is referenced by your SP (stored procedure) and your SP should be recreated *after* tsearch2 installed because functions are referenced by OID and your SP try to lookup tsearch2 functions using old OID. Oleg On Tue, 23 Mar 2004, Mike Bell wrote: > Hi, > > I'm am having real problems restoring a database which uses tsearch2. > I've followed the dump/restore notes in the tseach2 documents. After > much fiddling, I have managed to restore the database. However, as soon > as I call a SP which updates a table which uses ts_vector. I get the > following error: > 2004-03-23 11:53:03 [27000] ERROR: fmgr_info: function 534990: cache > lookup failed > > The following is from the pg errorlog: > > 2004-03-23 11:53:03 [27000] LOG: query: INSERT INTO article_content ( > active , article_id , lang_id , active_date , expire_date , > revision_date , revision_days , content_type_id , > contribution_receipt_date , version , article_type_id , > override_template_id , name , copyright , summary , comment , content , > search1 ) VALUES ( true , $1 , $2 , $3 , $4 , $5 , $6 ::interval , $7 , > $8 , DEFAULT , $9 , $10 , $11 , $12 , $13 , $14 , $15 , > to_tsvector('default', coalesce( $16 ,'') || ' ' || coalesce( $17 ,'') > || ' ' || coalesce(strip_tags( $18 ),'')) ) > 2004-03-23 11:53:03 [27000] LOG: query: select oid from pg_ts_cfg where > ts_name = $1 > 2004-03-23 11:53:03 [27000] LOG: query: select prs_name from pg_ts_cfg > where oid = $1 > 2004-03-23 11:53:03 [27000] LOG: query: select lt.tokid, > pg_ts_cfgmap.dict_name from pg_ts_cfgmap, pg_ts_cfg, token_type( $1 ) as > lt where lt.alias = pg_ts_cfgmap.tok_alias and pg_ts_cfgmap.ts_name = > pg_ts_cfg.ts_name and pg_ts_cfg.oid= $2 order by lt.tokid desc; > 2004-03-23 11:53:03 [27000] LOG: query: select oid from pg_ts_parser > where prs_name = $1 > 2004-03-23 11:53:03 [27000] LOG: query: select prs_start, prs_nexttoken, > prs_end, prs_lextype, prs_headline from pg_ts_parser where oid = $1 > 2004-03-23 11:53:03 [27000] ERROR: fmgr_info: function 534990: cache > lookup failed > > PostgreSQL 7.3.3 on i386-redhat-linux-gnu, compiled by GCC 2.96 > > I'm totallly stuck - I have seen pg postings which refer to droping and > re-creating SPs but I don't have the knowledge of tseach2 to understand > all the dependencies. > Hope someone can help!! > > Cheers > > Mike >
On Tue, 23 Mar 2004, Mike Bell wrote: > Oleg, > > Thanks for your suggestion but I'm not sure how this will help. The > function is created _after_ the tsearch installation (script below). I'm > backing up on one machine and trying to restore to another. It's not clear that you create your SPs after tsearch2 installation :) Where is your SP ? And what's /var/lib/pgsql/backups/tsearch2.sql ? I think you should install tsearch2.sql from pgsql distribution. anyway, please check if your SP has any hardcoded OID. > > Cheers > > Mike > > #!/bin/bash > createdb cms > psql cms < /var/lib/pgsql/backups/globalobjects.sql > psql cms < /var/lib/pgsql/backups/extra.sql > psql cms < /var/lib/pgsql/backups/tsearch2.sql > psql -e cms < /var/lib/pgsql/backups/db_schema_new.sql > errors 2>&1 > pg_restore -N -a -d cms > /var/lib/pgsql/backups/2004-03-16-23-10-cms_241103.dmp > errors2 2>&1 > > -----Original Message----- > From: Oleg Bartunov [mailto:oleg@sai.msu.su] > Sent: 23 March 2004 15:20 > To: Mike Bell > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Cache loolup failed problems with tsearch following > pg_restore > > > Mike, > > I think the problem is that tsearch2 is referenced by your SP (stored > procedure) > and your SP should be recreated *after* tsearch2 installed because > functions > are referenced by OID and your SP try to lookup tsearch2 functions > using old OID. > > Oleg > > On Tue, 23 Mar 2004, Mike Bell wrote: > > > Hi, > > > > I'm am having real problems restoring a database which uses tsearch2. > > I've followed the dump/restore notes in the tseach2 documents. After > > much fiddling, I have managed to restore the database. However, as > soon > > as I call a SP which updates a table which uses ts_vector. I get the > > following error: > > 2004-03-23 11:53:03 [27000] ERROR: fmgr_info: function 534990: cache > > lookup failed > > > > The following is from the pg errorlog: > > > > 2004-03-23 11:53:03 [27000] LOG: query: INSERT INTO article_content ( > > active , article_id , lang_id , active_date , expire_date , > > revision_date , revision_days , content_type_id , > > contribution_receipt_date , version , article_type_id , > > override_template_id , name , copyright , summary , comment , content > , > > search1 ) VALUES ( true , $1 , $2 , $3 , $4 , $5 , $6 ::interval , $7 > , > > $8 , DEFAULT , $9 , $10 , $11 , $12 , $13 , $14 , $15 , > > to_tsvector('default', coalesce( $16 ,'') || ' ' || coalesce( $17 ,'') > > || ' ' || coalesce(strip_tags( $18 ),'')) ) > > 2004-03-23 11:53:03 [27000] LOG: query: select oid from pg_ts_cfg > where > > ts_name = $1 > > 2004-03-23 11:53:03 [27000] LOG: query: select prs_name from pg_ts_cfg > > where oid = $1 > > 2004-03-23 11:53:03 [27000] LOG: query: select lt.tokid, > > pg_ts_cfgmap.dict_name from pg_ts_cfgmap, pg_ts_cfg, token_type( $1 ) > as > > lt where lt.alias = pg_ts_cfgmap.tok_alias and pg_ts_cfgmap.ts_name = > > pg_ts_cfg.ts_name and pg_ts_cfg.oid= $2 order by lt.tokid desc; > > 2004-03-23 11:53:03 [27000] LOG: query: select oid from pg_ts_parser > > where prs_name = $1 > > 2004-03-23 11:53:03 [27000] LOG: query: select prs_start, > prs_nexttoken, > > prs_end, prs_lextype, prs_headline from pg_ts_parser where oid = $1 > > 2004-03-23 11:53:03 [27000] ERROR: fmgr_info: function 534990: cache > > lookup failed > > > > PostgreSQL 7.3.3 on i386-redhat-linux-gnu, compiled by GCC 2.96 > > > > I'm totallly stuck - I have seen pg postings which refer to droping > and > > re-creating SPs but I don't have the knowledge of tseach2 to > understand > > all the dependencies. > > Hope someone can help!! > > > > Cheers > > > > Mike > > > > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83