DETAIL: User query might have needed to see row versions that must be removed.

[gumdropsteve]

issue

some queries I submit are frequently canceled. other times they work.

error output

TransactionRollbackError: canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

Click here to see full error

```
---------------------------------------------------------------------------
TransactionRollbackError                  Traceback (most recent call last)
/usr/local/lib/python3.7/dist-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1680         try:
-> 1681             cur.execute(*args, **kwargs)
   1682             return cur

TransactionRollbackError: canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

During handling of the above exception, another exception occurred:

TransactionRollbackError                  Traceback (most recent call last)
5 frames
TransactionRollbackError: terminating connection due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
SSL connection has been closed unexpectedly

The above exception was the direct cause of the following exception:

DatabaseError                             Traceback (most recent call last)
/usr/local/lib/python3.7/dist-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1688                     f"Execution failed on sql: {args[0]}\n{exc}\nunable to rollback"
   1689                 )
-> 1690                 raise ex from inner_exc
   1691 
   1692             ex = DatabaseError(f"Execution failed on sql '{args[0]}': {exc}")

DatabaseError: Execution failed on sql: 
        SELECT
            DATE_TRUNC('day', TO_TIMESTAMP(DIV(transactions.block_timestamp, 1000*1000*1000))) AS date,
            COUNT(*) AS transactions_count_by_date
        FROM 
            transactions
            GROUP BY 
                date
            ORDER BY 
                date

canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

unable to rollback
```

after that error occurs, i either get

InterfaceError: connection already closed

or it runs (rare without reconnecting), or i get the same error, and then get the interface error

code to reproduce the issue

https://gist.github.com/gumdropsteve/96b004c1a4436b481ce68cfd0a285ecc (can be run in colab)

[telezhnaya]

Hi @gumdropsteve!

Try adding WHERE clause to the query that will exclude today's lines, works for me:

SELECT
            DATE_TRUNC('day', TO_TIMESTAMP(DIV(transactions.block_timestamp, 1000*1000*1000))) AS date,
            COUNT(*) AS transactions_count_by_date
        FROM 
            transactions
            WHERE transactions.block_timestamp < 1626739200000000000
            GROUP BY 
                date
            ORDER BY 
                date;

The data is streamed into the DB in real-time, and some data could be updated. It's not a 100% working solution, but if we stop touching the freshest lines, the query will not use the data that will be probably updated.

[gumdropsteve]

Hi @telezhnaya , This worked!

Do you know to get the current date or block_timestamp for the WHERE so I could avoid having to input it manually?

I thought I had it the other day but can't find the query any more :/

[telezhnaya]

@gumdropsteve you can use subselect and ask for the latest line in transactions, you have a timestamp there. Then use arithmetics to find 00.00 UTC (subtract the div by number of nanoseconds in a day)