TRANSACTIONAL option with creating/dropping tables/indexes

[Eric Faulhaber]

Hello,

I work on a framework which uses H2 1.4.197 as an in-memory database, primarily for temporary tables.

The framework needs to create and drop temporary tables and their indexes on demand, as needed by an application running atop the framework.  The timing of these actions is driven by application logic, and as such is not fully under the control of the framework.  As a result, create/drop DDL often needs to be executed within the scope of an existing transaction established by an application.  These DDL actions must not commit the current transaction, as this causes application errors.

The CREATE TEMPORARY TABLE command offers the TRANSACTIONAL option, which prevents such a commit.  However, in accordance with the online documentation, the CREATE INDEX, DROP TABLE, and DROP INDEX commands do not support TRANSACTIONAL.

However, we noticed that at the top of the CreateIndex.update() method, a transactional instance variable (inherited from DefineCommand) is checked, and only if it is false is the current transaction committed.  The field is not initialized by default, and it is not obvious under which conditions (if at all) it would be set to true for the CREATE INDEX command.

I also checked DropTable.update() and DropIndex.update().  Both methods commit the current transaction unconditionally, again in accordance with the documentation.

My questions:

• As noted, CreateIndex.update() checks the transactional flag before committing.  Is this variable ever set to true, such that the commit would be bypassed?
  
• Is there a functional reason why CREATE INDEX, DROP INDEX, and DROP TABLE should not support the TRANSACTIONAL option for temporary tables and the indexes on them?  Or is it simply that no one has implemented it yet?
• If we were to implement support for TRANSACTIONAL syntax on CREATE INDEX, DROP INDEX, and DROP TABLE, would such support likely be welcomed back into the project?

Thank you in advance for your help.

Best regards,
Eric Faulhaber

[Noel Grandin]

On Sat, 30 May 2020 at 03:22, Eric Faulhaber <e...@goldencode.com> wrote:

• As noted, CreateIndex.update() checks the transactional flag before committing.  Is this variable ever set to true, such that the commit would be bypassed?
  

It is set when creating constraints inside a table and when the TRANSACTIONAL keyword is encountered.

 

• Is there a functional reason why CREATE INDEX, DROP INDEX, and DROP TABLE should not support the TRANSACTIONAL option for temporary tables and the indexes on them?  Or is it simply that no one has implemented it yet?

Most likely.

 

• If we were to implement support for TRANSACTIONAL syntax on CREATE INDEX, DROP INDEX, and DROP TABLE, would such support likely be welcomed back into the project?

We're quite happy to accept changes, as long they come with at least one unit test and don't break any of the other unit tests.

See build instructions here:

http://h2database.com/html/build.html