Re: Cache lookup failure for index during pg_dump - Mailing list pgsql-bugs

Tom,

Thanks for the detailed explanation - I appreciate it.  If i understand cor=
rectly I should be able to just change the job to not drop and recreate the=
 indexes and the dump will run without the cache lookup error.  I'll take a=
 hit in performance reloading the table, but I like database dumps more tha=
n speed at this point.

Could pg_dump also take an AccessShared lock on the system tables to preven=
t DDL changes during the dump, thereby preventing this error?  Just a thoug=
ht...

Bob Lunney

--- On Fri, 2/19/10, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> From: Tom Lane <tgl@sss.pgh.pa.us>
> Subject: Re: [BUGS] Cache lookup failure for index during pg_dump
> To: "Bob Lunney" <bob_lunney@yahoo.com>
> Cc: pgsql-bugs@postgresql.org
> Date: Friday, February 19, 2010, 7:39 PM
> Bob Lunney <bob_lunney@yahoo.com>
> writes:
> > I have a job that summarizes some data from a table,
> truncates the table, drops the indexes, writes the
> summarized data back into the table, then recreates the
> indexes.=A0 The operations above are not in a single
> transaction, but separate statements executed by a
> script.=A0=A0=A0Easy, runs great, has for
> years.=A0=20
> > Recently the job takes a little longer to run and is
> still going when the database dump starts. That's when I
> started getting this:
>=20
> > ERROR:=A0 cache lookup failed for index 70424
>=20
> > My questions are: can making DDL changes during a dump
> cause this error?=A0 Are the queries used by pg_dump
> transactionally consistent, i.e. do they run in a
> transaction and get a single view of the database system
> catalogs?=A0 Other than finer coordination of jobs, how
> can this situation be avoided?
>=20
> It's a bit messy.=A0 pg_dump runs in a serializable
> transaction, so it
> sees a consistent snapshot of the database including system
> catalogs.
> However, it relies in part on various specialized backend
> functions like
> pg_get_indexdef(), and those things tend to run on
> SnapshotNow time, ie
> they look at the currently committed state.=A0 So it is
> possible to get
> this type of error if someone performs DDL changes while a
> dump is
> happening: pg_dump sees index 70424 still listed in the
> catalogs,
> so it asks about it, and the backend says "there is no such
> index",
> which there isn't anymore because somebody dropped it since
> pg_dump's
> transaction started.
>=20
> The window for this sort of thing isn't very large, because
> the first
> thing pg_dump does is acquire AccessShareLock on every
> table it intends
> to dump, and past that point it won't be possible for
> anyone to modify
> the table's DDL.=A0 But it can happen.
>=20
> The right fix for this is to make all those inquiry
> functions use the
> calling query's snapshot; but duplicating a lot of backend
> infrastructure is going to be a major pain in the rear, so
> the
> discussion has kind of petered out every time it's come up
> in the past.
>=20
> In practice, because pg_dump does lock out DDL changes for
> the bulk of
> its run, it's not a great idea to be scheduling
> DDL-changing jobs during
> your dumps anyhow.=A0 Most of the time they'll just get
> blocked till the
> dump finishes, and if they touch more than one table it's
> not at all
> unlikely for them to end up deadlocked against pg_dump's
> locks.=A0 A fix
> for the snapshot-timing problem wouldn't do a thing for
> that problem.
>=20
> So in short, the path of least resistance is to reschedule
> your dumps.
> Or reconsider whether you really need to drop and recreate
> those indexes
> --- could you use REINDEX instead?
>=20
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0
> regards, tom lane
>=20