Обсуждение: 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

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:
>
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.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