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