On Wed, 6 Oct 1999, Mike Mascari wrote:
> [SNIP]
>
> Now do another update (in the same session):
>
> update employee set emp_id=6;
>
> Followed by a select:
>
> select * from emp_expense;
>
> What's the emp_id value?
doesnt even get that far:
caffeine=> update employee set emp_id=6;
UPDATE 1
(emp_expense updated accordingly)
caffeine=> update employee set emp_id=3;
ERROR: expense_empid_fk: tuple references non-existing key in employee
(emp_expense not updated; fki breaks, whole countries are washed away)
> In the version of refint.c which was release with
> 6.5.0, there was an error because after the first
> cascading update trigger was executed, the plan was
> saved (which includes the value of the foreign key
> to be updated), since the cascading update code was
> simply the cascading delete code. Saving the SPI
> plan for deletes is fine, but for updates it can
> cause either (a) the wrong value to updated or
> (b) an insertion of new rows.
>
> Perhaps this has been fixed, but I doubt it.
i havent had a chance to grab 6.5.2 ( latest, iirc ) yet and test this
out. actually, i suppose that after check_foreign_key()
completes it should discard its plan, yesno ? if so, ( and i havent done
any SPI stuff just yet ), the 'fix' would be to insert the proper function
call inside of check_foreign_key's "* Ok, execute prepared plan(s)." loop.
a quick lookie at programmer/spi-spisaveplan.htm ( grin ) doesnt show any
SPI_forgetPlan() functions, though. and SPI_saveplan() "saves a passed
plan (prepared by SPI_prepare) in memory protected from freeing by
SPI_finish...", which is bad in this situation.
so the fix is to wait for 6.6, which hopefully wont have this problem
since refint's functionality should be inside of the backend proper, or
dont do two cascading updates at the same time, beating users on the head
with large books and/or unused machines if they do so.
---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
"Just think how much deeper the ocean would be if sponges didn't live there."