Thread: Cache lookup failure for pg_restore?
After I restore a Postgres database (using pg_restore), I get the
following error message when I try to run a simple UPDATE query:
ERROR: cache lookup failed for function 70529
More details:
1. I backup the database:
pg_dump -Fc --username=webclient [dbname] > database.backup
2. I restore the database:
dropdb -U postgres [dbname]
createdb -U postgres [dbname]
createlang -U postgres plpgsql [dbname]
pg_restore -v -U postgres -Fc -d [dbname] database.backup
3. I connect to the database and run a query:
$ psql cspan webclient
Welcome to psql 7.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
cspan=> update contact set gender = 'M';
ERROR: cache lookup failed for function 70529
If I re-run the update, I get:
cspan=> update contact set gender = 'M';
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
Any ideas what might be happening here? This is all running on a
RedHat linux box. The original database (before backup/restore) works
fine. The database has a few GIST (full-search, tsearch2) indices, but
otherwise is pretty ordinary.
Thanks in advance for any insight,
Razvan.
surdules@yahoo.com (Razvan Surdulescu) writes:
> cspan=> update contact set gender = 'M';
> ERROR: cache lookup failed for function 70529
> If I re-run the update, I get:
> cspan=> update contact set gender = 'M';
> server closed the connection unexpectedly
Hm, could we see the full schema for the "contact" table?
> The database has a few GIST (full-search, tsearch2) indices, but
> otherwise is pretty ordinary.
Any of those on "contact"?
What would be useful to look at is a debugger stack trace from the core
dump...
Also it might be worth your time to update to 7.4.2, just to see if this
is an already-fixed bug.
regards, tom lane
Tom Lane wrote:
> surdules@yahoo.com (Razvan Surdulescu) writes:
>
>>cspan=> update contact set gender = 'M';
>>ERROR: cache lookup failed for function 70529
>
>>If I re-run the update, I get:
>
>>cspan=> update contact set gender = 'M';
>>server closed the connection unexpectedly
>
> Hm, could we see the full schema for the "contact" table?
Sure, I copy/pasted it at the end of the message: it's a bit long.
>>The database has a few GIST (full-search, tsearch2) indices, but
>>otherwise is pretty ordinary.
>
> Any of those on "contact"?
Yes, one of them, see the contact schema below.
> What would be useful to look at is a debugger stack trace from the core
> dump...
Hmm, I cannot find a core file from the crash. I looked in the
postgres/bin directory and in the current directory, but no luck.
> Also it might be worth your time to update to 7.4.2, just to see if this
> is an already-fixed bug.
I will look into that, but it would be non-trivial, I am hoping that
this bug might be faster to resolve without upgrading? Perhaps there is
something I am doing wrong with the backup or restore process?
Thanks,
Razvan.
CREATE TABLE contact (
id serial NOT NULL,
member_id integer NOT NULL REFERENCES member(id),
member_contact bool NOT NULL default false,
uploaded bool NOT NULL default false,
private bool NOT NULL default true,
nametitle varchar(5),
firstname varchar(50) NOT NULL,
middlename varchar(50),
lastname varchar(50) NOT NULL,
suffix varchar(10),
gender varchar(7),
profheadline varchar(255),
summary varchar(255),
announs varchar(255),
assmemb varchar(255),
appkeywords varchar(255),
street1 varchar(255),
street2 varchar(255),
city varchar(50),
state varchar(50),
zip varchar(15),
country varchar(50),
cellphone varchar(20),
busphone1 varchar(20),
busphone2 varchar(20),
busfax varchar(20),
asstphone varchar(20),
pager varchar(20),
email varchar(80) NOT NULL,
highschool varchar(255),
college varchar(255),
colldegree varchar(255),
gradschool varchar(255),
graddegree varchar(255),
homephone varchar(20),
homecity varchar(50),
homestate varchar(50),
homecountry varchar(50),
lang1 varchar(25),
lang2 varchar(25),
lang3 varchar(25),
lang4 varchar(25),
interests varchar (255),
georgs varchar(255),
keywords text,
-- This is the full text index field.
-- It is updated by the trigger below.
keywordsFTI tsvector,
PRIMARY KEY (id)
);
CREATE INDEX idx_contact_member_id ON contact(member_id);
CREATE INDEX idx_keywordsFTI ON contact USING gist(keywordsFTI);
CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON contact
FOR EACH ROW EXECUTE PROCEDURE tsearch2(keywordsFTI, keywords);
Razvan Surdulescu <surdules@yahoo.com> writes:
>>> cspan=> update contact set gender = 'M';
>>> ERROR: cache lookup failed for function 70529
>>> The database has a few GIST (full-search, tsearch2) indices, but
>>> otherwise is pretty ordinary.
>> Any of those on "contact"?
> Yes, one of them, see the contact schema below.
I recall some discussion recently about the FTI code getting confused
with this sort of symptom resulting; you might want to check the
archives.
>> What would be useful to look at is a debugger stack trace from the core
>> dump...
> Hmm, I cannot find a core file from the crash. I looked in the
> postgres/bin directory and in the current directory, but no luck.
The core would be in $PGDATA/base/yourdbOID/. If you don't see one,
it's likely because the postmaster is being started under "ulimit -c 0".
Restart it with "ulimit -c unlimited" and reproduce the problem and
you should get a core.
>> Also it might be worth your time to update to 7.4.2, just to see if this
>> is an already-fixed bug.
> I will look into that, but it would be non-trivial,
Shouldn't be; you won't have to dump and reload, just install new
software and restart postmaster. It's only cross-major-version updates
that are painful.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Razvan Surdulescu wrote: | After I restore a Postgres database (using pg_restore), I get the | following error message when I try to run a simple UPDATE query: | | ERROR: cache lookup failed for function 70529 Hi Razvan, Just to add to what Tom has already said, this is most certainly because of your tsearch/gist usage. Check the Tsearch2 site [1] for a lot of interesting documentation on Tsearch2. For a quite nice howto on backups and restores of databases with Tsearch2 see the Tsearch2 Intro document [2] There is actually now a patch [3] to tsearch (only for 7.4 though) which is supposed to improve dumping and reloading of tsearch2 databases. [1] http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ [2] http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html [3] http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/regprocedure_7.4.patch.gz Best Regards - -- Denis -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2-nr2 (Windows XP) iD8DBQFAniUSvsCA6eRGOOARAtJpAKCt4Wcrea3bIxu8fXw/5ZNFACdohwCfZPDf UuCk1dXLx8SCS4/qMniC2z4= =871m -----END PGP SIGNATURE-----
Recently, we discover how to avoid problem with OIDs backup/restore in tsearch2. Check http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ for a little patch (regprocedure_7.4.patch.gz). It won't work on existed tsearch2 installation, though, but will help in future. Oleg On Sun, 9 May 2004, Denis Braekhus wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Razvan Surdulescu wrote: > > | After I restore a Postgres database (using pg_restore), I get the > | following error message when I try to run a simple UPDATE query: > | > | ERROR: cache lookup failed for function 70529 > > Hi Razvan, > > Just to add to what Tom has already said, this is most certainly because > of your tsearch/gist usage. Check the Tsearch2 site [1] for a lot of > interesting documentation on Tsearch2. > For a quite nice howto on backups and restores of databases with > Tsearch2 see the Tsearch2 Intro document [2] > > There is actually now a patch [3] to tsearch (only for 7.4 though) which > is supposed to improve dumping and reloading of tsearch2 databases. > > [1] http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ > [2] > http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html > [3] > http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/regprocedure_7.4.patch.gz > > Best Regards > - -- > Denis > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.2.2-nr2 (Windows XP) > > iD8DBQFAniUSvsCA6eRGOOARAtJpAKCt4Wcrea3bIxu8fXw/5ZNFACdohwCfZPDf > UuCk1dXLx8SCS4/qMniC2z4= > =871m > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > 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
Hello, > Recently, we discover how to avoid problem with OIDs backup/restore > in tsearch2. Check > http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ for > a little patch (regprocedure_7.4.patch.gz). It won't work on > existed tsearch2 > installation, though, but will help in future. I do have the same problem, after reimporting with the correct command sequence, with no errors (I edit the schema to get rid of duplicate functions) , ( AND: DELETE from pg_ts_dict; DELETE from pg_ts_parser ; DELETE from pg_ts_cfg; DELETE from pg_ts_cfgmap ; to avoid duplicate keys - that point is missing in the howto ?) I can connect to the DB , but cannot use the tsquery function e.g. . Example: www2=# select set_curcfg('default'); ERROR: cache lookup failed for function 138031386 www2=# select to_tsquery('cd&rohling') ; ERROR: could not find tsearch config by locale www2=# select set_curcfg('default'); set_curcfg ------------ (1 row) www2=# select to_tsquery('cd&rohling') ; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. ! --- I think this is the same problem. The patch is for 7.4.[0|1|2] , not 7.4, i guess. Is there a chance to rebuild a backup from a DB , which runs tsearch2 with the unpatched tsearch2-schema ? My idea: Isn't this a thing to be mentioned in the docs ? Restoring a DB could be a point :-) Andre
Hi, Below is a email from Andrew Kopciuch who tested the patch and wrote some instruction. sql file is available from http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/regprocedure_update.sql Andrew, could you help Andre with his problem ? Oleg ---------------------------------------------------------------------- Date: Mon, 10 May 2004 16:24:36 -0600 From: Andrew J. Kopciuch <akopciuch@bddf.ca> To: Oleg Bartunov <oleg@sai.msu.su> Subject: Re: patch for tsearch2 is available, please test Oleg: > Andrew, could you, please, test patch and write instruction ? I've done some testing with this patch : > I tried it myself and it works well, backup/restore works fine. > I think, the main problem will be upgrade of tsearch2 and restore data. > I dump sql and data separately, then install new tsearch2 with patch, > created db, load tsearch2.sql, edit tsearch2 configuration and load data. > After that, dump/reload should works fine. I have created an SQL file (attached) that I used to simply alter the current table definitions, and update the data prior to dumping. This way the upgrade is instant ... and from this point on ... simply dumping the database and restoring can be done in typical fashion. pg_dump dbname > dbname.sql createdb dbname psql dbname < dbname.sql Could you look through the file jsut to double check. It may be useful to other people for upgrading. I will go through the Introduction and make some modifications when I can later tonight ... or tomorrow. I will add sections regarding the patch, and leave the current documentation (I don't know why anyone _wouldn't_ apply the patch ... but nothing would surprise me). I will also add instructions about the dump / restore now. It's just like any other DB dump and restore procedure. Andy ---------------------------------------------------------------------------- On Wed, 12 May 2004, Gellert, Andre wrote: > Hello, > > > Recently, we discover how to avoid problem with OIDs backup/restore > > in tsearch2. Check > > http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ for > > a little patch (regprocedure_7.4.patch.gz). It won't work on > > existed tsearch2 > > installation, though, but will help in future. > > I do have the same problem, after reimporting with the correct command > sequence, > with no errors (I edit the schema to get rid of duplicate functions) , > ( AND: > DELETE from pg_ts_dict; > DELETE from pg_ts_parser ; > DELETE from pg_ts_cfg; > DELETE from pg_ts_cfgmap ; > to avoid duplicate keys - that point is missing in the howto ?) > > I can connect to the DB , but cannot use the tsquery function e.g. . > > Example: > www2=# select set_curcfg('default'); > ERROR: cache lookup failed for function 138031386 > www2=# select to_tsquery('cd&rohling') ; > ERROR: could not find tsearch config by locale > www2=# select set_curcfg('default'); > set_curcfg > ------------ > > (1 row) > > www2=# select to_tsquery('cd&rohling') ; > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > ! > --- > I think this is the same problem. The patch is for 7.4.[0|1|2] , not 7.4, i > guess. > > Is there a chance to rebuild a backup from a DB , which runs tsearch2 with > the unpatched tsearch2-schema ? > My idea: Isn't this a thing to be mentioned in the docs ? Restoring a DB > could be a point :-) > > Andre > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > 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