Snapshot recovery or rolling back commited - Mailing list pgsql-general

I saw there was a question about a similar topic recently but my use case is quite different so there may be a different answer.

Roughly, I have a database which is updated by a single stream of updates (through jdbc), so I have a single write transaction at any time. However, sometimes I need to cancel some of the last updates in order to re-apply different updates, but the readers should always be able to query the most up-to-date state, including updates that could potentially be cancelled afteward.

In other words, I need to be able, at any time, to rollback the last updates up to a certain point (which is moving but always known) and to be able to query the most up-to-date state (including updates that could be rollbacked later) 

Putting differently, I need two version of the same database, "consistent" and "latest", with the "consistent" version being some updates behind latest (or sometimes at the same state) and sometimes the "latest" version must be restored back to the "consistent" version.

An approach would be to have a single transaction for the updates with a "moving" savepoint, so that we can always rollback to the last "correct" state. But, as far as I know, there is no way to query the updated snapshot of an uncommitted transaction outside of it. 

Indeed, we cannot do READ UNCOMMITED transaction and exporting transaction snapshot  does not show the updates made by the original trnasaction.

The more I think and read about this, the more it seems this cannot be achieved within a MVCC architecture...

I also thought of a different approach that would use PITR and WAL features, but as far as I read about it, this would require a lot of filesytem-level scripting as well as as restarting postgres each time we want to restore to a previous state. This sounds quite difficult to accomplish all of this automatically and ot very efficient for a production system.

The last solution I thought of is to use an audit history (such as in https://wiki.postgresql.org/wiki/Audit_trigger_91plus). Then I can use it to rollback the last changes. And as I know up to which point I want to restore, I can periodically clean the history to keep only the last required changes. Currently, this is the only solution that seems doable to me....but I'm not 100% sure that it would capture all possible cases and that in some situations, restoring from the audit history will not restore to the exact same state that it was...

So, my question is do you think any of the proposed approaches is doable? And if yes, which one and how?

Otherwise, do you have any thought on how to accomplish such a kind of use-case using postgres?

Thanks in advance for your response!

Best regards,

Marc-Antoine Nüssli