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?
Best regards,
Eric Faulhaber
--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [email protected].
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/76bcf837-8563-543d-4dbf-9d335b67bdef%40goldencode.com.
