Replacing records

Franklin Schmidt

unread,

Aug 22, 2009, 7:30:01 AM8/22/09

to h2-da...@googlegroups.com

I am looking for a safe way to replace records. MySQL supports a
"replace" comments, and Postgres has true versioning, so one can
safely delete and insert. But I can't find a safe way to do this with
H2. My test program is attached.

HU.java

MB

unread,

Aug 22, 2009, 8:27:15 AM8/22/09

to H2 Database

> HU.java

How about using "MERGE"?

Thomas Kellerer

unread,

Aug 22, 2009, 3:15:04 PM8/22/09

to H2 Database

On 22 Aug., 13:30, Franklin Schmidt <fschm...@gmail.com> wrote:
> Postgres has true versioning, so one can
> safely delete and insert.

I don't understand why you cannot use this approach in H2? You can
rollback any delete just as well.

Franklin Schmidt

unread,

Aug 23, 2009, 2:12:27 AM8/23/09

to h2-da...@googlegroups.com

On Sat, Aug 22, 2009 at 7:27 AM, MB<markus.ba...@liwest.at> wrote:
>
> How about using "MERGE"?

MERGE is just what I am looking for, except that it doesn't work. See
the attached program modified to use MERGE.

HU.java

Thomas Mueller

unread,

Aug 23, 2009, 4:17:16 AM8/23/09

to h2-da...@googlegroups.com

Hi,

I couldn't get it to work with PostgreSQL, the exception I get is:

org.postgresql.util.PSQLException: ERROR: duplicate key value violates
unique constraint "t_pkey"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:304)
at db.HU$1.run(HU.java:51)
at java.lang.Thread.run(Thread.java:613)

I don't know how to solve this yet using MVCC in H2. A workaround is
_not_ using MVCC.

Regards,
Thomas

HU.java

Franklin Schmidt

unread,

Aug 23, 2009, 5:24:10 AM8/23/09

to h2-da...@googlegroups.com

On Sun, Aug 23, 2009 at 3:17 AM, Thomas
Mueller<thomas.to...@gmail.com> wrote:
>
> I couldn't get it to work with PostgreSQL, the exception I get is:

You are right. But I have attached a modified version that works in
Postgres but fails in H2.

PU2.java

Thomas Mueller

unread,

Aug 28, 2009, 11:13:00 AM8/28/09

to h2-da...@googlegroups.com

Hi,

I believe the reason why it works with PostgreSQL is: creating a
connection takes a lot longer for PostgreSQL. If you add
Thread.sleep(100) before 'if (r == 0)' it also fails with PostgreSQL.
See http://h2database.com/p.html#6abe5ea085bb0ea87d57261323d786ef

Regards,
Thomas

Reply all

Reply to author

Forward