Thread: Query Help

Hi All,

I am getting an error I do not understand from the following setup

CREATE TABLE timesheet_booking
(
  timesheet_booking_id bigserial NOT NULL,
  operator_id integer,
  booking_item_id integer,
  "day" date NOT NULL,
  minutes integer NOT NULL,
  CONSTRAINT timesheet_booking_pkey PRIMARY KEY (timesheet_booking_id),
  CONSTRAINT timesheet_booking_booking_item_id_fkey FOREIGN KEY
(booking_item_id)
      REFERENCES booking_item (booking_item_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT timesheet_booking_operator_id_fkey FOREIGN KEY (operator_id)
      REFERENCES "operator" (operator_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL
)
WITHOUT OIDS;


CREATE UNIQUE INDEX timesheet_booking_unique_idx
  ON timesheet_booking
  USING btree
  (operator_id, booking_item_id, "day");

Now the timesheet booking contains the following data:

timesheet_booking_id, operator_id,booking_item_id,day,minutes
2;284;1;"2007-01-18";10
4;284;2;"2007-01-18";10

If I try the following query:
    delete from operator where operator_id=283;
I get the following error message:

ERROR: could not open relation with OID 438427
SQL state: XX000
Context: SQL statement "UPDATE ONLY "public"."timesheet_booking" SET
"operator_id" = NULL WHERE "operator_id" = $1"

If I try this sql statement directly it works OK. What does this error
mean and how can I get rid of it?

Thanks
Howard.

Howard Cole <howardnews@selestial.com> writes:
> If I try the following query:
>     delete from operator where operator_id=283;
> I get the following error message:

> ERROR: could not open relation with OID 438427
> SQL state: XX000
> Context: SQL statement "UPDATE ONLY "public"."timesheet_booking" SET
> "operator_id" = NULL WHERE "operator_id" = $1"

It looks like you have a stale plan for that ON DELETE SET NULL
constraint.  Was there perhaps an index on operator_id that you removed?
Postgres is not very good about flushing cached plans when you change
table schemas (something I hope will be fixed in 8.3).

If that is the problem, starting a fresh session would be enough to take
care of it.

            regards, tom lane

Tom Lane wrote:
> It looks like you have a stale plan for that ON DELETE SET NULL
> constraint.  Was there perhaps an index on operator_id that you removed?
> Postgres is not very good about flushing cached plans when you change
> table schemas (something I hope will be fixed in 8.3).
>
> If that is the problem, starting a fresh session would be enough to take
> care of it.
>
>             regards, tom lane
>
>

Thanks Tom. You were, as usual, correct!