temporary table support
steve.ebersole
to properly utilize them from Hibernate's H2Dialect. But I am running
into some questions:
1) First, does creating a temporary table cause an implicit
transaction commit? The H2 driver reports that DDL does cause an
implicit commit
(DatabaseMetaData#dataDefinitionCausesTransactionCommit). My question
is whether that is true of temporary objects as well? Does LOCAL/
GLOBAL matter here?
2) Based on WhatMadeSense, I tried to have Hibernate use "create
cached local temporary table if not exists" as the command (cached
because I am unsure how many rows might be added and the docs implied
that memory should not be used if the table is "too large", whatever
that means). However, because of
DatabaseMetaData#dataDefinitionCausesTransactionCommit returning true
right now Hibernate is issuing this in a separate connection. The odd
part is that the "main connection" is able to see it. Am I missing
something about "local" as pertains to temp tables in H2?
--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Thomas Mueller
> 1) does creating a temporary table cause an implicit
> transaction commit?
Yes, in all cases.
> Does LOCAL/GLOBAL matter here?
No.
> "create cached local temporary table if not exists"
connection, so this should not work. My test case throws an exception:
http://h2database.com/p.html#4d04031e2abad7738756f232658a2502
package db;
import java.sql.Connection;
import java.sql.DriverManager;
import org.h2.tools.DeleteDbFiles;
public class TestSimple {
public static void main(String... args) throws Exception {
Class.forName("org.h2.Driver");
DeleteDbFiles.execute("data", "test", true);
Connection conn1 = DriverManager.getConnection("jdbc:h2:data/test");
Connection conn2 = DriverManager.getConnection("jdbc:h2:data/test");
try {
conn1.createStatement().execute(
"create cached local temporary table if not exists test(id int)");
conn2.createStatement().execute(
"select * from test");
} finally {
conn1.close();
conn2.close();
}
}
}
Regards,
Thomas
steve.ebersole
I guess I had misread the docs. I thought I read that LOCAL was the
default for temp tables. I guess I was confused with CACHED/MEMORY.
Should I be concerned with the CACHED/MEMORY option in your opinion?
I am actually running into issues in our testsuite if I try to specify
CACHED (the table is not found after creation). Perhaps CACHED here
wont work if using an in-memory DB? H2 throws no exception and gives
no warnings though...
steve.ebersole
succeed. The subsequent inserts and selects seems to work fine as
well. The problem is when we try to delete the rows. The part that
is odd is that the temp table is GLOBAL so technically the connections
should not matter, but the same connection is used for the inserts and
selects as for the delete.
Also, could you clarify wrt GLOBAL temp tables... Do they maintain
which session/connection inserted rows?
We are using these temporary tables to temporarily store IDs that
match some criteria. The issue is that these ID values need to be
scoped to a Hibernate session. In the ideal world you'd always use
local temporary tables since these would be scoped. The problem is
that we create these tables as we need them. And we cannot do this
"in line" with the main JDBC connection because there may already be
uncommitted work done on it and as we discussed above creating this
temp table would cause those changes to be committed. This is why we
open up the new connection, but as you point out that means we need to
use a global temporary table.
Any suggestions?
The only one I see is to mimic what Oracle does for its temporary
table support in Hibernate itself for databases such as H2 (and
HSQLDB) which do not support creating local temp tables within a
transaction. What Oracle does is to essentially create a global
temporary table but it automatically manages a column that indicates
which session (connection) the data belongs to.
You see any other options here?
Thomas Mueller
> Should I be concerned with the CACHED/MEMORY option in your opinion?
probably make that the default in a future release (but not now).
> I am actually running into issues in our testsuite if I try to specify
> CACHED (the table is not found after creation). Perhaps CACHED here
> wont work if using an in-memory DB? H2 throws no exception and gives
> no warnings though...
> GLOBAL temp tables... Do they maintain which session/connection inserted rows?
differently... I didn't know about that (and nobody told me about this
so far). Unfortunately it will take some time until I can fix this.
> Any suggestions?
I see the problem. I think there are two workarounds:
A) Create a global temporary table (using a different connection) with
a unique name (for example using the session id as the unique name,
you get that using "call session_id()".
B) Create a global temporary table and a temporary view with one more column:
create cached global temporary table temp(
session_id int default session_id(),
id int, name varchar(255),
primary key(id, session_id));
insert into temp(id, name) values(1, 'Hello');
select * from temp where session_id = session_id();
Regards,
Thomas
steve.ebersole
wrote:
>
> > Should I be concerned with the CACHED/MEMORY option in your opinion?
>
> If you are not sure how large the table will be, use CACHED. I will
> probably make that the default in a future release (but not now).
>
> > I am actually running into issues in our testsuite if I try to specify
> > CACHED (the table is not found after creation). Perhaps CACHED here
> > wont work if using an in-memory DB? H2 throws no exception and gives
> > no warnings though...
>
> That's strange, what kind of issues?
insert into them. I can select from them. But for some odd reason as
soon as I try to delete from them I get an error that the table cannot
be found. As we discussed, the creation happens on a separate
connection; but all the DML is happening on the single main
connection. Thats what makes this so strange.
Literally all I change is to remove "cached" from the CREATE TABLE
statement and it all works.
I would like to use CACHED as I said before, since my reading of the
docs seemed to indicate that would be the best option for this case.
But obviously I need to go with what "works" (though obviously the
solution as is is only going to work for non-concurrent type access).
> > GLOBAL temp tables... Do they maintain which session/connection inserted rows?
>
> No. Now that I read the Oracle documentation, I see H2 behaves
> differently... I didn't know about that (and nobody told me about this
> so far). Unfortunately it will take some time until I can fix this.
local temp table. Their global temp table, however, behaves like one
because of it auto tracking the sessionid.
Personally I'd be much happier with a change to not have creation of
(at least local) temp tables cause the implicit transaction commit.
steve.ebersole
Thomas Mueller
I think I understand. What if creating a local temp table wouldn't
commit? I will investigate if this can be supported. Anyway at some
point DDL operations should be transactional (it's not high on the
priority list so far however). What about a new syntax:
CREATE LOCAL TEMPORARY TABLE .... WITHOUT COMMIT;
It's a bit ugly I agree, but it would solve the problem for you. Like
this, I could add support for transactional DDL step by step, and when
everything is implemented then switch over so this becomes the
default.
Regards,
Thomas
Rami Ojares
- rami
Dario Fassi
What's the best way to monitor connections count and resource consumption ?
regards
Dario.
steve.ebersole
wrote:
> Hi,
>
> I think I understand. What if creating a local temp table wouldn't
> commit?
Like I said earlier, this is my preferred solution. If creating the
temp table does not commit then I can do it on that initial connection
and use a true local temp table.
> I will investigate if this can be supported. Anyway at some
> point DDL operations should be transactional (it's not high on the
> priority list so far however). What about a new syntax:
>
> CREATE LOCAL TEMPORARY TABLE .... WITHOUT COMMIT;
>
> It's a bit ugly I agree, but it would solve the problem for you. Like
> this, I could add support for transactional DDL step by step, and when
> everything is implemented then switch over so this becomes the
> default.
Sounds reasonable to me short term and totally something i could
leverage immediately (well, once its available).
Thomas Mueller
> What's the best way to monitor connections count and resource consumption ?
Use SELECT * FROM INFORMATION_SCHEMA.SESSIONS
There is no easy way to measure the resource consumption for each
session individually, however some values (specially info.CACHE_SIZE)
of this database are available using SELECT * FROM
INFORMATION_SCHEMA.SETTINGS.
Regards,
Thomas
Dario Fassi
>> There is no easy way to measure the resource consumption for each
>> session individually, however some values (specially info.CACHE_SIZE)
>> of this database are available using SELECT * FROM
>> INFORMATION_SCHEMA.SETTINGS.
JVM -Xmx.
For deploying webapps to tomcat with and H2 database embeded it's
important to know database's memory consumption in the worst case to
adjunt the -Xmx on the JMV that start tomcat, since the same jvm must
allocate resources for it self + webapps + h2db.
I will try to dig this with jvm instrumentation on a running instance.
regards,
Dario.
Thomas Mueller
> I'm worried about last change log notice of CACHE_SIZE using half of
> JVM -Xmx.
Do you mean this entry?: "When opening an existing database, the cache
size is set to at most half the amount of memory available for the
virtual machine (Runtime.getRuntime().maxMemory()), even if the cache
size setting stored in the database is larger. Setting the cache size
in the database URL or explicitly using SET CACHE_SIZE overrides this
value (even if larger than the physical memory)."
The "half of JVM" only applies if the cache size defined in the
cache_size setting is *larger* than that.
The reason for this change is: sometimes I get databases with the
cache_size set very large. As the cache_size setting is persisted (in
the database) this is problematic (slow performance, sometimes even
out of memory). The change in version 1.2.136 is just a safeguard
against forgetting to reset this value. Maybe it would be better to
not persist the cache_size setting in the database itself (not sure).
Regards,
Thomas
Dario Fassi
What I mean is that the amount of memory consumed at any time by dbm (even in Embedded mode) should respect an "upper bound" or at least knowable and predictable.
Similarly it would be important to know about the amount of additional resources that use the dbm for each new client connection in server mode.
This is extremely important to properly allocate resources in a particular on application server but at JVM too
I understand what you say goes in this direction and it's some way to circumvent user's bad setting so there are nothing to be worried if we can know what's the used value !
A thought. Why not let this value to be dynamically adjusted based on the available memory in the JVM conservatively ?
Thus if, on the dbm could use idle memory.
Dario
Thomas Mueller
> What I mean is that the amount of memory consumed at any time by dbm (even
> in Embedded mode) should respect an "upper bound" or at least knowable and
> predictable.
It is, if you specify it in the connection setting (A).
However if you don't (B), and it is set in the database, then there
are two cases:
- you do want to use the previous setting
- you forgot that it was set previously
The "automatic limit" only applies to (B).
> if we can
> know what's the used value !
You can. But the point is, if you do want to use an explicit value,
then you need to set it each time you open the database.
> Why not let this value to be dynamically adjusted based on the
> available memory in the JVM conservatively ?
That's what the automatic mechanism does.
> Thus if, on the dbm could use idle memory.
That's already available:
http://h2database.com/html/features.html#cache_settings
Regards,
Thomas
Dario Fassi
> You can. But the point is, if you do want to use an explicit value,
> then you need to set it each time you open the database.
>
>> Why not let this value to be dynamically adjusted based on the
>> available memory in the JVM conservatively ?
>> Thus if, on the dbm could use idle memory.
> That's what the automatic mechanism does.
>
I mean dynamically at runtime, but if I had understood that this value
it's determined at database start and never adjusted, I'm right ?
What's happen when at runtime we change the CACHE_SIZE to a greater or
lower value , it's adjusted in the next database start or ... ?
Is a costly operation to dbm change CACHE_SIZE at runtime or do nothing ?
If you wonder why:
I'm thinking in the case of H2 in embedded mode or started with a
ConnectionPool as DataSource factory of Tomcat or another application
server.
In the same JVM we have the application (or Container) and the embedded
H2 instance sharing jvm resources.
To setup this scenario we need to estimate application or container
memory needs (can be measured starting H2 as a server in a separated
process) and then embed H2 with a convenient cache size.
Because application/container memory needs must to taken in the worst
peak plus a prudential margin, most of the time there will be available
memory at jvm that H2 could use and release periodically.
Ex.
setup JVM -Xmx=512m
Application/Container use = from 60m to 450m : avg 260m
If we start H2 without specific cache size then they will be using 256m
that can lead in a suboptimal situation.
If we choice to set a fixed cache size to avoid that , then it must be
set to some value bellow 200m, hurting h2 performance even if there are
400m of available memory in most of the time.
In an application case we can get a thread adjusting cache size in
regard of runtime.freeMemory() .
Can be something like this possible ?
regards
Dario
Thomas Mueller
> I mean dynamically at runtime, but if I had understood that this value
> it's determined at database start and never adjusted, I'm right ?
Yes, the value is not adjusted automatically. I don't think that would
be a solution. Would you add a background thread to detect if other
applications need memory? Actually there is no way to do that correct,
except using SoftReference/WeakReference/PhantomReference. And that's
what is already implemented:
http://h2database.com/html/features.html#cache_settings
> Is a costly operation to dbm change CACHE_SIZE at runtime or do nothing ?
I don't understand the question sorry.
> most of the time there will be available
> memory at jvm that H2 could use and release periodically.
This is already available. I suggest to read the documentation:
http://h2database.com/html/features.html#cache_settings "Also included
is an experimental second level soft reference cache."
Regards,
Thomas
steve.ebersole
Should I create one?
On May 26, 3:34 pm, "steve.ebersole" <steven.ebers...@gmail.com>
wrote:
Thomas Mueller
> Was there an issue I could track for this?
I created the issue
http://code.google.com/p/h2database/issues/detail?id=214 - if you
'star' the feature you will get notified of changes.
I have implemented this feature now, if everything goes fine it will
be included in the next release (probably in two weeks).
Regards,
Thomas
Steve Ebersole
I apologize but I am not familiar with this issue tracker. As far as I
can tell it is still an open issue. At the very least I did not see an
association to the version in which it is/will be fixed. That's more
what I was getting at.
--
Steve Ebersole <st...@hibernate.org>
http://hibernate.org
Thomas Mueller
> I am not familiar with this issue tracker.
It's the one from Google Code. It's not as sophisticated as Jira, but
it works well for what I need.
> As far as I can tell it is still an open issue.
I closed it today: Implemented in version 1.2.139. Example usage:
set autocommit true;
create table test(id int);
insert into test values(1);
set autocommit false;
insert into test values(2);
create local temporary table temp(id int primary key, name
varchar(255)) transactional;
insert into temp values(1, 'test');
rollback;
select * from test;
drop table test;
drop table temp;
set autocommit true;
create table test(id int);
insert into test values(1);
set autocommit false;
insert into test values(2);
create local temporary table temp(id int primary key, name
varchar(255)) on commit drop transactional;
insert into temp values(1, 'test');
select * from temp;
rollback;
select * from test;
drop table test;
-- table already dropped, so this will fail:
select * from temp;
Regards,
Thomas