Re: [h2] TRANSACTIONAL option with creating/dropping tables/indexes

[Noel Grandin]

On Sat, 30 May 2020 at 03:22, Eric Faulhaber  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

-- 
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 h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWoREi%2BSyuQ%3D2MVMGP_dQq3vSH%3DKX0Lxz-HMYK%3DQcjz7w%40mail.gmail.com.

[h2] 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
  




-- 
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 h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/76bcf837-8563-543d-4dbf-9d335b67bdef%40goldencode.com.