BUG #1084: dropping in-use index causes "could not open relation with OID..."

The following bug has been logged online:

Bug reference:      1084
Logged by:          Reece Hart

Email address:      reece@in-machina.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::DBIError 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