Cache lookup failed for relation

David Clymer <david.clymer@vistashare.com> writes:

I've been seeing the following error in one database of ours:
"cache lookup failed for relation 7640518"

Always the same OID, or does it change?

The SQL that apparently triggers this is:
drop table if exists ns_e5461ae570429d0b7863cce9ef4d4ead;

Unfortunately, manual attempts to reproduce the issue have failed. In
normal operation, this statement is run as one of several parallel queries,
and the tables are by nature, short lived. That said, they are not
temporary tables.

Hm ... what are the parallel queries exactly? If you're doing something
like dropping both ends of a foreign-key linkage in parallel, I'd not be
very astonished by an error like this, especially not in 9.0.x. It'd be
basically a race condition between two sessions both locking the same
table, but by the time the second one gets the lock, the first one has
dropped the table. (Robert Haas has done some great work towards
eliminating this type of race condition lately, but it's sure not in
9.0.x.)

One other item of note: db #2 has recently had an OID wrap-around, which
makes me suspect that plays some part in this behavior.

I don't believe that theory at all.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/2/11 Tom Lane <tgl@sss.pgh.pa.us>:

David Clymer <david.clymer@vistashare.com> writes:

I've been seeing the following error in one database of ours:
"cache lookup failed for relation 7640518"

Always the same OID, or does it change?

The SQL that apparently triggers this is:
drop table if exists ns_e5461ae570429d0b7863cce9ef4d4ead;

Unfortunately, manual attempts to reproduce the issue have failed. In
normal operation, this statement is run as one of several parallel queries,
and the tables are by nature, short lived. That said, they are not
temporary tables.

Hm ... what are the parallel queries exactly? If you're doing something
like dropping both ends of a foreign-key linkage in parallel, I'd not be
very astonished by an error like this, especially not in 9.0.x. It'd be
basically a race condition between two sessions both locking the same
table, but by the time the second one gets the lock, the first one has
dropped the table. (Robert Haas has done some great work towards
eliminating this type of race condition lately, but it's sure not in
9.0.x.)

we can see same behave in 9.1

when you try drop some tables in parallel sessions

Regards

Pavel Stehule

One other item of note: db #2 has recently had an OID wrap-around, which
makes me suspect that plays some part in this behavior.

I don't believe that theory at all.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I'm confused by the error message. Is a cache miss an error condition?

Thanks
Peter
On Feb 11, 2013 6:22 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

=?UTF-8?B?UMOpdGVyIEtvdsOhY3M=?= <peter.dunay.kovacs@gmail.com> writes:

I'm confused by the error message. Is a cache miss an error condition?

Well, this isn't a "cache miss", it's more of a "there's no such OID in
the pg_class catalog" condition. Normally you see something more
user-friendly; but in the case of going to remove a cross-table linkage,
the code isn't expecting the other table to not be there, so you get a
pretty low-level error.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Feb 11, 2013 at 12:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Clymer <david.clymer@vistashare.com> writes:

I've been seeing the following error in one database of ours:
"cache lookup failed for relation 7640518"

Always the same OID, or does it change?

It appears that almost all instances reference a different OID.

The SQL that apparently triggers this is:
drop table if exists ns_e5461ae570429d0b7863cce9ef4d4ead;

Unfortunately, manual attempts to reproduce the issue have failed. In
normal operation, this statement is run as one of several parallel

queries,

and the tables are by nature, short lived. That said, they are not
temporary tables.

Hm ... what are the parallel queries exactly?

Sorry, that's our application level terminology. As far as postgres is
concerned they are just individual queries running at the roughly same time.

If you're doing something
like dropping both ends of a foreign-key linkage in parallel, I'd not be
very astonished by an error like this, especially not in 9.0.x. It'd be
basically a race condition between two sessions both locking the same
table, but by the time the second one gets the lock, the first one has
dropped the table. (Robert Haas has done some great work towards
eliminating this type of race condition lately, but it's sure not in
9.0.x.)

I don't think we are doing that, but it may be that two queries are
attempting to drop the same table "if exists". I'll have to look at that a
bit more.

The SERIALIZABLE isolation mode is being used in 9.0, and REPEATABLE READ
in 9.2, which should be the same thing, correct (eg. 9.0 serializable ~ 9.2
repeatable read)?

One other item of note: db #2 has recently had an OID wrap-around, which
makes me suspect that plays some part in this behavior.

I don't believe that theory at all.

Good to know.

-davidc

--
*David Clymer*
VistaShare
866-828-4782, ext. 828
www.VistaShare.com <http://www.vistashare.com/&gt;

[image: Facebook] www.facebook.com/vistashare
[image: Twitter] www.twitter.com/vistashare

On Mon, Feb 11, 2013 at 12:47 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

2013/2/11 Tom Lane <tgl@sss.pgh.pa.us>:

David Clymer <david.clymer@vistashare.com> writes:

I've been seeing the following error in one database of ours:
"cache lookup failed for relation 7640518"

Always the same OID, or does it change?

The SQL that apparently triggers this is:
drop table if exists ns_e5461ae570429d0b7863cce9ef4d4ead;

Unfortunately, manual attempts to reproduce the issue have failed. In
normal operation, this statement is run as one of several parallel

queries,

and the tables are by nature, short lived. That said, they are not
temporary tables.

Hm ... what are the parallel queries exactly? If you're doing something
like dropping both ends of a foreign-key linkage in parallel, I'd not be
very astonished by an error like this, especially not in 9.0.x. It'd be
basically a race condition between two sessions both locking the same
table, but by the time the second one gets the lock, the first one has
dropped the table. (Robert Haas has done some great work towards
eliminating this type of race condition lately, but it's sure not in
9.0.x.)

we can see same behave in 9.1

when you try drop some tables in parallel sessions

OK,
--
*David Clymer*
VistaShare
866-828-4782, ext. 828
www.VistaShare.com <http://www.vistashare.com/&gt;

[image: Facebook] www.facebook.com/vistashare
[image: Twitter] www.twitter.com/vistashare

On Mon, Feb 11, 2013 at 1:13 PM, David Clymer
<david.clymer@vistashare.com>wrote:

On Mon, Feb 11, 2013 at 12:47 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

2013/2/11 Tom Lane <tgl@sss.pgh.pa.us>:

David Clymer <david.clymer@vistashare.com> writes:

I've been seeing the following error in one database of ours:
"cache lookup failed for relation 7640518"

Always the same OID, or does it change?

The SQL that apparently triggers this is:
drop table if exists ns_e5461ae570429d0b7863cce9ef4d4ead;

Unfortunately, manual attempts to reproduce the issue have failed. In
normal operation, this statement is run as one of several parallel

queries,

and the tables are by nature, short lived. That said, they are not
temporary tables.

Hm ... what are the parallel queries exactly? If you're doing something
like dropping both ends of a foreign-key linkage in parallel, I'd not be
very astonished by an error like this, especially not in 9.0.x. It'd be
basically a race condition between two sessions both locking the same
table, but by the time the second one gets the lock, the first one has
dropped the table. (Robert Haas has done some great work towards
eliminating this type of race condition lately, but it's sure not in
9.0.x.)

we can see same behave in 9.1

when you try drop some tables in parallel sessions

OK, so perhaps the difference is purely due to the use of postgres < 9.2 on
one db.

-davidc

--
*David Clymer*
VistaShare
866-828-4782, ext. 828
www.VistaShare.com <http://www.vistashare.com/&gt;

[image: Facebook] www.facebook.com/vistashare
[image: Twitter] www.twitter.com/vistashare

2013/2/11 David Clymer <david.clymer@vistashare.com>

On Mon, Feb 11, 2013 at 1:13 PM, David Clymer <david.clymer@vistashare.com

wrote:

On Mon, Feb 11, 2013 at 12:47 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

2013/2/11 Tom Lane <tgl@sss.pgh.pa.us>:

David Clymer <david.clymer@vistashare.com> writes:

I've been seeing the following error in one database of ours:
"cache lookup failed for relation 7640518"

Always the same OID, or does it change?

The SQL that apparently triggers this is:
drop table if exists ns_e5461ae570429d0b7863cce9ef4d4ead;

Unfortunately, manual attempts to reproduce the issue have failed. In
normal operation, this statement is run as one of several parallel

queries,

and the tables are by nature, short lived. That said, they are not
temporary tables.

Hm ... what are the parallel queries exactly? If you're doing

something

like dropping both ends of a foreign-key linkage in parallel, I'd not

be

very astonished by an error like this, especially not in 9.0.x. It'd

be

basically a race condition between two sessions both locking the same
table, but by the time the second one gets the lock, the first one has
dropped the table. (Robert Haas has done some great work towards
eliminating this type of race condition lately, but it's sure not in
9.0.x.)

we can see same behave in 9.1

when you try drop some tables in parallel sessions

OK, so perhaps the difference is purely due to the use of postgres < 9.2
on one db.

yes, I have not 9.2 now, but on 9.3 you get user friendly message

NOTICE: table "foo" does not exist, skipping
DROP TABLE

Regards

Pavel

David Clymer <david.clymer@vistashare.com> wrote:

The SERIALIZABLE isolation mode is being used in 9.0, and
REPEATABLE READ in 9.2, which should be the same thing, correct
(eg. 9.0 serializable ~ 9.2 repeatable read)?

Correct.

In 9.0 SERIALIZABLE and REPEATABLE READ are exactly same.  In 9.1
and later REPEATABLE READ has not changed from 9.0, but
SERIALIZABLE has basically become REPEATABLE READ with extra
checking for serialization failures.

-Kevin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thank you, Tom!

Recently, as I wanted to drop a schema (PG 8.2), I had to wade through a
number of such messages and keep dropping rows in the pg_dependency table
with the OID specified in the messages. When I was finally able to drop the
schema, I wanted to drop the user (who previously owned the schema), but I
got the same message again. I can imagine that something may have gone
wrong in the schema at the application level, but how come I am getting the
same messages when trying to drop a user?

Thanks,

Peter

On Mon, Feb 11, 2013 at 6:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: