Re: [BUGS] BUG #1084: dropping in-use index causes "could not open relation

#1

I think the problem is similar to the FAQ item about using temp tables
in plpgsql functions. On your first plpgsql call, the function body is
compiled and cached for future calls. If the index is dropped, the
cached function doesn't know that and errors out. We really need some
dependency code to remember all the object referenced in a function, but
we don't have that yet.

---------------------------------------------------------------------------

PostgreSQL Bugs List wrote:

Quote:

The following bug has been logged online:

Bug reference: 1084
Logged by: Reece Hart

Email address: reece (AT) in-machina (DOT) com

PostgreSQL version: 7.4

Operating system: linux 2.4.18 (smp)

Description: dropping in-use index causes "could not open relation
with OID..."

Details:

Synopsis: I have a table which I access through two pl/pgsql functions
(essentially a set/get pair). While I had several concurrent operations
through those functions, I created one index and then dropped
another. Clients and the backend then logged "could not open relation with
OID 50491953" and all transactions stopped.

Speculation: My suspicion is that the plan for get function used the
dropped index and that this plan wasn't invalidated when the index was
dropped.

Details:
=>\d run_history
Table "unison.run_history"
Column | Type | Modifiers
--------------+-----------------------------+---------------
pseq_id | integer | not null
params_id | integer | not null
porigin_id | integer |
pmodelset_id | integer |
ran_on | timestamp without time zone | default now()
Indexes:
"run_history_pq" unique, btree (params_id, pseq_id)
WHERE ((porigin_id IS NULL) AND (pmodelset_id IS NULL))
"run_history_search_m" unique, btree (pseq_id, params_id,
pmodelset_id)
WHERE ((porigin_id IS NULL) AND (pmodelset_id IS NOT NULL))
"run_history_search_o" unique, btree (pseq_id, params_id, porigin_id)
WHERE ((porigin_id IS NOT NULL) AND (pmodelset_id IS NULL))
"run_history_search_om" unique, btree (pseq_id, params_id, porigin_id,
pmodelset_id)
WHERE ((porigin_id IS NOT NULL) AND (pmodelset_id IS NOT NULL))
"run_history_q" btree (pseq_id)
[snip]

The deleted index was
"run_history_search_q" btree (pseq_id)
(I just wanted to rename it to run_history_q... serves me right for
tinkering with index names.)

Upon dropping the run_history_search_q index, all clients died with:
! Unison::Exception:

BIError occurred: ERROR: could not open relation
with OID 50491953
and the backend said (once for each client):
ERROR: could not open relation with OID 50491953
CONTEXT: PL/pgSQL function "get_run_timestamp" line 8 at select into
variables

get_run_timestamp(integer,integer,integer,integer) is:
=> \df+ get_run_timestamp
[snip]
DECLARE
q alias for $1;
p alias for $2;
o alias for $3;
m alias for $4;
z timestamp;
BEGIN
select into z ran_on from run_history
where pseq_id=q
and params_id=p
and (case when o is null then true else porigin_id=o end)
and (case when m is null then true else pmodelset_id=m end);
return z;
END;

Indeed, OID 50491953 no longer exists in pg_class. From a backup I fished
out:
-- TOC entry 809 (OID 50491953)
-- Name: run_history_search_q; Type: INDEX; Schema: unison; Owner: unison
which shows that the missing oid is indeed the dropped index.

Thanks,
Reece

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] BUG #1084: dropping in-use index causes "could not open relation

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] BUG #1084: dropping in-use index causes "could not open relation - 03-02-2004 , 12:21 PM