How to handle dangling references to deleted records

I have a table that has a field that references another table. In the referenced table I have deleted an entry (manually). In the main table some records will have dangling references to the deleted row as can be seen in the table viewer as just a link:

Screenshot 2020-04-22 at 17.35.52

When I iterate over the records in the main table I get an exception “anvil.tables.TableError: This row has been deleted”. This is correct of course once one realises that the record deletion is not cascading.

Here is a test app:https://anvil.works/build#clone:W4G4TLINOMWNX32I=FPD6OYIKQKFRW3PLHAV63AS2

Now for the question: How can I test for dangling record references? I could handle the exception, but I’d prefer to test the record. (The TableError is too broad an exception for me)
There is nothing in the API Docs (that I could find) about the table row class.

Finally a gotcha: I noticed that the display in the table viewer shows a deleted record as a valid reference *until you logout"! Only after logging in again one sees the empty references.

No suggestions to tackle this problem?

Best practice is prevention. Use a function to delete the row. This function can then remove the dangling references, too, before it returns.

It took some digging, but tables have a has_row function. An example appears in code under https://anvil.works/docs/how-to/crud-best-practice-guide#security .

2 Likes

The has_row looks useful but cannot solve my problem. It does not seem to work with stale references to deleted rows.

app_tables.referenced.has_row(some_stale_ref)

returns:

AttributeError: 'LiveObjectProxy' object has no attribute 'has_record'

and

I know you were asking for a way to handle this without handling the exception but this is what I do when an exception is ‘too broad’:

try:
    blah.cause_table_error()
except anvil.tables.TableError as err:
    if "This row has been deleted" in str(err):
        handle_exception_whatever()
    else:
        # or pass #or nothing
        # or print(str(err)) #whatever
        raise

ymmv, if there are an abundance of errors this could get slow.

1 Like

So, if this were wrapped in a function, it would look something like this (untested):

def table_has_row(table, possibly_stale_row_ref):
    """Returns a boolean."""
    try:
        return table.has_row(possibly_stale_row_ref)
    except anvil.tables.TableError as err:
        if "This row has been deleted" in str(err):
            return False  # row was stale
        raise
    except AttributeError as err:
        if "'LiveObjectProxy' object has no attribute 'has_record'" in str(err):
            return False  # row was stale
        raise

This is not feasible if the table is provided by another App.

When a table is associated with some logic and comes from another app and must be managed by functions defined in the other app, you can use the other app as a dependency and never work with the table directly.

Or better, make a third app designed to be a dependency which only manages that table.

It is not a fool proof solution, because you still have direct access to the table and could make a mess, but it’s more elegant and safe than requiring functions on every app that uses it.

I agree.

But apps grow organically, so one app “controls” a table and another app uses that table and has pointers to it. The second app cannot handle the deletion of records except react to them happening. That’s why a more robust way of detecting/handling dangling pointers would be welcome.

Currently I tend to not use links at all, and use ids and handle references myself.

I agree that Anvil cold do more and with your request.

I just wanted to add that I too use my own ids rather than linked columns because of the problem you mention and especially because they are much faster. Linked tables are often victims of the n+1 problem and trigger database queries when you don’t need them (and if you use them on the client side, they also trigger a round trip, but I don’t use database row objects on the client side at all).

Why not?

The only problem is discipline: you do have pointers, just pay attention not to use them :slight_smile:

And if you want to make the table manager app dependency cleaner and avoid those pointers, you could define a class that manages every access to the table. Then, all the apps that need that table will use the interface defined in that class and not break anything. The class will expose values, not database rows, so there is no way to break anything. The class could also call back some functions so the depending apps could react to row deletions or other events.

2 Likes

Not a solution but an alternate option - sometimes it is easier not to delete the row but rather mark it as ‘archived’.

The benefits being you don’t have to deal with deleted rows and it won’t break any code that relies on those rows. You also have the opportunity to restore rows that have been archived.

The downsides are that you have to add exclusion logic to your search functions (not difficult) and you also need to handle the rows that are still linked to archived data.

3 Likes

A facade Table manager class would be able to solve all this. But that requires quite a bit of upfront design. My Anvil apps tend to be quick (and perhaps dirty) apps.

1 Like

Is there a way to manually delete the dangling reference?

Setting it to None should do it. Or some other value of course.

Yeah that’s what I’m trying to do, but can’t figure out how to manually. When I double click on the table cell, I can only choose from the existing rows in the referenced table, but not None.

Try pressing the Delete key

5 Likes