Is there a way to run transaction for the data in DB before some time point in Spring Boot?

Question

I do have Spring Boot application and PostgreSql DB.

I need to do some processing of the data, but for the data that was written before some certain time point, i.e. I need to run a transaction in Repeatable Read isolation level, but for the snapshot of some certain time (other writes to db could be going on at that time).

Is it possible in Spring Boot? if so anyone has example samples? I assume some snapshot point also should have been created on db side, right?

UPDATE: So it is possible in PosgreSql to create a snapshot id and then use it on another transaction, as it written here https://www.postgresql.org/docs/current/sql-set-transaction.html

To begin a new transaction with the same snapshot as an already existing transaction, first export the snapshot from the existing transaction. That will return the snapshot identifier, for example:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();
 pg_export_snapshot
---------------------
 00000003-0000001B-1
(1 row)

 Then give the snapshot identifier in a SET TRANSACTION SNAPSHOT command at the beginning of the newly opened transaction:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-0000001B-1';

How the same thing could be done in Spring Boot via hibernate?

Answer 1

This is how it could be done.

@Transactional(isolation = Isolation.REPEATABLE_READ) 
public void getSnapshot() {
        Session session = sessionFactory.getCurrentSession();
        String snapshotQuery = "SELECT pg_export_snapshot()";
        String snapshotValue = (String)session.createSQLQuery(snapshotQuery).uniqueResult();
        //save snapshotValue
        ...
    
    }

And then use it on another transaction

@Transactional(isolation = Isolation.REPEATABLE_READ)
public void setSnapshot(String snapshotValue) {
    Session session = sessionFactory.getCurrentSession();
    String setSnapshotQuery = "SET TRANSACTION SNAPSHOT '" + snapshotValue + "'";
    session.createSQLQuery(setSnapshotQuery).executeUpdate();
    ...

}

Need to note once the transaction that created that snapshot value was commited/rollbacked then any other request to set that transaction snapshot again will fail.