Primary key violation for AUTO_INCREMENT column

[Gili]

Hi,

I'm running into constraint violations that I believe should be impossible using H2 1.4.181. My table definition is:

CREATE TABLE user (id SMALLINT AUTO_INCREMENT(-32768, 1) PRIMARY KEY, owner_id SMALLINT,

email VARCHAR(254) NOT NULL UNIQUE, password VARCHAR(82) NOT NULL, name VARCHAR(100) NOT NULL,

owned_permission_id SMALLINT NOT NULL, view_id SMALLINT NOT NULL, edit_id SMALLINT NOT NULL,

delete_id SMALLINT NOT NULL, version INTEGER NOT NULL, last_modified TIMESTAMP NOT NULL,

FOREIGN KEY (owned_permission_id) REFERENCES permission(id) ON DELETE CASCADE,

FOREIGN KEY (owner_id) REFERENCES user(id),

FOREIGN KEY (view_id) REFERENCES permission(id),

FOREIGN KEY (edit_id) REFERENCES permission(id),

FOREIGN KEY (delete_id) REFERENCES permission(id));

I am getting this exception:

com.vtlr.backend.ConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.USER(ID)"; SQL statement:

insert into USER (EMAIL, PASSWORD, NAME, OWNED_PERMISSION_ID, OWNER_ID, VIEW_ID, EDIT_ID, DELETE_ID, VERSION, LAST_MODIFIED)

values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-181]

at com.vtlr.backend.SqlExceptions.getConstraintViolation(SqlExceptions.java:91)

at com.vtlr.backend.row.User.insert(User.java:102)

at com.vtlr.backend.resource.AnonymousUserResource.createUser(AnonymousUserResource.java:85)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:483)

at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)

at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:151)

at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:171)

at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:152)

at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:104)

at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:387)

at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:331)

at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:103)

at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:271)

at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)

at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)

at org.glassfish.jersey.internal.Errors.process(Errors.java:315)

at org.glassfish.jersey.internal.Errors.process(Errors.java:297)

at org.glassfish.jersey.internal.Errors.process(Errors.java:267)

at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:297)

at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:254)

at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1028)

at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:372)

at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:381)

at org.glassfish.jersey.servlet.ServletContainer.doFilter(ServletContainer.java:534)

at org.glassfish.jersey.servlet.ServletContainer.doFilter(ServletContainer.java:482)

at org.glassfish.jersey.servlet.ServletContainer.doFilter(ServletContainer.java:419)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1650)

at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)

at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)

at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)

at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1125)

at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)

at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)

at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1059)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)

at org.eclipse.jetty.server.Server.handle(Server.java:485)

at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:290)

at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:248)

at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)

at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:606)

at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:535)

at java.lang.Thread.run(Thread.java:745)

Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.USER(ID)"; SQL statement:

insert into USER (EMAIL, PASSWORD, NAME, OWNED_PERMISSION_ID, OWNER_ID, VIEW_ID, EDIT_ID, DELETE_ID, VERSION, LAST_MODIFIED)

values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-181]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)

at org.h2.message.DbException.get(DbException.java:179)

at org.h2.message.DbException.get(DbException.java:155)

at org.h2.index.PageDataIndex.getNewDuplicateKeyException(PageDataIndex.java:165)

at org.h2.index.PageDataIndex.add(PageDataIndex.java:143)

at org.h2.table.RegularTable.addRow(RegularTable.java:119)

at org.h2.command.dml.Insert.insertRows(Insert.java:156)

at org.h2.command.dml.Insert.update(Insert.java:114)

at org.h2.command.CommandContainer.update(CommandContainer.java:78)

at org.h2.command.Command.executeUpdate(Command.java:254)

at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:157)

at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:143)

at com.jolbox.bonecp.PreparedStatementHandle.executeUpdate(PreparedStatementHandle.java:205)

at com.mysema.query.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:302)

at com.mysema.query.sql.dml.SQLInsertClause.executeWithKey(SQLInsertClause.java:186)

at com.mysema.query.sql.dml.SQLInsertClause.executeWithKey(SQLInsertClause.java:169)

at com.vtlr.backend.row.User.insert(User.java:94)

... 45 common frames omitted

If I understand correctly, H2 is complaining that I am inserting a duplicate ID. But as you can see, the ID is AUTO_INCREMENT and I am not specifying an explicit value (not ever in any place in my code). Any ideas?

This error is intermittent so I've been unable to create a testcase for it.

Thanks,

Gili

[Gili]

I caught the exception and dumped the database contents after such a conflict and, sure enough, H2 is trying to insert an existing primary key value.

I didn't have these kind of problems before and I'm wondering what has been triggering all of this. I'm beginning to think this has something to do with the use of negative numbers with AUTO_INCREMENT or the use of primary keys that are smaller than BIGINT.

Gili

[Thomas Mueller]

Hi,

I'm not aware of any bugs in this area. It would be great if you could provide a reproducible test case!

Regards,

Thomas

--
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...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Gili]

I've been H2 for many years but sadly three different factors have compelled me to try to migrate to Postgresql:

1. The inability to use different isolation levels for different connections (a short-term showstopper).
   
2. A poor clustering story (a long-term showstopper)
3. This AUTO_INCREMENT bug (probably easy to fix but it's the straw that broke the camel's back).
   

I'm going to go break my teeth migrating to PL/PGSQL (which I dislike with a passion). When that's over with, I'll try to go back and produce a testcase for this issue.

Sorry I can't handle this the other way around. I've got people breathing down my neck asking for a stable release :(

Out of curiosity, do you think a trace log is enough to get started on this issue (I'm not sure I will be able to produce a minimal testcase)? I've already got one handy. I'd just need to clean it up a bit and walk you through what lines to look at.

Gili

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

[Noel Grandin]

HI

Sorry to hear that.

If you're in crunch mode, you should probably not be using our beta release - either downgrade to 1.3.176 or run 1.4.181
with MV_STORE=false to use the old storage engine.

I would suggest simply not using isolation levels for H2 - we should be plenty fast enough in our default isolation mode.

Clustering is a very hard problem to do right - We still have so much we could do with the single machine case, that
unless someone else shows up who is keen to hack on that, we are not likely to do much there for quite a while.
At the moment Thomas does 98% of the work, I do about 1% and various random people showing up with patches account for
the other 1%.

A trace log is probably not going to help us very much, but reverting to the old storage engine will probably fix this
for you anyhow.

Regards, Noel.

On 2014-09-16 09:12 AM, Gili wrote:
> I've been H2 for many years but sadly three different factors have compelled me to try to migrate to Postgresql:
>

> 1. The inability to use different isolation levels for different connections (a short-term showstopper).
> 2. A poor clustering story (a long-term showstopper)
> 3. This AUTO_INCREMENT bug (probably easy to fix but it's the straw that broke the camel's back).

> com.vtlr.backend.__ConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON

> PUBLIC.USER(ID)"; SQL statement:
> insert into USER (EMAIL, PASSWORD, NAME, OWNED_PERMISSION_ID, OWNER_ID, VIEW_ID, EDIT_ID, DELETE_ID,
> VERSION, LAST_MODIFIED)
> values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-181]

> at com.vtlr.backend.__SqlExceptions.__getConstraintViolation(__SqlExceptions.java:91)
> at com.vtlr.backend.row.User.__insert(User.java:102)
> at com.vtlr.backend.resource.__AnonymousUserResource.__createUser(__AnonymousUserResource.java:85)
> at sun.reflect.__NativeMethodAccessorImpl.__invoke0(Native Method)
> at sun.reflect.__NativeMethodAccessorImpl.__invoke(__NativeMethodAccessorImpl.java:__62)
> at sun.reflect.__DelegatingMethodAccessorImpl.__invoke(__DelegatingMethodAccessorImpl.__java:43)
> at java.lang.reflect.Method.__invoke(Method.java:483)
> at
> org.glassfish.jersey.server.__model.internal.__ResourceMethodInvocationHandle__rFactory$1.invoke(__ResourceMethodInvocationHandle__rFactory.java:81)
> at
> org.glassfish.jersey.server.__model.internal.__AbstractJavaResourceMethodDisp__atcher$1.run(__AbstractJavaResourceMethodDisp__atcher.java:151)
> at
> org.glassfish.jersey.server.__model.internal.__AbstractJavaResourceMethodDisp__atcher.invoke(__AbstractJavaResourceMethodDisp__atcher.java:171)
> at
> org.glassfish.jersey.server.__model.internal.__JavaResourceMethodDispatcherPr__ovider$ResponseOutInvoker.__doDispatch(__JavaResourceMethodDispatcherPr__ovider.java:152)
> at
> org.glassfish.jersey.server.__model.internal.__AbstractJavaResourceMethodDisp__atcher.dispatch(__AbstractJavaResourceMethodDisp__atcher.java:104)
> at org.glassfish.jersey.server.__model.ResourceMethodInvoker.__invoke(ResourceMethodInvoker.__java:387)
> at org.glassfish.jersey.server.__model.ResourceMethodInvoker.__apply(ResourceMethodInvoker.__java:331)
> at org.glassfish.jersey.server.__model.ResourceMethodInvoker.__apply(ResourceMethodInvoker.__java:103)
> at org.glassfish.jersey.server.__ServerRuntime$1.run(__ServerRuntime.java:271)
> at org.glassfish.jersey.internal.__Errors$1.call(Errors.java:271)
> at org.glassfish.jersey.internal.__Errors$1.call(Errors.java:267)
> at org.glassfish.jersey.internal.__Errors.process(Errors.java:__315)
> at org.glassfish.jersey.internal.__Errors.process(Errors.java:__297)
> at org.glassfish.jersey.internal.__Errors.process(Errors.java:__267)
> at org.glassfish.jersey.process.__internal.RequestScope.__runInScope(RequestScope.java:__297)
> at org.glassfish.jersey.server.__ServerRuntime.process(__ServerRuntime.java:254)
> at org.glassfish.jersey.server.__ApplicationHandler.handle(__ApplicationHandler.java:1028)
> at org.glassfish.jersey.servlet.__WebComponent.service(__WebComponent.java:372)
> at org.glassfish.jersey.servlet.__ServletContainer.service(__ServletContainer.java:381)
> at org.glassfish.jersey.servlet.__ServletContainer.doFilter(__ServletContainer.java:534)
> at org.glassfish.jersey.servlet.__ServletContainer.doFilter(__ServletContainer.java:482)
> at org.glassfish.jersey.servlet.__ServletContainer.doFilter(__ServletContainer.java:419)
> at org.eclipse.jetty.servlet.__ServletHandler$CachedChain.__doFilter(ServletHandler.java:__1650)
> at org.eclipse.jetty.servlet.__ServletHandler.doHandle(__ServletHandler.java:583)
> at org.eclipse.jetty.server.__handler.ScopedHandler.handle(__ScopedHandler.java:143)
> at org.eclipse.jetty.security.__SecurityHandler.handle(__SecurityHandler.java:577)
> at org.eclipse.jetty.server.__session.SessionHandler.__doHandle(SessionHandler.java:__223)
> at org.eclipse.jetty.server.__handler.ContextHandler.__doHandle(ContextHandler.java:__1125)
> at org.eclipse.jetty.servlet.__ServletHandler.doScope(__ServletHandler.java:515)
> at org.eclipse.jetty.server.__session.SessionHandler.__doScope(SessionHandler.java:__185)
> at org.eclipse.jetty.server.__handler.ContextHandler.__doScope(ContextHandler.java:__1059)
> at org.eclipse.jetty.server.__handler.ScopedHandler.handle(__ScopedHandler.java:141)
> at org.eclipse.jetty.server.__handler.HandlerWrapper.handle(__HandlerWrapper.java:97)
> at org.eclipse.jetty.server.__Server.handle(Server.java:485)
> at org.eclipse.jetty.server.__HttpChannel.handle(__HttpChannel.java:290)
> at org.eclipse.jetty.server.__HttpConnection.onFillable(__HttpConnection.java:248)
> at org.eclipse.jetty.io <http://org.eclipse.jetty.io>.__AbstractConnection$2.run(__AbstractConnection.java:540)
> at org.eclipse.jetty.util.thread.__QueuedThreadPool.runJob(__QueuedThreadPool.java:606)
> at org.eclipse.jetty.util.thread.__QueuedThreadPool$3.run(__QueuedThreadPool.java:535)
> at java.lang.Thread.run(Thread.__java:745)

> Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "PRIMARY KEY ON
> PUBLIC.USER(ID)"; SQL statement:
> insert into USER (EMAIL, PASSWORD, NAME, OWNED_PERMISSION_ID, OWNER_ID, VIEW_ID, EDIT_ID, DELETE_ID,
> VERSION, LAST_MODIFIED)
> values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-181]

> at org.h2.message.DbException.__getJdbcSQLException(__DbException.java:345)
> at org.h2.message.DbException.__get(DbException.java:179)
> at org.h2.message.DbException.__get(DbException.java:155)
> at org.h2.index.PageDataIndex.__getNewDuplicateKeyException(__PageDataIndex.java:165)
> at org.h2.index.PageDataIndex.__add(PageDataIndex.java:143)
> at org.h2.table.RegularTable.__addRow(RegularTable.java:119)
> at org.h2.command.dml.Insert.__insertRows(Insert.java:156)
> at org.h2.command.dml.Insert.__update(Insert.java:114)
> at org.h2.command.__CommandContainer.update(__CommandContainer.java:78)
> at org.h2.command.Command.__executeUpdate(Command.java:__254)
> at org.h2.jdbc.__JdbcPreparedStatement.__executeUpdateInternal(__JdbcPreparedStatement.java:__157)
> at org.h2.jdbc.__JdbcPreparedStatement.__executeUpdate(__JdbcPreparedStatement.java:__143)
> at com.jolbox.bonecp.__PreparedStatementHandle.__executeUpdate(__PreparedStatementHandle.java:__205)
> at com.mysema.query.sql.dml.__SQLInsertClause.__executeWithKeys(__SQLInsertClause.java:302)
> at com.mysema.query.sql.dml.__SQLInsertClause.__executeWithKey(__SQLInsertClause.java:186)
> at com.mysema.query.sql.dml.__SQLInsertClause.__executeWithKey(__SQLInsertClause.java:169)
> at com.vtlr.backend.row.User.__insert(User.java:94)

> ... 45 common frames omitted
>
> If I understand correctly, H2 is complaining that I am inserting a duplicate ID. But as you can see, the ID
> is AUTO_INCREMENT and I am not specifying an explicit value (not ever in any place in my code). Any ideas?
>
> This error is intermittent so I've been unable to create a testcase for it.
>
> Thanks,
> Gili
>
> --
> 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...@googlegroups.com.

> To post to this group, send email to h2-da...@googlegroups.com.

> Visit this group at http://groups.google.com/group/h2-database <http://groups.google.com/group/h2-database>.
> For more options, visit https://groups.google.com/d/optout <https://groups.google.com/d/optout>.

>
> --
> 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...@googlegroups.com <mailto:h2-database...@googlegroups.com>.
> To post to this group, send email to h2-da...@googlegroups.com <mailto:h2-da...@googlegroups.com>.

[cowwoc]

Hi Noel,

I tried reverting early on but (surprisingly) I ran into the problem in 1.3.176 as well.

Regarding the need for different isolation levels, my (web) application is partially at fault.

The default isolation mode might be very fast, but the application methods that INSERT require SERIALIZABLE isolation (more on this below) and because I cannot change the isolation level at runtime I am forced to use SERIALIZABLE across the board which ... as you can imagine ... is not great :)

Regarding needing SERIALIZABLE for INSERT, I didn't want to deal with the following scenario:

1. T1: Try to INSERT company Nintendo.
2. T1: INSERT failed due to a UNIQUE constraint violation (another company has the same name).
3. T2: DELETE company Nintendo.
4. T1: Look up the conflicting company by name ... oops, the record no longer exists.

So now T1 wants to report a conflict but doesn't have the necessary information to do so (the communication protocol requires me to provide the primary key of the conflicting row). Even if T1 could somehow repeat the operation (which it cannot due to http://stackoverflow.com/q/16628713/14731) there is no guarantee that T2 won't insert a new company with the same name (causing a second conflict to occur).

Anyway, this is why I am using READ_COMMITTED for all methods except for the ones that INSERT (which use SERIALIZABLE). Anyway... I hope this clarifies why I consider the lack of this feature as a showstopper.

Gili

[Noel Grandin]

Hi

You could try doing this, which is roughly equivalent to SERIALIZABLE mode:

exec("begin transaction")
var id = exec("select id from company where name = 'Nintendo'");
if (id != null) {
exec("commit");
throw new DuplicateCompanyException(id);
}
exec("insert into company(name) values('Nintendo')");
exec("commit");

Hmmm, it looks like in some places we add the key information when we throw a DUPLICATE_KEY_1 error.
I could certainly update the other places to also add that information, which you could extract by doing some string
munging.

Not ideal, but a relatively simple change.

Regards, Noel

On 2014-09-16 09:41 AM, cowwoc wrote:
> Hi Noel,
>
> I tried reverting early on but (surprisingly) I ran into the problem in 1.3.176 as well.
>
> Regarding the need for different isolation levels, my (web) application is partially at fault.
>
> The default isolation mode might be very fast, but the application methods that INSERT require SERIALIZABLE isolation
> (more on this below) and because I cannot change the isolation level at runtime I am forced to use SERIALIZABLE across
> the board which ... as you can imagine ... is not great :)
>
> Regarding needing SERIALIZABLE for INSERT, I didn't want to deal with the following scenario:
>

> 1. T1: Try to INSERT company Nintendo.
> 2. T1: INSERT failed due to a UNIQUE constraint violation (another company has the same name).
> 3. T2: DELETE company Nintendo.
> 4. T1: Look up the conflicting company by name ... oops, the record no longer exists.

[cowwoc]

Hi Noel,

This is already I what I do (in SERIALIZABLE mode). If you were to run
the same in READ_COMMITTED mode you could get a conflict on that last
INSERT. The problem with DUPLICATE_KEY_1 is the following:

Companies might have a UNIQUE constraint on column "name" but the web
service needs to return the "id" of the conflicting row (resources are
looked up by ID even though they conflict on name). In other words, in
case of a collision, I need to get back the entire conflicting record
... something that might not make sense in general for all developers.

I'm a bit surprised I am the only person running into this problem
though. I'd expect this to be more common. What are other people doing?

Gili

[Noel Grandin]

Why do you need to use READ_COMMITTED at all? Are we not fast enough without it?

[Thomas Mueller]

Hi,

As for high availability: 

* Did you look at the H2HA project? Would that be enough? If not why not. 

* Do you know MongoDB replication? Would that be better?

Regards,

Thomas

[cowwoc]

Hi Thomas,

On 17/09/2014 2:54 AM, Thomas Mueller wrote:

Hi,

As for high availability: 

* Did you look at the H2HA project? Would that be enough? If not why not.

Very briefly. I just emailed the author with a list of questions. There are two main reasons I am investigating Postgresql:

1. It's a more mature solution backed up by a large(r) development team. Sorry to say, but I worry about what would happen to this project or H2HA if one of you were to get hit by a bus or simply grow bored with the project and move on.
   
2. It has a more mature failover/clustering story. H2HA deals with failover but not clustering. H2 is certainly fast enough for my current needs, but I know that in the medium/long term I will need to deal with clustering across multiple machines.
   

* Do you know MongoDB replication? Would that be better?

I'm not familiar with it. What did you have in mind?

Thanks,
Gili

You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/tUdgguJPogg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[cowwoc]

(Very) good question.

It's hard for me to judge at this point because my application isn't
customer-facing yet and I don't have any metrics to measure against. So
by that yard stick, it's certainly good enough for now. I just antsy
about deadlocks and scalability problems once I go public. FYI:
Converting the database and creation script to Postgresql took around 4
days. With the migration nearly complete I'm not sure how to proceed
(whether to go back to H2 or continue with Postgresql).

I'll try to invest more energy tracking down this AUTO_INCREMENT problem
but thus far I've only been able to reproduce it 1 out of every 20 runs.
I've produced a testcase that inserted all possible values in the
SMALLINT range and the problem did not occur so at this point it's
looking increasingly likely that this is a race condition that only
occurs when two threads try to insert at the same time. I'll keep on trying.

Gili

[Noel Grandin]

Just for completeness - what does your H2 URL look like?

To be honest, if you are feeling the need to scale up, then PostgreSQL
is a good solution.
H2 is more focussed on ease of deployment and integration with Java,
so I doubt that we're ever going to play in the same performance and
scalability leagues as PostgreSQL.

[cowwoc]

On 17/09/2014 2:08 PM, Noel Grandin wrote:
> Just for completeness - what does your H2 URL look like?

jdbc:h2:c:/foo/bar/../database/target/database;MV_STORE=false;TRACE_LEVEL_FILE=4;LOCK_MODE=1

> To be honest, if you are feeling the need to scale up, then PostgreSQL
> is a good solution.
> H2 is more focussed on ease of deployment and integration with Java,
> so I doubt that we're ever going to play in the same performance and
> scalability leagues as PostgreSQL.

Thanks :) I agree that H2 can't be everything to everyone (and vice
versa, I wouldn't use Postgresql for a self-contained application).

Gili

[Thomas Mueller]

Hi,

I agree. H2 is "good enough" for many use cases (and for some use cases it's even better than other databases), but it just can't compete with PostgreSQL when you need certain features. It's nice if switching to PostgreSQL is relatively painless.

Regards,

Thomas

--
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...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.