Implement restore points (vaguely similar to Oracle)

[Enno Thieleke]

Hello H2 group,

as the subject implies, I want to suggest a restore point feature for H2.

The main motivation I have for it is this: H2 is heavily used in integration tests in undoubtedly thousands of Java projects all over the planet. It would be great if it were possible to populate a database once (e.g. with Flyway or Liquibase), create a restore point, run a test that actually commits data (instead of just rolling back, which is kinda the default in many setups), and then restore to the point before the test. This way one could test commit behavior of an application without the need to recreate a database from scratch for different tests.

I've actually implemented this already as some sort of PoC. You can find the code (with tests) here: https://github.com/i-am-not-giving-my-name-to-a-machine/h2database/tree/feature/implement-restore-points

I'm curious to read your opinion about the feature and would like to know if it has any chance of making it into H2. If it has a chance, then I'm happy to create a pull request for the changes I've made and would appreciate any feedback on how I could improve the current implementation.

And don't worry if you think it's a bad idea and the changes won't make it into H2. It was still a good exercise.

Kind regards

Enno

[Noel Grandin]

Hi

> I've actually implemented this already as some sort of PoC. You can find the code (with tests) here: https://github.com/

> i-am-not-giving-my-name-to-a-machine/h2database/tree/feature/implement-restore-points <https://github.com/i-am-not-
> giving-my-name-to-a-machine/h2database/tree/feature/implement-restore-points>
>

At a first glance, that looks pretty good, I'm sure it would need some tweaks, but it seems like a reasonable thing to
include in H2.

Regards, Noels.

[Evgenij Ryazanov]

Hello!

This feature is nice, but there are significant problems in your implementation.

1. I think it is a very bad idea to implement it on top of oldestVersionToKeep from MVStore. MVStore by itself creates a lot of garbage and this setting will effectively prevent garbage collection. In case of H2, all these versions also can be lost because H2 can rewrite the whole storage during shutdown. MVStore is actually a third storage engine for H2 and who knows, maybe there will be a new one without this setting or MVStore itself will be changed incompatibly. I think you need to find some other way.
2. Your implementation seems to be not compatible with anything, including Oracle. You shouldn't use mixed syntax partially taken from Oracle, partially your own, because it will prevent introduction of Oracle-specific features in Oracle compatibility mode due to syntax conflict.

You also should use the same code style as the whole project. Please, don't use var and don't invent new interfaces for unit tests.

I tried to add some reconnections to your tests and they started to fail. You need to test all these cases too.

[Noel Grandin]

On 11/8/2024 11:32 AM, Evgenij Ryazanov wrote:
>
> 1. I think it is a very bad idea to implement it on top of oldestVersionToKeep from MVStore. MVStore by itself creates a
> lot of garbage and this setting will effectively prevent garbage collection. In case of H2, all these versions also can
> be lost because H2 can rewrite the whole storage during shutdown. MVStore is actually a third storage engine for H2 and
> who knows, maybe there will be a new one without this setting or MVStore itself will be changed incompatibly. I think
> you need to find some other way.

I think the strategy there is sufficient - we don't need to support all features with all backends.
We can just throw an exception in the future if this feature is used with a backend which does not support it.

The integration with oldestVersionToKeep is not ideal, it could certainly be better, but we should "not let the perfect
be the enemy of the good".

> 2. Your implementation seems to be not compatible with anything, including Oracle. You shouldn't use mixed syntax
> partially taken from Oracle, partially your own, because it will prevent introduction of Oracle-specific features in
> Oracle compatibility mode due to syntax conflict.

Any such feature is going to be heavily dependant on various details of how the underlying storage engine works, so I
don't think this is a deal-breaker. I cannot see us ever needing such a thing in the Oracle compatibility mode. And in
the unlikely event that we do, our Parser class is flexible enough to copy with having two different syntax paths for
different modes.

>
> You also should use the same code style as the whole project. Please, don't use var and don't invent new interfaces for
> unit tests.
>
> I tried to add some reconnections to your tests and they started to fail. You need to test all these cases too.
>

Agreed, these areas will need improvement.

Regards, Noel Grandin

[Enno Thieleke]

Hello,

thanks for your feedback.

> MVStore is actually a third storage engine for H2 and who knows, maybe there will be a new one without this setting or MVStore itself will be changed incompatibly. I think you need to find some other way.

I thought about a different solution. I wanted to remove the awareness of H2 whether it is in-memory or persistent entirely. The idea I had was to simply rely on `java.nio.file.FileSystem`. Then H2 could write its data, regardless of storage format, and a restore point would simply make a copy of the data on the filesystem - be it in-memory, e.g. using Jimfs, or on disk. However, since I wanted restore points to work with both modes in H2, in-memory and persistent, and in-memory mode is not transparent (yet), I rejected the idea.

I still like the idea though. The current filesystem abstraction in H2 is a good start, but it would need to be able to use `java.nio.file.FileSystem#getPath(...)` on a single `java.nio.file.FileSystem` instance consistently to not end up on a different filesystem.

A downside would be that creating a restore point for large databases would be costly when it comes to storage.

> Your implementation seems to be not compatible with anything, including Oracle.

That's correct. As I said, it is vaguely similar to Oracle. Maybe I should rephrase: It is somewhat inspired by Oracle.

However, I don't care (too) much for the specific SQL syntax. I'd be happy to change it to whatever you think fits H2 best. Maybe `create database snapshot` and `restore database from snapshot`. Something like that. I'm open to suggestions.

> The integration with oldestVersionToKeep is not ideal, it could certainly be better [...]

Do you mean that I shouldn't introduce another variable `restorePointVersion` on top of `oldestVersionToKeep`? I don't think I can make it work without `restorePointVersion` on top, because `oldestVersionToKeep` increments as transactions are being committed. And I need some boundary to tell the actual storage system what to keep and what not.

> Please, don't use var and don't invent new interfaces for unit tests.

No problem. I got rid of `var` already (not pushed yet). Would it be ok if I added an interface to `BaseTest` similar to `VoidCallable`? Maybe `ThrowingConsumer`? I introduced the interface to deal with "aspects" (connections, statements and auto-commit) and to be able to work with lambdas which can throw exceptions. If you have something else in mind, could you point me in the right direction?

> I tried to add some reconnections to your tests and they started to fail.

Could you be more specific so I can look into it? The reconnections are one of the reasons I introduced the interface in my unit tests. I.e. everything is executed with and without auto-commit and, unless in-memory, everything is executed with and without reconnections, because I thought those are the most critical aspects when it comes to restore points.

Regards,

Enno

[Evgenij Ryazanov]

Don't use labdas in unit tests at all unless it is strongly necessary. Write a single method for each test case with plain code, if in needs to be tested with different setup, pass parameters to this method and invoke it multiple times. Take a look on existing unit tests for examples.

I changed execute(boolean autoCommit, SQLUnit... units) to re-open connection and tests started to fail, but maybe I did something wrong. Anyway, such method shouldn't exist, because it's hard to add some additional operation (such as mentioned reconnection) to existing tests ad debugging is also complicated.

This code throws OOME unless restore point creation is commented out:

Connection conn = DriverManager.getConnection("jdbc:h2:mem:1");

Statement stat = conn.createStatement();

stat.execute("CREATE TABLE TEST(ID BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, V BINARY VARYING)");

stat.execute("CREATE RESTORE POINT P");

PreparedStatement prepInsert = conn.prepareStatement("INSERT INTO TEST(V) VALUES ?");

PreparedStatement prepDelete = conn.prepareStatement("DELETE FROM TEST");

for (;;) {

prepInsert.setBytes(1, new byte[1_000_000]);

prepInsert.executeUpdate();

prepDelete.executeUpdate();

}

This code throws General Error:

DeleteDbFiles.execute(".", "rptest1", false);

Connection conn = DriverManager.getConnection("jdbc:h2:./rptest1;DEFRAG_ALWAYS=TRUE");

Statement stat = conn.createStatement();

stat.execute("CREATE TABLE TEST(ID BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, V INT)");

PreparedStatement prepInsert = conn.prepareStatement("INSERT INTO TEST(V) VALUES ?");

prepInsert.setInt(1, 1);

prepInsert.executeUpdate();

stat.execute("CREATE RESTORE POINT P");

for (int i = 2; i <= 100; i++) {

prepInsert.setInt(1, i);

prepInsert.executeUpdate();

}

conn.close();

conn = DriverManager.getConnection("jdbc:h2:./rptest1");

stat = conn.createStatement();

stat.execute("RESTORE TO POINT P");

ResultSet rs = stat.executeQuery("SELECT COUNT(*) FROM TEST");

rs.next();

System.out.println(rs.getLong(1));

It is possible to write a more complex code where RESTORE TO POINT will silently restore to the wrong version. I think you can document that database compaction may erase restore points, but in this case you need to remove their metadata as well to prevent possibility to execute these commands with outdated versions.

Because SCRIPT command can't export these restore points, it must be documented.

You also need to document this functionality as experimental only.

I think content of INFORMATION_SCHEMA.RESTORE_POINTS shouldn't be visible for regular users. Don't use MVCC in its documentation, this term only confuses people.

What is a purpose of OLDEST_DATABASE_VERSION_TO_KEEP and why this field isn't changed in newer restore points if older restore points were already dropped?

[Andrei Tokar]

Hi Enno,

Forgive me for a possibly stupid question, but why this feature is so badly needed?

What's wrong with populating database to a desired state, shutting down, copying the file, and starting H2 with a file copy.

Why do you need this functionality for in-memory (non-persistent) database? If performance is the reason, you can use RAM-based file system instead.

I am struggling to understand what is going to happen with all other concurrent connections and their transactions at the moment when RESTORE TO POINT is executed.

I also agree with Evgenij, that implementation based on MVStore will prevent a lot of intermediate versions from being dropped and heedlessly bloat database file.

Regards,
Andrei

[Andreas Reichel]

Dear All.

On Fri, 2024-11-08 at 18:47 -0800, Andrei Tokar wrote:

What's wrong with populating database to a desired state, shutting down, copying the file, and starting H2 with a file copy.

Am I right to assume, that shutdown is not even needed: you can run export to script/backup on an open instance without interruptions, right?

(Tiny detail, but it could matter.)

So if I was interested in this feature, I would implement SAVEPOINT as "export to script" and RESTORE as "drop all schemas and execute script". Only questions I have: how to lock the DB while this happens and queue/stall any pending sessions -- in case of multi-user access.

Cheers

Andreas

[Enno Thieleke]

Hello,

thanks for the valuable feedback and time you've taken. I will try to go from top to bottom.

> Don't use labdas in unit tests at all unless it is strongly necessary.

Understood.

> I changed [...] to re-open connection and tests started to fail.

Correct me if I'm wrong, but for in-memory databases failures are expected, because they are dropped when the last connection is closed (unless prevented via flags in the connection string). That's one of the things my lambdas tried to address. However, I've replaced the lambda based tests with simpler, more straight forward ones which allow faster reasoning about the test behavior.

> This code throws OOME unless restore point creation is commented out

Thanks for pointing that out and good catch. For the time being I will not address it this, but I would address it once you (as a group) are convinced the feature goes in.

> This code throws General Error

Same as above, but an even better catch. In my opinion database compaction should not erase restore points. That would defeat the purpose of restore points (the way I intended them) entirely.

> Because SCRIPT command can't export these restore points, it must be documented.

Good point.

> You also need to document this functionality as experimental only.

Of course. If it goes in at all though.

> I think content of INFORMATION_SCHEMA.RESTORE_POINTS shouldn't be visible for regular users.

I disagree. The information schema contains all available information of a database and restore points are a part of that information, much like tables and columns and constraints. If nothing else, it simply enables users to realize that there are still restore points, i.e. more is kept on disk than might be necessary. Or maybe I misunderstood: I'm thinking of users who have access to the information schema and you'd like to restrict access to the table `restore_points` to admins?

> Don't use MVCC in its documentation, this term only confuses people.

I disagree again. MVCC is a widely used concept and restore points simply expose an implementation detail in H2: that it uses MVCC. I think this is comparable to the Oracle documentation, which is pretty thorough. Even more so than PostgreSQL (which is mind blowing to me). But I also get your point that it would be great if we were able to hide that information from users in case H2 ever switches to another storage which is not MVCC. But we could always adjust the docs if that happens.

> What is a purpose of OLDEST_DATABASE_VERSION_TO_KEEP and why this field isn't changed in newer restore points if older restore points were already dropped?

The field tracks the `database_version` of the very first restore point across all restore points. The reason (for better or worse) is that if one creates two restore points A and B, the latter one includes the creation of A, because they share the same storage as the rest of the database. Ergo, if one drops restore point A but returns to point B (at which the drop of A hasn't happened yet) and then returns to A, we expect the database to be in the state of A. And that's why we need to track the version of A (the oldest restore point in a chain) in every subsequent restore point. I'd be lieing if I said that I like this detail.

> [...] a [...] question, but why this feature is so badly needed?

It is 100% not badly needed. It would merely be a nice to have and, to be completely honest, I don't think it would ever go beyond that. However, if somebody wants to be able to restore an H2 database to an earlier state without the hassle of requiring persistent storage and copying files around, that's where it would shine.

> What's wrong with populating database to a desired state, shutting down, copying the file, and starting H2 with a file copy.

Nothing. It's just a wee bit more work.

> Why do you need this functionality for in-memory (non-persistent) database? If performance is the reason, you can use RAM-based file system instead.

Performance is not the reason. At least not when it comes to H2 directly. Database population tools like Liquibase on the other hand can be a bottleneck. And I actually tried a RAM-based filesystem. Jimfs. But I didn't get H2 to work on it, because it doesn't use `Paths.get(URI)` to look up files, but `Paths.get(String, String...)`. As for "real" RAM-based filesystems: That's simply too much work in my opinion.

But to answer your question why I would like this functionality for in-memory databases: Convenience. There's simply no other and no good or better reason.

But this kinda brings me back to the point I mentioned already: I think H2 shouldn't know if it's running in-memory or not and it should work with Java based in-memory filesystems. And maybe it even does and I was not able to figure out how, but I think it doesn't.

> I am struggling to understand what is going to happen with all other concurrent connections and their transactions at the moment when RESTORE TO POINT is executed.

Gone. Simple as that. Concurrent connections are dropped. The connection that executes `restore to point` (which requires admin permissions) goes into exclusive mode and drops all other connections. It then rolls the storage back to the desired version and re-initializes the database (reloads META, rolls back transactions that were ongoing concurrently at the time of restore point creation it is restoring to). It is as if nothing ever happened after the restore point, that's being returned to. That's the idea at least.

Not gonna lie, it is complicated.

> I also agree with Evgenij, that implementation based on MVStore will prevent a lot of intermediate versions from being dropped and heedlessly bloat database file.

Which shouldn't be a problem once all restore points have been dropped, because then the MVStore GC can work its magic, or if a database is being restored to a point, because then at least some GC can happen.

> So if I was interested in this feature, I would implement SAVEPOINT as "export to script" and RESTORE as "drop all schemas and execute script". Only questions I have: how to lock the DB while this happens and queue/stall any pending sessions -- in case of multi-user access.

I like that idea a lot. It is much, much simpler than my approach. Although it's more than drop all schemas, because it would require a wiped database. Nothing bad though. Let's maybe go even further and simply create a script export and then create a new database with `INIT=RUNSCRIPT FROM '~/create.sql'`. That should do the trick, no? Why didn't I come up with it? :D

Yes, all connections would have to be dropped to the existing database, but it would be wiped clean anyway, so that's not an issue.

Guys, thanks a lot for the thoughts exchange. I'm still willing to spend time on "my approach" if you'd accept it into H2, but I'd also be fine with dropping it. As I said, what I've written so far was a good exercise and I've learned more about H2 in the past few days than I have over the last 5 or more years.

Regards,

Enno

[Andrei Tokar]

Here is my quick and dirty simulation of the "restore point" with copying of in-memory files:

public class RestorePointSimulation
{
    public static void main(String[] args) throws Exception {
        Class.forName("org.h2.Driver");
        String goldenCopy = "test";

        // populate database with required data

        try(Connection conn = DriverManager.getConnection("jdbc:h2:memFS:" + goldenCopy)) {
            try (Statement stat = conn.createStatement()) {
                stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, V INT) AS SELECT X, X FROM SYSTEM_RANGE(1, 1_000)");
            }
        }

        String workingCopy = "work";

        copyDatabaseFile(goldenCopy, workingCopy); // @BeforeTest

        // run first test

        try(Connection conn = DriverManager.getConnection("jdbc:h2:memFS:"+workingCopy)) {
            try (Statement stat = conn.createStatement()) {
                verifyRowCount(stat, 1_000);
                stat.execute("DELETE FROM TEST");
                verifyRowCount(stat, 0);
            }
        }

        copyDatabaseFile(goldenCopy, workingCopy); // @BeforeTest

        // run second test
        try(Connection conn = DriverManager.getConnection("jdbc:h2:memFS:"+workingCopy)) {
            try (Statement stat = conn.createStatement()) {
                verifyRowCount(stat, 1_000);
            }
        }
    }

    private static void copyDatabaseFile(String goldenCopy, String workingCopy) throws IOException {
        String workFileName = "memFS:" + workingCopy + ".mv.db";
        FilePath workFilePath = FilePath.get(workFileName);
        try (OutputStream out = workFilePath.newOutputStream(false)) {
            IOUtils.copy(FilePath.get("memFS:"+ goldenCopy +".mv.db").newInputStream(), out);
        }
        assert workFilePath.exists();
        assert workFilePath.isRegularFile();
        assert workFilePath.size() > 0;
    }

    private static void verifyRowCount(Statement stat, int expectedCount) throws SQLException {
        try (ResultSet resultSet = stat.executeQuery("SELECT COUNT(*) FROM TEST")) {
            assert resultSet.next();
            assert resultSet.getInt(1) == expectedCount;
        }
    }
}

[Noel Grandin]

Hi

On 11/9/2024 4:47 AM, Andrei Tokar wrote:
> Forgive me for a possibly stupid question, but why this feature is so badly needed?
> What's wrong with populating database to a desired state, shutting down, copying the file, and starting H2 with a file copy.
> Why do you need this functionality for in-memory (non-persistent) database? If performance is the reason, you can use
> RAM-based file system instead.

Just noting that in the time I have been working on H2, this is the third time this feature has come up.

Some people want to (a) have their test database at a known point (b) run a _lot_ of unit tests and (c) have their unit
tests run relatively quickly.

So for all it's shortcomings, I think it would be a useful addition (Plus I am tickled pink that such afeature could be
implemented in so few lines of code, thanks to H2's excellent internal architecture)

Regards, Noel Grandin

[Enno Thieleke]

Hello,

@Evgenij:

> This code throws OOME unless restore point creation is commented out:

I've taken a look at it and at first I thought it's the concurrent system session that commits the auto generated sequence value. However, a bit of playing and testing revealed that I can reproduce at least a similar issue in "vanilla" H2 (i.e. without restore points). It is caused by auto-commit `false` which I tried, because I thought it would prevent database version increments. After all, with a restore point, all commits (database versions) are being retained and thus cost storage. Please find a reproducer at https://github.com/i-am-not-giving-my-name-to-a-machine/h2-tests

> This code throws General Error:

Very interesting. This shows a blatant shortcoming of my implementation. The oldest version to keep is currently not part of the MVStore file (it's only part of the META table of the database), but it has to be to be taken into account in situations where the files are being compacted. This is something I'd happily try to address if this feature has a future.

@Andrei

> Here is my quick and dirty simulation of the "restore point" with copying of in-memory files:

I didn't fully realize that H2 comes with its own in-memory file system. Albeit a very basic one. I have taken your code and tried it for Spring tests. It works great. If anyone is interested, here's my "PoC": https://github.com/i-am-not-giving-my-name-to-a-machine/spring-test-database-restoration-poc

This also made me realize that it would be nice if a restore point feature would not drop connections on a `restore to point` SQL execution. If no transaction is ongoing, it could "freeze" the database (block new transactions), restore the database and unfreeze it. That would remove the need for clearing a connection pool of connections to a database that doesn't exist anymore. And a golden copy (nice choice of words btw) wouldn't be necessary as well.

@Noel

> [...] thanks to H2's excellent internal architecture

Couldn't agree more. It was extremely easy to understand the mechanics and interactions. The one thing that "frightens" me the most is the actual storage format/structure of the MVStore, but it's probably not a that difficult either.

To wrap up: I'm here, if you want to continue this journey. Maybe you could point me in the right direction where to put the oldest version to keep in the MVStore file. I'm assuming there are some headers and I could simply put it there?

Regards,

Enno

[Enno Thieleke]

Hi,

I've put some more work into this feature request and wanted to end this thread here in favor of a PR I've just created on GitHub. In case anyone is interested.

Regards,

Enno