Best way to keep only last NNN rows of a table

Anvil Q&A
1

What I’m trying to do:
I have some tables I use to keep an hystorical track of some activity.
I don’t want DB to explode, so say I want to keep only last 1000 rows of that table.

What I’ve tried:
Currently, I’m doing this in this way:

def make_db_housekeeping():
    max_rows = 1000
    # insert order guaranteed?
    my_rows = app_tables.my_table.search()
    num_rows = len(my_rows )
    if num_rows > max_rows :
        to_delete = num_rows - max_rows 
        deleted = 0
        for row in my_rows :
            row .delete()
            deleted += 1
            if deleted == to_delete:
                break

I find this extremely slow and unelegant, is there a better way to do that?

Thanks

2

How about something like (completely untested):

from anvil.tables import app_tables, in_transaction

MAX_ROWS = 1000


@in_transaction
def make_db_housekeeping():
    rows = app_tables.my_table.search()
    for row in rows[:len(rows) - MAX_ROWS]:
        row.delete()
1 Like
3

Thanks Owen, I didn’t think at transactions, that’s a good idea I’ll give it a try.

4

Don’t forget to explicitly add the order. Don’t rely on the search returning older rows first.

Something like rows = app_tables.my_table.search(tables.order_by('timestamp', ascending=True))

4 Likes
5

I followed your suggestion adding the in_transaction decorator (but keeping the rest of my original code structure, for lazyness I guess :slight_smile:):

@tables.in_transaction()        
def make_db_housekeeping():
    # cut tables to max dimensions
    max_documenti = 1000
    # insert order guaranteed? TODO: add a date field or a counter
    documenti = app_tables.esaw_files_buffer.search()
    num_docs = len(documenti)
    if num_docs > max_documenti:
        to_delete = num_docs - max_documenti
        deleted = 0
        for doc_row in documenti:
            doc_row.delete()			#	--> this is row 1140
            deleted += 1
            if deleted == to_delete:
                break

It worked seamlessly since then until 2 days ago.
Since 2 days ago I am consistently getting the error

anvil.tables.TransactionConflict: Another transaction has changed this data; aborting
<running on the server>
called from /downlink/anvil/_server.py, line 42
called from server_module, line 1140

This code is executed by a background task, nightly, at 03.00 am, without any concurrency.
No other task is running at that time and no front-end user too.
esaw_files_buffer is a 1000 rows table (now 1006, since last 3 runs failed).

Main question: I can’t understand this error.

Side questions:

  1. I see I am having a consistent number of DB errors since 1 month ago (approx)… code that never gave such problems… I guess something has changed in DB on Anvil’s side, can someone confirm that?

  2. Am I the only one here having this brand new set of DB errors on consolidated, stable production code?

Thanks and BR

6

Perhaps you have the same background task running twice, and each instance causes the other to fail?

7

Hi @stefano.menci
and thanks for answering.
These are the scheduled tasks:
chrome_s6vCkJLZmZ

sentinella is the task doing the job,
problems_notifier just checks if the sentinella's semaphore has been cleared or - if not - notifies me sentinella didn’t run completely 'till the end and there is a problem.

Once UTC there can’t be two tasks running.
This is the APP logs since yesterday:

chrome_AtfQ0BrGFJ
chrome_AtfQ0BrGFJ925×129 12.6 KB

No user session / other task running ad 05.00 CEST.
08.50 CEST is me checking what went wrong.

8

Your code is looping over every record in the table and deciding whether to delete or not for each one. That’s potentially a lot of redundant processing.

In the code I suggested, the loop is only over those records that will be deleted.

It’s not necessarily the cause of the problem you’re seeing but one mantra of transaction handling is to keep the work within them to a minimum.

You could reduce the scope of the transaction further by splitting the function in two - one that does the table search and then calls the other to do the deletion. Only the second of those would be decorated as within a transaction.

1 Like
9

Hi @owen.campbell and thanks.

No it’s not.
Supposing the search is giving me the records in the order I want delete them (the older first), if the table has 1010 records I am looping on the first 1010-1000=10 search results then I exit the loop.

That’s a good suggestion.
What would be the difference between splitting the function in two functions (as you suggest) and using a context manager like:

with anvil.tables.Transaction() as txn:

in the same function? I guess the decorator adds some management (like the 5-times retry) for free, but I am not sure.

10

All described in the docs

1 Like
11

OK. The comment from @stefano.menci applies to us both then - don’t forget the order by or there is no guarantee of that.

2 Likes