FAQ
Hello

I have run into a problem which seems to be a bug in cayenne. I have
tried several approaches in order to isolate the direct cause, thinking
it is some kind of misconfiguration in my code, yet no luck.


Using cayenne 3.0, derby (not tested on other db so far).
My cayenne model defines an entity called SiteTagRelation, inheriting
from TagRelation.
TagRelation is based on database entity with two important fields:
entityRecordId and entityIdentifier. The latter is used for inheritance,
identifying the type of TagRelation, the former is used as foreign key.
Both fields are mandatory, and there is an unique index on the
TagRelation table, created with following :
CREATE UNIQUE INDEX TagRelation_unique ON TagRelation (nodeId,
entityIdentifier, entityRecordId)

For this example the final relationships are as follow: Site -<
SiteTagRelation >- Tag

Now a simple scenario of:
- getting a context
- fetching a Site
- deleting one SiteTagRelation using objectContext.deleteObject()
- re-creating new SiteTagRelation to the same Tag and Site using
objectContext.newObject()

At this moment the context contains:
Tag, persistent state = committed
Site, persistent state = committed
SiteTagRelation, persistent state = deleted
SiteTagRelation, persistent state = new

but commitChanges() yields
java.sql.SQLIntegrityConstraintViolationException: The statement was
aborted because it would have caused a duplicate key value in a unique
or primary key constraint or unique index identified by
'TAGRELATION_UNIQUE' defined on 'TAGRELATION'.


Please can someone confirm that they have the same problem... or not,
which means I have to dig deeper into my code and find what I'm doing wrong.

Best regards!
Marcin
-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001

Search Discussions

  • Michael Gentry at Feb 15, 2011 at 1:48 pm
    Hi Marcin,

    Most likely your unique index is causing some grief. Any chance you
    are logging the SQL and can post it?

    Also, is "entityIdentifier" being used for inheritance in Cayenne?
    (Did you fill something in the "Qualifier" field in the ObjEntity?)
    If so, it sounds like you are using it as a singleton?

    Thanks,

    mrg

    On Mon, Feb 14, 2011 at 10:04 PM, Marcin Skladaniec wrote:
    Hello

    I have run into a problem which seems to be a bug in cayenne. I have tried
    several approaches in order to isolate the direct cause, thinking it is some
    kind of misconfiguration in my code, yet no luck.


    Using  cayenne 3.0, derby (not tested on other db so far).
    My cayenne model defines an entity called SiteTagRelation, inheriting from
    TagRelation.
    TagRelation is based on database entity with two important fields:
    entityRecordId and entityIdentifier. The latter is used for inheritance,
    identifying the type of TagRelation, the former is used as foreign key. Both
    fields are mandatory, and there is an unique index on the TagRelation table,
    created with following :
    CREATE UNIQUE INDEX TagRelation_unique ON TagRelation (nodeId,
    entityIdentifier, entityRecordId)

    For this example the final relationships are as follow: Site -<
    SiteTagRelation >- Tag

    Now a simple scenario of:
    - getting a context
    - fetching a Site
    - deleting one SiteTagRelation using objectContext.deleteObject()
    - re-creating new SiteTagRelation to the same Tag and Site using
    objectContext.newObject()

    At this moment the context contains:
    Tag, persistent state = committed
    Site, persistent state = committed
    SiteTagRelation, persistent state = deleted
    SiteTagRelation, persistent state = new

    but commitChanges() yields
    java.sql.SQLIntegrityConstraintViolationException: The statement was aborted
    because it would have caused a duplicate key value in a unique or primary
    key constraint or unique index identified by 'TAGRELATION_UNIQUE' defined on
    'TAGRELATION'.


    Please can someone confirm that they have the same problem... or not, which
    means I have to dig deeper into my code and find what I'm doing wrong.

    Best regards!
    Marcin
    -------------------------->
    ish
    http://www.ish.com.au
    Level 1, 30 Wilson Street Newtown 2042 Australia
    phone +61 2 9550 5001   fax +61 2 9550 4001
  • Marcin Skladaniec at Feb 16, 2011 at 2:55 am
    Hi Michael

    Yes, the qualifier is filled in. This cayenne model was in use for a
    long time, we only added some NOT NULL and UNIQUE constraints recently,
    and now the error showed up.


    This is what I do (in java) and relevant sql logs (simplified):
    DataContext cc =
    AngelServer.getDatabaseConnector().getNewContext();
    Site s = queryForTheSite();
    SiteTagRelation str = s.getTaggingRelations().get(0);
    Tag t = str.getTag();

    SELECT t0.createdOn, t0.id... FROM Site t0
    org.apache.cayenne.access.QueryLogger :401 - === returned 1 row. - took
    127 ms.

    SELECT t0.createdOn, t0.id... FROM TagRelation t0 WHERE
    (t0.entityRecordId = ?) AND (t0.entityIdentifier = ?) [bind:
    1->entityRecordId:280, 2->entityIdentifier:9]
    org.apache.cayenne.access.QueryLogger :401 - === returned 1 row. - took
    164 ms.

    SELECT t0.createdOn, t0.id... FROM Tag t0 WHERE t0.id = ? [bind: 1->id:460]
    org.apache.cayenne.access.QueryLogger :401 - === returned 1 row. - took
    169 ms.


    logger.warn("deleting tag relation: " + str);
    cc.deleteObject(str);
    logger.warn("tag relation deleted...");

    deleting tag relation: {<ObjectId:SiteTagRelation, id=542>; committed;
    [tag=>?; entityIdentifier=>9; taggedSite=>?]}
    tag relation deleted...


    SiteTagRelation strNew = cc.newObject(SiteTagRelation.class);
    strNew.setTag(t);
    strNew.setTaggedRelation(s);
    logger.warn("tag relation created: " + strNew);

    tag relation created: {<ObjectId:SiteTagRelation,
    TEMP:0000004ABC950164>; new; [tag=>{<ObjectId:Tag, id=460>};
    entityIdentifier=>9; taggedSite=>{<ObjectId:Site, id=280>}]}

    cc.commitChanges();

    SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE TABLE_NAME = ? FOR UPDATE
    [bind: 1:'TagRelation']
    INSERT INTO TagRelation (entityIdentifier, entityRecordId, id, tagId)
    VALUES (?, ?, ?, ?)

    org.apache.cayenne.access.QueryLogger :372 - [batch bind:
    1->entityIdentifier:9, 2->entityRecordId:280, 3->id:560, 5->tagId:460]
    org.apache.cayenne.access.QueryLogger :453 - *** error.
    java.sql.SQLIntegrityConstraintViolationException: The statement was
    aborted because it would have caused a duplicate key value in a unique
    or primary key constraint or unique index identified by
    'TAGRELATION_UNIQUE' defined on 'TAGRELATION'.


    As you can see the insert is executed before delete.

    If the context is committed after deleting its all fine.

    As you can guess nullifying the relationships manually before deleting
    object, makes no difference.

    With regards
    Marcin
    On 15/02/11 11:47 PM, Michael Gentry wrote:
    Hi Marcin,

    Most likely your unique index is causing some grief. Any chance you
    are logging the SQL and can post it?

    Also, is "entityIdentifier" being used for inheritance in Cayenne?
    (Did you fill something in the "Qualifier" field in the ObjEntity?)
    If so, it sounds like you are using it as a singleton?

    Thanks,

    mrg


    On Mon, Feb 14, 2011 at 10:04 PM, Marcin Skladaniecwrote:
    Hello

    I have run into a problem which seems to be a bug in cayenne. I have tried
    several approaches in order to isolate the direct cause, thinking it is some
    kind of misconfiguration in my code, yet no luck.


    Using cayenne 3.0, derby (not tested on other db so far).
    My cayenne model defines an entity called SiteTagRelation, inheriting from
    TagRelation.
    TagRelation is based on database entity with two important fields:
    entityRecordId and entityIdentifier. The latter is used for inheritance,
    identifying the type of TagRelation, the former is used as foreign key. Both
    fields are mandatory, and there is an unique index on the TagRelation table,
    created with following :
    CREATE UNIQUE INDEX TagRelation_unique ON TagRelation (nodeId,
    entityIdentifier, entityRecordId)

    For this example the final relationships are as follow: Site -<
    SiteTagRelation>- Tag

    Now a simple scenario of:
    - getting a context
    - fetching a Site
    - deleting one SiteTagRelation using objectContext.deleteObject()
    - re-creating new SiteTagRelation to the same Tag and Site using
    objectContext.newObject()

    At this moment the context contains:
    Tag, persistent state = committed
    Site, persistent state = committed
    SiteTagRelation, persistent state = deleted
    SiteTagRelation, persistent state = new

    but commitChanges() yields
    java.sql.SQLIntegrityConstraintViolationException: The statement was aborted
    because it would have caused a duplicate key value in a unique or primary
    key constraint or unique index identified by 'TAGRELATION_UNIQUE' defined on
    'TAGRELATION'.


    Please can someone confirm that they have the same problem... or not, which
    means I have to dig deeper into my code and find what I'm doing wrong.

    Best regards!
    Marcin
    -------------------------->
    ish
    http://www.ish.com.au
    Level 1, 30 Wilson Street Newtown 2042 Australia
    phone +61 2 9550 5001 fax +61 2 9550 4001
  • Aristedes Maniatis at Feb 17, 2011 at 8:11 am

    On 15/02/11 2:04 PM, Marcin Skladaniec wrote:
    java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'TAGRELATION_UNIQUE' defined on 'TAGRELATION'.

    Can I summarise, since I think you've highlighted a problem which is completely different to your email subject.

    1. Table A has primary key which is user defined (in this case, it is the join between two other tables, so the PK is the compound of the two foreign keys)

    2. Create record in table A. Commit.

    3. Delete that record in table A.

    4. Create another record in table A with the same PK as the one just deleted (since it joins the same other two tables).

    5. Commit -> ERRROR


    The create in step 4 is being committed to the db before the delete in step 3, failing the key constraint.


    Use case
    --------
    You may wonder why on earth this is happening. Well, in a rich client application a user might tick a checkbox to link two records and then untick it and tick it again while they make up their mind. All those changes result in changes to the context.


    Solution
    --------

    A. Dedupe the overlapping create/delete records in the context before committing.
    B. Order the SQL better.



    Any thoughts about how Cayenne could deal with this better? What do other ORMs do in this scenario?



    Ari


    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Patrick Mayweg at Feb 17, 2011 at 8:29 am
    Hi Aristedes,
    in our Cayenne application, we have the use case where a row with a unique
    value ist first deleted and new row with different values in the other
    columns is created.
    For that problem only putting the delete in front of the insert would
    help.
    Regards,
    Patrick




    Aristedes Maniatis <ari@maniatis.org>
    17.02.2011 09:11
    Bitte antworten an
    user@cayenne.apache.org


    An
    user@cayenne.apache.org
    Kopie

    Thema
    Re: possible bug with inherited cayenne objects and mandatory fields





    On 15/02/11 2:04 PM, Marcin Skladaniec wrote:
    java.sql.SQLIntegrityConstraintViolationException: The statement was
    aborted because it would have caused a duplicate key value in a unique or
    primary key constraint or unique index identified by 'TAGRELATION_UNIQUE'
    defined on 'TAGRELATION'.


    Can I summarise, since I think you've highlighted a problem which is
    completely different to your email subject.

    1. Table A has primary key which is user defined (in this case, it is the
    join between two other tables, so the PK is the compound of the two
    foreign keys)

    2. Create record in table A. Commit.

    3. Delete that record in table A.

    4. Create another record in table A with the same PK as the one just
    deleted (since it joins the same other two tables).

    5. Commit -> ERRROR


    The create in step 4 is being committed to the db before the delete in
    step 3, failing the key constraint.


    Use case
    --------
    You may wonder why on earth this is happening. Well, in a rich client
    application a user might tick a checkbox to link two records and then
    untick it and tick it again while they make up their mind. All those
    changes result in changes to the context.


    Solution
    --------

    A. Dedupe the overlapping create/delete records in the context before
    committing.
    B. Order the SQL better.



    Any thoughts about how Cayenne could deal with this better? What do other
    ORMs do in this scenario?



    Ari


    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Bryan Lewis at Feb 17, 2011 at 1:30 pm
    This sounds like the same problem we've lived with for years. In a
    complicated model (hundreds of entities for us), Cayenne's graph-following
    algorithm (AshwoodEntitySorter) doesn't have enough information to perform
    all the commits in exactly the right order. (Perhaps this is an argument
    for supporting database constraints in the modeler, so that Cayenne would
    know about them.)

    If multiple interrelated entities are inserted or deleted in the same
    commit, they can occasionally get committed out of order. This matters if
    your database is fully constrained. (For us the error usually involves a
    foreign-key constraint. I've wondered why other people weren't asking about
    this problem more often, and assumed that most people didn't have such large
    models, or seldom did multi-entiy commits, or didn't add constraints to the
    database.)

    Anyway, the fix is to override the EntitySorter and explicitly tell it the
    order in each case that arises.


    // We call this to initialize our model... the first time a DataContext
    is created.
    private static synchronized Configuration initConfiguration()
    {
    ...
    DataDomain domain = config.getDomain();
    // We have only one DataNode.
    DataNode node = domain.getDataNodes().iterator().next();

    node.setEntitySorter(new CustomEntitySorter());
    ...
    }

    public class CustomEntitySorter extends AshwoodEntitySorter
    {
    public CustomEntitySorter()
    {
    super(Collections.EMPTY_LIST);
    }

    @Override
    public void sortDbEntities(List<DbEntity> dbEntities, boolean
    deleteOrder)
    {
    // Somewhat ugly code, depending on Cayenne internals.
    _indexSorter();
    Collections.sort(dbEntities, getDbEntityComparator(deleteOrder));

    // No need to check the ordering of a single-entity list.
    if (dbEntities.size() <= 1) {
    return;
    }

    // Two examples. In our apps we have a few dozen of these lines.
    putParentBeforeChild("contact", "note", dbEntities, deleteOrder);
    putParentBeforeChild("company", "companyphone", dbEntities,
    deleteOrder);
    }

    /**
    * If the parent object comes after the child in the list, move it
    before
    * the child. Unless we're deleting, which reverses the order.
    *
    * The naming might be a bit confusing. I'm calling the parent the
    * entity that appears to be the source of the relationship in the
    modeler.
    * The parent record needs to be inserted first.
    *
    * The confusing thing is, the parent table is the one pointed *to* by
    * the FK constraint, and the child is the table that defines the
    constraint.
    */
    private void putParentBeforeChild(String parentDbEntityName,
    String childDbEntityName,
    List<DbEntity> dbEntities,
    boolean deleteOrder)
    {
    int iParent = -1;
    int iChild = -1;

    // Protect against a misspelling of an entity name, which I did
    once.
    List<String> allDbEntityNames =
    Model.getInstance().getDbEntityNames();
    if (!allDbEntityNames.contains(parentDbEntityName)) {
    log.error("!! Unrecognized parentDbEntity name " +
    parentDbEntityName);
    }
    if (!allDbEntityNames.contains(childDbEntityName)) {
    log.error("!! Unrecognized childDbEntity name " +
    childDbEntityName);
    }

    for (int i = 0; i < dbEntities.size(); i++) {
    DbEntity entity = dbEntities.get(i);
    String name = entity.getName();
    // Use equalsIgnoreCase in case we're switching between Oracle
    and Postgres databases.
    if (name.equalsIgnoreCase(parentDbEntityName) && iParent == -1)
    {
    iParent = i;
    }
    // Check for -1 so that we get the lowest child in case there
    // are more than one. Might not ever matter.
    if (name.equalsIgnoreCase(childDbEntityName) && iChild == -1) {
    iChild = i;
    }
    }

    if (iParent != -1 && iChild != -1) {
    StringBuilder buf = new StringBuilder();
    if (deleteOrder) {
    if (iParent < iChild) {
    buf.append("-- need to move
    ").append(childDbEntityName);
    buf.append(" before ").append(parentDbEntityName);
    buf.append(" for deletion");
    log.debug(buf.toString());

    // Remove and re-add it just before the parent.
    DbEntity childEntity = dbEntities.remove(iChild);
    dbEntities.add(iParent, childEntity);
    }
    }
    else {
    if (iParent > iChild) {
    buf.append("-- need to move
    ").append(parentDbEntityName);
    buf.append(" before ").append(childDbEntityName);
    buf.append(" for insertion");
    log.debug(buf.toString());

    // Remove and re-add it just before the child.
    DbEntity parentEntity = dbEntities.remove(iParent);
    dbEntities.add(iChild, parentEntity);
    }
    }
    }
    }
    }


    On Thu, Feb 17, 2011 at 3:11 AM, Aristedes Maniatis wrote:
    On 15/02/11 2:04 PM, Marcin Skladaniec wrote:

    java.sql.SQLIntegrityConstraintViolationException: The statement was
    aborted because it would have caused a duplicate key value in a unique or
    primary key constraint or unique index identified by 'TAGRELATION_UNIQUE'
    defined on 'TAGRELATION'.

    Can I summarise, since I think you've highlighted a problem which is
    completely different to your email subject.

    1. Table A has primary key which is user defined (in this case, it is the
    join between two other tables, so the PK is the compound of the two foreign
    keys)

    2. Create record in table A. Commit.

    3. Delete that record in table A.

    4. Create another record in table A with the same PK as the one just
    deleted (since it joins the same other two tables).

    5. Commit -> ERRROR


    The create in step 4 is being committed to the db before the delete in step
    3, failing the key constraint.


    Use case
    --------
    You may wonder why on earth this is happening. Well, in a rich client
    application a user might tick a checkbox to link two records and then untick
    it and tick it again while they make up their mind. All those changes result
    in changes to the context.


    Solution
    --------

    A. Dedupe the overlapping create/delete records in the context before
    committing.
    B. Order the SQL better.



    Any thoughts about how Cayenne could deal with this better? What do other
    ORMs do in this scenario?



    Ari


    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Andrus Adamchik at Feb 17, 2011 at 1:53 pm
    Then there are cases that don't have correct ordering at all. Usually this involves cyclic relationships. I don't have an example handy, but IIRC we discussed that some time ago.

    Andrus

    On Feb 17, 2011, at 3:29 PM, Bryan Lewis wrote:

    This sounds like the same problem we've lived with for years. In a
    complicated model (hundreds of entities for us), Cayenne's graph-following
    algorithm (AshwoodEntitySorter) doesn't have enough information to perform
    all the commits in exactly the right order. (Perhaps this is an argument
    for supporting database constraints in the modeler, so that Cayenne would
    know about them.)

    If multiple interrelated entities are inserted or deleted in the same
    commit, they can occasionally get committed out of order. This matters if
    your database is fully constrained. (For us the error usually involves a
    foreign-key constraint. I've wondered why other people weren't asking about
    this problem more often, and assumed that most people didn't have such large
    models, or seldom did multi-entiy commits, or didn't add constraints to the
    database.)

    Anyway, the fix is to override the EntitySorter and explicitly tell it the
    order in each case that arises.


    // We call this to initialize our model... the first time a DataContext
    is created.
    private static synchronized Configuration initConfiguration()
    {
    ...
    DataDomain domain = config.getDomain();
    // We have only one DataNode.
    DataNode node = domain.getDataNodes().iterator().next();

    node.setEntitySorter(new CustomEntitySorter());
    ...
    }

    public class CustomEntitySorter extends AshwoodEntitySorter
    {
    public CustomEntitySorter()
    {
    super(Collections.EMPTY_LIST);
    }

    @Override
    public void sortDbEntities(List<DbEntity> dbEntities, boolean
    deleteOrder)
    {
    // Somewhat ugly code, depending on Cayenne internals.
    _indexSorter();
    Collections.sort(dbEntities, getDbEntityComparator(deleteOrder));

    // No need to check the ordering of a single-entity list.
    if (dbEntities.size() <= 1) {
    return;
    }

    // Two examples. In our apps we have a few dozen of these lines.
    putParentBeforeChild("contact", "note", dbEntities, deleteOrder);
    putParentBeforeChild("company", "companyphone", dbEntities,
    deleteOrder);
    }

    /**
    * If the parent object comes after the child in the list, move it
    before
    * the child. Unless we're deleting, which reverses the order.
    *
    * The naming might be a bit confusing. I'm calling the parent the
    * entity that appears to be the source of the relationship in the
    modeler.
    * The parent record needs to be inserted first.
    *
    * The confusing thing is, the parent table is the one pointed *to* by
    * the FK constraint, and the child is the table that defines the
    constraint.
    */
    private void putParentBeforeChild(String parentDbEntityName,
    String childDbEntityName,
    List<DbEntity> dbEntities,
    boolean deleteOrder)
    {
    int iParent = -1;
    int iChild = -1;

    // Protect against a misspelling of an entity name, which I did
    once.
    List<String> allDbEntityNames =
    Model.getInstance().getDbEntityNames();
    if (!allDbEntityNames.contains(parentDbEntityName)) {
    log.error("!! Unrecognized parentDbEntity name " +
    parentDbEntityName);
    }
    if (!allDbEntityNames.contains(childDbEntityName)) {
    log.error("!! Unrecognized childDbEntity name " +
    childDbEntityName);
    }

    for (int i = 0; i < dbEntities.size(); i++) {
    DbEntity entity = dbEntities.get(i);
    String name = entity.getName();
    // Use equalsIgnoreCase in case we're switching between Oracle
    and Postgres databases.
    if (name.equalsIgnoreCase(parentDbEntityName) && iParent == -1)
    {
    iParent = i;
    }
    // Check for -1 so that we get the lowest child in case there
    // are more than one. Might not ever matter.
    if (name.equalsIgnoreCase(childDbEntityName) && iChild == -1) {
    iChild = i;
    }
    }

    if (iParent != -1 && iChild != -1) {
    StringBuilder buf = new StringBuilder();
    if (deleteOrder) {
    if (iParent < iChild) {
    buf.append("-- need to move
    ").append(childDbEntityName);
    buf.append(" before ").append(parentDbEntityName);
    buf.append(" for deletion");
    log.debug(buf.toString());

    // Remove and re-add it just before the parent.
    DbEntity childEntity = dbEntities.remove(iChild);
    dbEntities.add(iParent, childEntity);
    }
    }
    else {
    if (iParent > iChild) {
    buf.append("-- need to move
    ").append(parentDbEntityName);
    buf.append(" before ").append(childDbEntityName);
    buf.append(" for insertion");
    log.debug(buf.toString());

    // Remove and re-add it just before the child.
    DbEntity parentEntity = dbEntities.remove(iParent);
    dbEntities.add(iChild, parentEntity);
    }
    }
    }
    }
    }


    On Thu, Feb 17, 2011 at 3:11 AM, Aristedes Maniatis wrote:
    On 15/02/11 2:04 PM, Marcin Skladaniec wrote:

    java.sql.SQLIntegrityConstraintViolationException: The statement was
    aborted because it would have caused a duplicate key value in a unique or
    primary key constraint or unique index identified by 'TAGRELATION_UNIQUE'
    defined on 'TAGRELATION'.

    Can I summarise, since I think you've highlighted a problem which is
    completely different to your email subject.

    1. Table A has primary key which is user defined (in this case, it is the
    join between two other tables, so the PK is the compound of the two foreign
    keys)

    2. Create record in table A. Commit.

    3. Delete that record in table A.

    4. Create another record in table A with the same PK as the one just
    deleted (since it joins the same other two tables).

    5. Commit -> ERRROR


    The create in step 4 is being committed to the db before the delete in step
    3, failing the key constraint.


    Use case
    --------
    You may wonder why on earth this is happening. Well, in a rich client
    application a user might tick a checkbox to link two records and then untick
    it and tick it again while they make up their mind. All those changes result
    in changes to the context.


    Solution
    --------

    A. Dedupe the overlapping create/delete records in the context before
    committing.
    B. Order the SQL better.



    Any thoughts about how Cayenne could deal with this better? What do other
    ORMs do in this scenario?



    Ari


    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Aristedes Maniatis at Feb 17, 2011 at 10:25 pm

    On 18/02/11 12:53 AM, Andrus Adamchik wrote:
    Then there are cases that don't have correct ordering at all. Usually this involves cyclic relationships. I don't have an example handy, but IIRC we discussed that some time ago.
    Would it make sense to perform all deletes before all inserts? Or are there situations where that is not correct?

    Ari

    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Andrus Adamchik at Feb 18, 2011 at 7:41 am
    There are - to one relationships. E.g. X1 (deleted), X2 (inserted), Y1 (updated FK from X1 to X2).

    Andrus
    On Feb 18, 2011, at 12:25 AM, Aristedes Maniatis wrote:
    On 18/02/11 12:53 AM, Andrus Adamchik wrote:
    Then there are cases that don't have correct ordering at all. Usually this involves cyclic relationships. I don't have an example handy, but IIRC we discussed that some time ago.
    Would it make sense to perform all deletes before all inserts? Or are there situations where that is not correct?

    Ari

    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Mike Kienenberger at Feb 17, 2011 at 2:09 pm
    Solution C: assign a meaningless primary key to the join table.

    Obviously, this isn't an option for everyone.
    On Thu, Feb 17, 2011 at 3:11 AM, Aristedes Maniatis wrote:
    On 15/02/11 2:04 PM, Marcin Skladaniec wrote:

    java.sql.SQLIntegrityConstraintViolationException: The statement was
    aborted because it would have caused a duplicate key value in a unique or
    primary key constraint or unique index identified by 'TAGRELATION_UNIQUE'
    defined on 'TAGRELATION'.

    Can I summarise, since I think you've highlighted a problem which is
    completely different to your email subject.

    1. Table A has primary key which is user defined (in this case, it is the
    join between two other tables, so the PK is the compound of the two foreign
    keys)

    2. Create record in table A. Commit.

    3. Delete that record in table A.

    4. Create another record in table A with the same PK as the one just deleted
    (since it joins the same other two tables).

    5. Commit -> ERRROR


    The create in step 4 is being committed to the db before the delete in step
    3, failing the key constraint.


    Use case
    --------
    You may wonder why on earth this is happening. Well, in a rich client
    application a user might tick a checkbox to link two records and then untick
    it and tick it again while they make up their mind. All those changes result
    in changes to the context.


    Solution
    --------

    A. Dedupe the overlapping create/delete records in the context before
    committing.
    B. Order the SQL better.



    Any thoughts about how Cayenne could deal with this better? What do other
    ORMs do in this scenario?



    Ari


    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A
  • Bryan Lewis at Feb 17, 2011 at 2:55 pm
    Agreed. We've started following that practice too. Always assign a simple
    unrelated primary key. Consistent, avoids surprises.

    On Thu, Feb 17, 2011 at 9:09 AM, Mike Kienenberger wrote:

    Solution C: assign a meaningless primary key to the join table.

    Obviously, this isn't an option for everyone.
    On Thu, Feb 17, 2011 at 3:11 AM, Aristedes Maniatis wrote:
    On 15/02/11 2:04 PM, Marcin Skladaniec wrote:

    java.sql.SQLIntegrityConstraintViolationException: The statement was
    aborted because it would have caused a duplicate key value in a unique
    or
    primary key constraint or unique index identified by
    'TAGRELATION_UNIQUE'
    defined on 'TAGRELATION'.

    Can I summarise, since I think you've highlighted a problem which is
    completely different to your email subject.

    1. Table A has primary key which is user defined (in this case, it is the
    join between two other tables, so the PK is the compound of the two foreign
    keys)

    2. Create record in table A. Commit.

    3. Delete that record in table A.

    4. Create another record in table A with the same PK as the one just deleted
    (since it joins the same other two tables).

    5. Commit -> ERRROR


    The create in step 4 is being committed to the db before the delete in step
    3, failing the key constraint.


    Use case
    --------
    You may wonder why on earth this is happening. Well, in a rich client
    application a user might tick a checkbox to link two records and then untick
    it and tick it again while they make up their mind. All those changes result
    in changes to the context.


    Solution
    --------

    A. Dedupe the overlapping create/delete records in the context before
    committing.
    B. Order the SQL better.



    Any thoughts about how Cayenne could deal with this better? What do other
    ORMs do in this scenario?



    Ari


    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedFeb 15, '11 at 3:05a
activeFeb 18, '11 at 7:41a
posts11
users7
websitecayenne.apache.org

People

Translate

site design / logo © 2022 Grokbase