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

Hello Marc-Antoine,

On Thu, 2018-03-15 at 10:43 +0100, Marc-Antoine Nüssli wrote:
> Hi there,
> 
> 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



Without seeing your schema but assuming that this stream of updates
only affects a single table, have you considered adding a column type
boolean to that table default value true, and assuming that it could be
included in a surrogate primary key, when an "update" is to be
processed, lock that row, update the boolean to false and then insert a
new row?

You could then schedule a cron job to physically delete all rows where
the boolean is false and if required, insert them into an archive table
just in case you need to review the history.

This way, your application is useable 24/7.

Just a thought.

Cheers,
Rob