CODEWITHSUNDEEP
javapostgresqljpa
greengold
greengold

Reputation: 1333

JPA 2 with PostgreSQL initial sequence pooling and alteration

I have Java SE application using Eclipselink as persistence provider for Postgres database and I am experiencing odd behaviour upon application initialization. Thing is that one of my entities has ID setted to be pooled from the postgres' sequence. Upon initialization of application, with logging level setted to fine I can see that this ID is fetched two times and so is sequence incremented by 1 each time. log:

 [EL Info]: 2015-01-22 19:25:13.659--ServerSession(1107730949)--Thread(Thread[main,5,main])--EclipseLink, version: Eclipse Persistence Services - 2.5.0.v20130507-3faac2b
[EL Fine]: connection: 2015-01-22 19:25:13.751--Thread(Thread[main,5,main])--Detected database platform: org.eclipse.persistence.platform.database.PostgreSQLPlatform
[EL Config]: connection: 2015-01-22 19:25:13.766--ServerSession(1107730949)--Connection(1370979551)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
    platform=>PostgreSQLPlatform
    user name=> "intime"
    datasource URL=> "jdbc:postgresql://localhost:5432/InTime?charSet=LATIN2"
))
[EL Config]: connection: 2015-01-22 19:25:13.77--ServerSession(1107730949)--Connection(1142931081)--Thread(Thread[main,5,main])--Connected: jdbc:postgresql://localhost:5432/InTime?charSet=LATIN2
    User: intime
    Database: PostgreSQL  Version: 9.3.5
    Driver: PostgreSQL Native Driver  Version: PostgreSQL 9.3 JDBC4.1 (build 1102)
[EL Info]: connection: 2015-01-22 19:25:13.901--ServerSession(1107730949)--Thread(Thread[main,5,main])--file:/home/tepo/IdeaProjects/WebParser/target/classes/_parser_writable login successful
[EL Fine]: sql: 2015-01-22 19:25:13.986--ServerSession(1107730949)--Connection(1142931081)--Thread(Thread[main,5,main])--select nextval(timetable)
[EL Fine]: sql: 2015-01-22 19:25:13.99--ServerSession(1107730949)--Connection(1142931081)--Thread(Thread[main,5,main])--ALTER SEQUENCE timetable INCREMENT BY 1
[EL Fine]: sql: 2015-01-22 19:25:13.992--ServerSession(1107730949)--Connection(1142931081)--Thread(Thread[main,5,main])--select nextval(timetable)
[EL Fine]: sql: 2015-01-22 19:25:13.993--ServerSession(1107730949)--Connection(1142931081)--Thread(Thread[main,5,main])--ALTER SEQUENCE timetable INCREMENT BY 1
[EL Fine]: sql: 2015-01-22 19:25:14.041--ServerSession(1107730949)--Connection(1142931081)--Thread(Thread[main,5,main])--SELECT ID, CODE, DAYCAT FROM "public"."DAYCATCODEMAP"
[EL Fine]: sql: 2015-01-22 19:25:14.049--ServerSession(1107730949)--Connection(1142931081)--Thread(Thread[main,5,main])--SELECT ID, CODE, READABLE FROM "public"."CT_EXCLUSION"
[EL Fine]: sql: 2015-01-22 19:25:14.05--ServerSession(1107730949)--Connection(1142931081)--Thread(Thread[main,5,main])--SELECT ID, ABBREVIATION, FULLNAME FROM "public"."REGION"

This happens on very beginning of app initialization even before entityManagerFactory is initialized.

at the end this causes insetrion of record with shifted ID when application is running. Here is an entity:

@Table(name = "\"TIMETABLE\"", catalog = "public")
@Entity
@NamedQuery(
        name = "deleteTimetableByIds",
        query = "DELETE FROM Timetable T where T.id = :id"
)
@SequenceGenerator(name="TTBL_SEQ", sequenceName="timetable", allocationSize = 1)
public class Timetable{

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "TTBL_SEQ")
    private Long id;
...
}

persistence.xml:

<persistence-unit name="parser_writable" transaction-type="RESOURCE_LOCAL">

    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>

    <properties>
        <property name="javax.persistence.target-database" value="PostgreSQL" />
        <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>
        <property name="javax.persistence.jdbc.url"
                  value="jdbc:postgresql://localhost:5432/InTime?charSet=LATIN2"/>
        <property name="javax.persistence.jdbc.user" value="intime"/>
        <property name="javax.persistence.jdbc.password" value="intime"/>

        <!-- EclipseLink should create the database schema automatically -->
        <property name="eclipselink.ddl-generation" value="create-or-extend-tables"/>
        <property name="eclipselink.ddl-generation.output-mode"
                  value="database"/>
        <property name="eclipselink.logging.level" value="FINE"/>
    </properties>
</persistence-unit>

so, it is happening when I am initializing a PersistenceManager class which implies this method:

private static EntityManager em_write = null;
    private static EntityManagerFactory factory;

    public static void initializeEntityManager() {
        if (em_write == null) {
            factory = Persistence.createEntityManagerFactory("parser_writable");
            em_write = factory.createEntityManager();
        }

Obvious question is why is this happening and how to avoid it?

Upvotes: 2

Views: 396

Answers (2)

greengold
greengold

Reputation: 1333

got that!

<property name="eclipselink.ddl-generation" value="create-or-extend-tables"/>

is causing this trouble. Maybe I should raise a bug report..

Upvotes: 1

void
void

Reputation: 7890

I think the problem is: you are using @SequenceGenerator on the top of class try with using @SequenceGenerator and @GeneratedValue annotations only for the pk/unique column:

@Id
@SequenceGenerator(name="TTBL_SEQ", sequenceName="timetable", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "TTBL_SEQ")
private Long id;

Upvotes: 0

Related Questions