Cache lookup failure for pg_restore?

[Razvan Surdulescu]

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.

[Tom Lane]

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

[Tom Lane]

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

[Razvan Surdulescu]

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);

[Razvan Surdulescu]

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);

[Tom Lane]

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

[Razvan Surdulescu]

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)

[Razvan Surdulescu]

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.

[Razvan Surdulescu]

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

[Denis Braekhus]

-----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

[Oleg Bartunov]

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: [phone]

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

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