skapral skapral - 1 year ago 94
Java Question

How to manage PK generation with Cayenne 4.0 + PostgreSQL 9.4

I have:

  • PostgreSQL 9.4

  • Apache cayenne 4.0.M3

  • A schema which consists of one simplest table "proba":

    CREATE TABLE proba (
    id bigint NOT NULL,
    value character varying(255),
    CONSTRAINT proba_pkey PRIMARY KEY (id)

  • A simple Main method:

    public static void main(String[] args) {
    ServerRuntime runtime = ServerRuntimeBuilder.builder()

    ObjectContext ctx = runtime.newContext();

    CayenneDataObject newObject = new CayenneDataObject();
    newObject.writeProperty("value", "proba1");

  • A simple cayenne-project.xml:

    <?xml version="1.0" encoding="utf-8"?>
    <domain project-version="7">
    <map name="datamap"/>
    <node name="datanode"
    <map-ref name="datamap"/>

  • A simple (hand-maded):

    <?xml version="1.0" encoding="utf-8"?>
    <data-map xmlns=""
    <property name="defaultPackage" value=""/>
    <property name="defaultSchema" value="public"/>
    <db-entity name="proba" schema="public">
    <db-attribute name="id" type="BIGINT" isPrimaryKey="true" isGenerated="false" length="19"/>
    <db-attribute name="value" type="VARCHAR" length="255"/>
    <obj-entity name="Proba" dbEntityName="proba">
    <obj-attribute name="value" type="java.lang.String" db-attribute-path="value"/>

Trying it, I got the following output:

INFO: --- transaction started.
Nov 15, 2016 5:06:26 PM org.apache.cayenne.log.CommonsJdbcEventLogger logQuery
INFO: SELECT nextval('public.pk_proba')
Exception in thread "main" org.apache.cayenne.CayenneRuntimeException: [v.4.0.M3 Feb 08 2016 16:38:05] Commit Exception
at org.apache.cayenne.access.DataContext.flushToParent(
at org.apache.cayenne.access.DataContext.commitChanges(
at com.echelon.proba.cayenne.Main.main(
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.pk_proba" does not exist
Position: 16
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(
at org.postgresql.core.v3.QueryExecutorImpl.processResults(

So, cayenne expects a sequence named pk_proba. Why? I did't mean it to be generated. I didn't mentioned any postgresql sequences neither in my schema, nor in the Cayenne mappings.

So, I have two questions:

  • How can I suppress Cayenne attempts to generate ids and make Cayenne to fail fast if no identity is provided for specific entity at commit time?

  • May I customize a way how Cayenne manages PK auto-generation in my project (preferable solution would be the one without involving the Cayenne Modeller)?

Answer Source

TL;DR: "pk_proba" is the default name of a sequence to use for PK generation. If you want Cayenne default PK mechanism to function, you need to provide special sequences in PostgreSQL.

A longer version. You need to provide a PK to each inserted object one way or another. Cayenne PK generation algorithm roughly works like this:

  • If PK provided by the user as an object property, use it.
  • If PK is propagated from a master object via relationship, use it.
  • If PK is an auto_increment column in DB, use it (supported on PG since 4.0.M4)
  • If everything else failed, use Cayenne PK generator.

The last strategy requires you to prepare DB objects. Cayenne uses different strategies depending on the target DB. For PostgreSQL it will be sequences. In the Modeler go to "Tools > Generate Database Schema" and uncheck all checkboxes except "Create Primary Key Support". Then use the generated SQL to update your DB.

Now if you indeed want Cayenne to fail at step 4 (why though? you do want your insert to succeed after all), you can use a custom PkGenerator. Here is how you can load this via dependency injection using a custom DI module:

class CustomAdapterFactory extends DefaultDbAdapterFactory {
    public CustomAdapterFactory(
       List<DbAdapterDetector> detectors) {

    public DbAdapter createAdapter(
        DataNodeDescriptor nodeDescriptor, 
        DataSource dataSource) throws Exception {

        AutoAdapter adapter = 
           (AutoAdapter) super.createAdapter(nodeDescriptor, dataSource);

        // your PkGenerator goes here
        return adapter;

// add this when creating ServerRuntime
Module module = new Module() {
        public void configure(Binder binder) {


Admittedly this can be made easier (and we are planning to expose PkGenerator as a DI service), but it should work. Just make sure this is really what you need.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download