You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Johannes <jo...@gmail.com> on 2014/12/22 22:19:29 UTC
double primary key exception
Hello!
In some circumstances, which I don't know exactly, I have problems with
my sequences. It believe the postgres nextval is not called and with
some magic the next pk is incremented by something else.
Here the data:
I have a many-to-many relationship:
table:israelnationaltrail join_intcat category
seq: join_intcat_id_seq category_id_seq
------------------- ----------- --------
id PK id PK id PK
... int_id FK <- ...
cat_id FK ->
The sequences are registered as custom sequence in cayenne modeler.
My postgres log. As you can see at Category "Weberkogel" and "Gates in
Israel" there is no logged select nextval();
2014-12-22 16:22:21 CET LOG: Ausführen <unnamed>: SELECT
nextval('category_id_seq')
2014-12-22 16:22:21 CET LOG: Ausführen <unnamed>: INSERT INTO
"public"."category" ("id", "name") VALUES ($1, $2)
2014-12-22 16:22:21 CET DETAIL: Parameter: $1 = '59', $2 = ''
...
2014-12-22 17:23:31 CET LOG: Ausführen <unnamed>: SELECT
nextval('category_id_seq')
2014-12-22 17:23:31 CET LOG: Ausführen <unnamed>: INSERT INTO
"public"."category" ("id", "name") VALUES ($1, $2)
2014-12-22 17:23:31 CET DETAIL: Parameter: $1 = '60', $2 = 'Golan Heights'
...
2014-12-22 17:45:30 CET LOG: Ausführen <unnamed>: INSERT INTO
"public"."category" ("id", "name") VALUES ($1, $2)
2014-12-22 17:45:30 CET DETAIL: Parameter: $1 = '61', $2 = 'Weberkogel'
...
2014-12-22 17:45:42 CET LOG: Ausführen <unnamed>: INSERT INTO
"public"."category" ("id", "name") VALUES ($1, $2)
2014-12-22 17:45:42 CET DETAIL: Parameter: $1 = '62', $2 = 'Gates in
Israel'
...
2014-12-22 21:16:56 CET LOG: Ausführen <unnamed>: SELECT
nextval('category_id_seq')
2014-12-22 21:16:56 CET LOG: Ausführen <unnamed>: INSERT INTO
"public"."category" ("id", "name") VALUES ($1, $2)
2014-12-22 21:16:56 CET DETAIL: Parameter: $1 = '62', $2 =
'International borders of Lebanon'
2014-12-22 21:16:56 CET FEHLER: doppelter Schlüsselwert verletzt
Unique-Constraint »category_pkey«
2014-12-22 21:16:56 CET DETAIL: Schlüssel »(id)=(62)« existiert bereits.
2014-12-22 21:16:56 CET ANWEISUNG: INSERT INTO "public"."category"
("id", "name") VALUES ($1, $2)
2014-12-22 21:16:56 CET LOG: Ausführen S_3: ROLLBACK
test=> select * from category_id_seq ;
sequence_name | last_value | start_value | increment_by |
max_value | min_value | cache_value | log_cnt | is_cycled | is_called
-------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
categories_id_seq | 62 | 1 | 1 |
9223372036854775807 | 1 | 1 | 32 | f | t
(1 Zeile)
test=> select * from category where id >= 59;
id | name
----+-----------------
59 |
60 | Golan Heights
61 | Weberkogel
62 | Gates in Israel
(4 Zeilen)
Re: double primary key exception
Posted by Johannes <jo...@gmail.com>.
I think that helps! Thank you! Chears
Am 23.12.2014 um 00:16 schrieb Andrew Lindesay:
> Hello Johannes;
>
> Try to add in the following after the "db-key-generator" attribute.
>
> <db-key-cache-size>1</db-key-cache-size>
>
> See JdbcPkGenerator#generatePk for the problem - it's "cache value"
> differs from that of the sequence that you are using.
>
> cheers.
>
> On 23/12/14 11:54 am, Johannes wrote:
>> I did not understand your question completely. But here are DbEntity and
>> ObjEntity from my datamap.map.xml file.
>
Re: double primary key exception
Posted by Andrew Lindesay <ap...@lindesay.co.nz>.
Hello Johannes;
Try to add in the following after the "db-key-generator" attribute.
<db-key-cache-size>1</db-key-cache-size>
See JdbcPkGenerator#generatePk for the problem - it's "cache value"
differs from that of the sequence that you are using.
cheers.
On 23/12/14 11:54 am, Johannes wrote:
> I did not understand your question completely. But here are DbEntity and
> ObjEntity from my datamap.map.xml file.
--
Andrew Lindesay
Re: double primary key exception
Posted by Johannes <jo...@gmail.com>.
But this should be *not* the root cause. Or?
Am 22.12.2014 um 23:54 schrieb Johannes:
> But this should be the root cause.
Re: double primary key exception
Posted by Johannes <jo...@gmail.com>.
Hello Andrew.
I did not understand your question completely. But here are DbEntity and
ObjEntity from my datamap.map.xml file.
<obj-entity name="Category" className="net.jotpe.data.Category"
dbEntityName="category">
<obj-attribute name="id" type="int" db-attribute-path="id"/>
<obj-attribute name="name" type="java.lang.String"
db-attribute-path="name"/>
</obj-entity>
....
<db-entity name="category" schema="public">
<db-attribute name="id" type="INTEGER"
isPrimaryKey="true" isMandatory="true"/>
<db-attribute name="name" type="VARCHAR" length="500"/>
<db-key-generator>
<db-generator-type>ORACLE</db-generator-type>
<db-generator-name>category_id_seq</db-generator-name>
</db-key-generator>
</db-entity>
Hm, when I look at my table definition, I forgot the foreign key
constraint. But this should be the root cause.
test=> \d category
Tabelle »public.category«
Spalte | Typ | Attribute
--------+------------------------+-----------
id | integer | not null
name | character varying(500) |
Indexe:
"category_pkey" PRIMARY KEY, btree (id)
"category_name_key" UNIQUE CONSTRAINT, btree (name)
Regards Johannes
Am 22.12.2014 um 22:44 schrieb Andrew Lindesay:
> Hello Johannes;
>
> What is the value of settings under the "Primary Key" section in the
> modeller for the "category"?
>
> Regards;
>
>> In some circumstances, which I don't know exactly, I have problems with
>> my sequences. It believe the postgres nextval is not called and with
>> some magic the next pk is incremented by something else.
>
Re: double primary key exception
Posted by Andrew Lindesay <ap...@lindesay.co.nz>.
Hello Johannes;
What is the value of settings under the "Primary Key" section in the
modeller for the "category"?
Regards;
> In some circumstances, which I don't know exactly, I have problems with
> my sequences. It believe the postgres nextval is not called and with
> some magic the next pk is incremented by something else.
--
Andrew Lindesay