pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Cache loolup failed problems with tsearch following pg_restore
4+ messages / 2 participants
[nested] [flat]

* Cache loolup failed problems with tsearch following pg_restore
@ 2004-03-23 13:47  Mike Bell <mike.bell@hashdefine.co.uk>
  0 siblings, 1 reply; 4+ messages in thread

From: Mike Bell @ 2004-03-23 13:47 UTC (permalink / raw)
  To: pgsql-admin

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Cache loolup failed problems with tsearch following
@ 2004-03-23 15:19  Oleg Bartunov <oleg@sai.msu.su>
  parent: Mike Bell <mike.bell@hashdefine.co.uk>
  0 siblings, 1 reply; 4+ messages in thread

From: Oleg Bartunov @ 2004-03-23 15:19 UTC (permalink / raw)
  To: Mike Bell <mike.bell@hashdefine.co.uk>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Cache loolup failed problems with tsearch following pg_restore
@ 2004-03-23 19:30  Mike Bell <mike.bell@hashdefine.co.uk>
  parent: Oleg Bartunov <oleg@sai.msu.su>
  0 siblings, 1 reply; 4+ messages in thread

From: Mike Bell @ 2004-03-23 19:30 UTC (permalink / raw)
  To: 'Oleg Bartunov' <oleg@sai.msu.su>; +Cc: pgsql-admin

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
>





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Cache loolup failed problems with tsearch following
@ 2004-03-24 11:11  Oleg Bartunov <oleg@sai.msu.su>
  parent: Mike Bell <mike.bell@hashdefine.co.uk>
  0 siblings, 0 replies; 4+ messages in thread

From: Oleg Bartunov @ 2004-03-24 11:11 UTC (permalink / raw)
  To: Mike Bell <mike.bell@hashdefine.co.uk>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-03-24 11:11 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-23 13:47 Cache loolup failed problems with tsearch following pg_restore Mike Bell <mike.bell@hashdefine.co.uk>
2004-03-23 15:19 ` Re: Cache loolup failed problems with tsearch following Oleg Bartunov <oleg@sai.msu.su>
2004-03-23 19:30   ` Mike Bell <mike.bell@hashdefine.co.uk>
2004-03-24 11:11     ` Re: Cache loolup failed problems with tsearch following Oleg Bartunov <oleg@sai.msu.su>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox