Subject Delete visibility
Author Daniel Miller
Just had a problem come up in a database that was previously working - at least I thought it was.  I'm using PHP as the client.
Table Nodes has ID.
    Table Routes has foreign key NODE_ID.
        Table Elevations has foreign key ROUTE_ID.
The foreign keys have cascade update & delete.
As part of an update process, I first delete all routes & elevations for a given node.  Theoretically, I could rely on the cascade delete, but (using a stored procedure) for a given Node I identify the associated routes, then explicitly delete all elevations for the routes, then delete the routes.  And for convenience, the proc returns the node ID.
I call this procedure with an explicit read/write transaction and then commit the transaction. Then I proceed with inserting the new values.
Just ran into an issue where Firebird via PHP was reporting foreign key errors.  I tried a few debug lines, then examined my logic.  In my PHP client, I start a transaction, perform the deletions, then do the insert/updates, then commit.  The theory being a full all-or-nothing update.  But since that now wasn't working...thought I'd try explicitly committing the delete first, then performing the updates.
Same issue.  Now I'm really confused.  So...break out FlameRobin.  I perform the steps manually...
    select NID from NODES - gives me an one.
        select RID from ROUTES where NODE_ID = NID - gives me routes.
            select EID from ELEVATIONS where ROUTE_ID = RID gives me a lot.
Ok...everything's there.  Now execute:
    select NID from purge_routes(1234)
Now commit in that window.  Then execute the route & elevation queries again, and I shouldn't see anything...but I do!  Close them and run they're clear.  I haven't had this happen before - either I'm doing something stupid, or have I got some kind of corruption?