473,322 Members | 1,719 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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.
Nov 23 '05 #1
10 3430
su******@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2
su******@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3

Tom Lane wrote:
su******@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);

Nov 23 '05 #4

Tom Lane wrote:
su******@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);

Nov 23 '05 #5
Razvan Surdulescu <su******@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #6

Tom Lane wrote:
Razvan Surdulescu <su******@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.


OK, I will look in the archives. Do you have some specific keywords I
should search for?
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.


I got the core, thanks, see the gdb output below.
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.


Thanks, I will look into that as well.

Razvan.

$ gdb ../../../bin/postmaster core.3080
GNU gdb 6.1
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you
are welcome to change it and/or distribute copies of it under certain
conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB. Type "show warranty" for details.
This GDB was configured as "i686-pc-linux-gnu"...Using host libthread_db
library "/lib/tls/libthread_db.so.1".

Core was generated by `postgres: webclient testrestore [local] UPDATE '.
Program terminated with signal 11, Segmentation fault.
Reading symbols from /usr/lib/libz.so.1...done.
Loaded symbols for /usr/lib/libz.so.1
Reading symbols from /usr/lib/libreadline.so.4...done.
Loaded symbols for /usr/lib/libreadline.so.4
Reading symbols from /lib/libtermcap.so.2...done.
Loaded symbols for /lib/libtermcap.so.2
Reading symbols from /lib/libcrypt.so.1...done.
Loaded symbols for /lib/libcrypt.so.1
Reading symbols from /lib/libresolv.so.2...done.
Loaded symbols for /lib/libresolv.so.2
Reading symbols from /lib/libnsl.so.1...done.
Loaded symbols for /lib/libnsl.so.1
Reading symbols from /lib/libdl.so.2...done.
Loaded symbols for /lib/libdl.so.2
Reading symbols from /lib/tls/libm.so.6...done.
Loaded symbols for /lib/tls/libm.so.6
Reading symbols from /lib/tls/libc.so.6...done.
Loaded symbols for /lib/tls/libc.so.6
Reading symbols from /lib/ld-linux.so.2...done.
Loaded symbols for /lib/ld-linux.so.2
Reading symbols from /lib/libnss_files.so.2...done.
Loaded symbols for /lib/libnss_files.so.2
Reading symbols from /cspan/sandbox/postgresql/lib/tsearch2.so...done.
Loaded symbols for /cspan/sandbox/postgresql/lib/tsearch2.so
#0 0x00000000 in ?? ()
(gdb) where
#0 0x00000000 in ?? ()
#1 0x081a7cfe in FunctionCall2 ()
#2 0x40d424c0 in parsetext_v2 ()
from /cspan/sandbox/postgresql/lib/tsearch2.so
#3 0x40d44eb4 in tsearch2 () from /cspan/sandbox/postgresql/lib/tsearch2.so
#4 0x080dc7fb in ExecCallTriggerFunc ()
#5 0x080dd079 in ExecBRUpdateTriggers ()
#6 0x080ed111 in ExecUpdate ()
#7 0x080eca7d in ExecutePlan ()
#8 0x080ebe52 in ExecutorRun ()
#9 0x0814d82e in ProcessQuery ()
#10 0x0814e168 in PortalRunMulti ()
#11 0x0814dc04 in PortalRun ()
#12 0x0814af14 in exec_simple_query ()
#13 0x0814cf7c in PostgresMain ()
#14 0x0812d127 in BackendFork ()
#15 0x0812cc1a in BackendStartup ()
#16 0x0812b54b in ServerLoop ()
#17 0x0812af2b in PostmasterMain ()
#18 0x08102d2c in main ()
(gdb)

Nov 23 '05 #7

Tom Lane wrote:
Razvan Surdulescu <su******@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.


I did some searches for:

"cache lookup failed" full text index
"cache lookup failed" fti
"cache lookup failed for function"

but I couldn't find anything recent and/or similar to the problem I
encountered below.

Am I searching for the right thing?
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.


I upgraded to 7.4.2, no luck: same problem/stack trace.

Razvan.

Nov 23 '05 #8
I don't know if this is any help, but if I create the database and
schema by hand and just restore the data into it, everything works fine.
However, if I let the backup script both create the database and restore
the schema and data, the bug manifests itself.

Perhaps it is some sort of ordering problem in the auto-generated
restore script?

Razvan.

Tom Lane wrote:
Razvan Surdulescu <su******@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend


Nov 23 '05 #9
-----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/postgr...-V2-intro.html
[3]
http://www.sai.msu.su/~megera/postgr...e_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

Nov 23 '05 #10
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/postgr...-V2-intro.html
[3]
http://www.sai.msu.su/~megera/postgr...e_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: ol**@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Joshua D. Drake | last post by:
Alright, we are testing pg_restore while restoring a 7GB database. This database has about 6GB of large objects and about 1Gb of textual data. The problem has been verified on PostgreSQL 7.3.2,...
14
by: Ron Johnson | last post by:
Hi, While on the topic of "need for in-place upgrades", I got to think- ing how the pg_restore could be speeded up. Am I wrong in saying that in the current pg_restore, all of the indexes are...
7
by: Juris Krumins | last post by:
I have a problem with postgresql tables. periodicaly, I would say frequently about 5-10 time per hour i have such errors in my server log file: 2004-04-14 12:23:32 ERROR: cache lookup of...
0
by: Razvan Surdulescu | last post by:
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:...
3
by: Sven Willenberger | last post by:
Created a pg_dump with Fc (custom format compression) that resulted in a 300+MB file. Now trying to pg_restore this thing fails with either an out of memory error (as in the subject line) on...
7
by: Tim Penhey | last post by:
Maybe it's just me, but I can't seem to get pg_restore to restore a database... I am running 8.0 beta 2 (using the dev3 installer) on Windows XP. I created a very simple database with one...
1
by: ruben | last post by:
Hi: I'm trying to dump tableA and restore it to tableB: $ ./pg_dump -Fc -t tableA databaseA -f tableA.dump -v $ ./pg_restore -t tableB -d databaseA tableA.dump -v pg_dump creates...
9
by: Michael M. | last post by:
Hi all, I would like to know how to access the NT/2000/XP/2003 Name cache; what I mean by this is: Open a Command Prompt and.., C:\> C:\>IPCONFIG /DISPLAYDNS
4
by: MonkeeSage | last post by:
Proposal: When an attribute lookup fails for an object, check the top-level (and local scope?) for a corresponding function or attribute and apply it as the called attribute if found, drop...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.