Postgres incorrectly quotes readback of sequence for IDENTITY IDs

Description

When using HBMs with:
<id name="accountId" column="`accountId`" type="long">
<generator class="identity" />
</id>

hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

  1. PostgreSQL (speed startup), not sure of relevance to the bug report
    hibernate.temp.use_jdbc_metadata_defaults=false

  2. as of 4.2.4 this only does tableName's
    hibernate.globally_quoted_identifiers=true

Hibernate produces SQL like: select currval('"master"."foo_Account"_"accountId"_seq')

The correct format would be:
select currval('"master"."foo_Account_accountId_seq"')

That is the concept of an identifier being the sequence name is a single construct that should be quoted. The use of apostrophe around the outer most part of the value is correct. The expression inside the string need to be a valid Postgres identifier expression.

I would guess hibernate is building the default sequence name out of the tableName + "_" + columnName + "_seq" format that postgres uses for SERIAL/BIGSERIAL.

What should happen is that if either the tableName or the columnName is using quoting, then the entire string should be quoted as a single identifer and the unquoted tableName and columnName be used inside.

StringBuilder sb = new StringBuilder();
sb.append(tableName.getUnquotedName()).append("_");
sb.append(columnName.getUnquotedName()).append("_seq");
if(tableName.isQuoted() || columnName.isQuoted())
return "\"" + sb.toString() + "\"";
return sb.toString();

Environment

Postgres Java7

Assignee

Unassigned

Reporter

Darryl Miles

Fix versions

None

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

Priority

Major
Configure