FWD » Core Development » Database

When performing database import on a fresh PostgreSQL database using 3821c/13704, we hit:

     [java] com.goldencode.p2j.persist.PersistenceException: Error executeUpdate: A result was returned when none was expected.
     [java] Caused by: org.postgresql.util.PSQLException: A result was returned when none was expected.
     [java]     at org.postgresql.jdbc.PgStatement.getNoResultUpdateCount(PgStatement.java:254)
     [java]     at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:122)
     [java]     at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:384)
     [java]     at com.goldencode.p2j.persist.orm.SQLQuery.executeUpdate(SQLQuery.java:445)
     [java]     at com.goldencode.p2j.schema.ImportWorker.createIdentitySequence(ImportWorker.java:516)
     [java]     at com.goldencode.p2j.schema.ImportWorker.access$1100(ImportWorker.java:311)
     [java]     at com.goldencode.p2j.schema.ImportWorker$Library.runImport(ImportWorker.java:976)
     [java]     at com.goldencode.expr.CE114.execute(Unknown Source)
     [java]     at com.goldencode.expr.Expression.execute(Expression.java:391)
     [java]     at com.goldencode.p2j.pattern.Rule.apply(Rule.java:497)
     [java]     at com.goldencode.p2j.pattern.RuleContainer.apply(RuleContainer.java:585)
     [java]     at com.goldencode.p2j.pattern.RuleSet.apply(RuleSet.java:98)
     [java]     at com.goldencode.p2j.pattern.PatternEngine.apply(PatternEngine.java:1670)
     [java]     at com.goldencode.p2j.pattern.PatternEngine.processAst(PatternEngine.java:1547)
     [java]     at com.goldencode.p2j.pattern.PatternEngine.processAst(PatternEngine.java:1484)
     [java]     at com.goldencode.p2j.pattern.PatternEngine.run(PatternEngine.java:1037)
     [java]     at com.goldencode.p2j.pattern.PatternEngine.main(PatternEngine.java:2143)

Ovidiu described the root cause as follows:

The other dialects use alter sequence statement for updating the sequence values but pgsql's syntax for this is select setval(). So it will return a value. OTOH, there is an alter sequence in pgsql, but it is equivalent to SELECT setval('myseq', <n>, false); meaning the first call to nextval will NOT increase its value. While this is not what OE expects, it is what the statements do for other dialects and an additional statement is necessary to "burn" the first value. So, instead of checking whether the current dialect is pgsql I think it would be better to unify the implementation across all supported dialects. The performance cost of an extra statement should be really negligible because the set-value for sequences is seldom called in 4GL applications.

The problem can be recreated with a fresh installation of Hotel GUI, using ant deploy.all or ant import.db. It occurs when the identity sequence is first created, after import completes.

In revision 3821c/13746 I added some code to detect whether the query string returned by p2j dialect when updating the sequence value is a query (so an result is expected) and use a different API for executing it.

I think this is a viable alternative to my idea from first note. If we decide to unify set-value sequence support for all dialect, this task can be track its progress. If the current solution of locally analysing the statement string and invoke the appropriate jdbc call so no exception is thrown (this way avoiding the exception) is better (also because it avoids altering implementation for set-value in case of psql with associated risks), then this task can be closed.