Re: DERBY-6303: Add ability to defer enforcement of unique indexes until transaction end
On 2/1/24 00:51, Rick Hillegas wrote: When I run fxmovierentals6-all.sql, I get a violation of a different foreign key than the one mentioned in the comment at the end of the script. I get this error: ERROR 23516: The transaction was aborted because of a deferred constraint violation: Foreign key 'FK9_talent_talent_id_TalentPhoto_talent_id' defined on "APP"."TALENT" referencing constraint 'SQL000362-4dc6d895-018d-4f9a-090e-170eba31' defined on "APP"."TALENTPHOTO", key '(0,(2,90))'. You are missing 3 matching keys in the referenced table. The following query (added just before the failed COMMIT) returns the following result: ij> SELECT talent_id FROM talent WHERE talent_id NOT IN (SELECT talent_id FROM talentphoto); TALENT_ID --- 0 1 2 Thanks for this query that really helped me! The data had indeed quite some issues. After fixing them, and using normal PK's everything works great. No need for special tricks during data import now. Thanks again for your help. Best regards, Bart P.D. 1: I am not sure how to test JIRA issue DERBY-6303 since the PK's are also unique. P.D. 2: Could this database be useful to include in the Derby distribution as example? It appears that you need to fix your data. Hope this helps, -Rick On 1/31/24 1:06 PM, Bart van der Bilt wrote: On 1/31/24 00:51, Rick Hillegas wrote: On 1/30/24 12:52 PM, jazz wrote: Hi, It would be great if this issue could be implemented, I have a use case for testing. How do I get access to JIRA to update the ticket? (to include the sql files for testing). You can request a JIRA account here: https://selfserve.apache.org/jira-account.html Thanks, much appreciated. I added two SQL files in the JIRA ticket for testing. When importing data into the database (which has foreign keys and unique constraints) this is useful. Import fails due to a deferred constraint violation due to the unique index (SQL000140-0fe82566-018d-4f9a-090e-170eba31): ERROR 23516: The transaction was aborted because of a deferred constraint violation: Foreign key 'FOREIGN_KEY_director_talent_id_talent_talent_id' defined on "APP"."DIRECTOR" referencing constraint 'SQL000140-0fe82566-018d-4f9a-090e-170eba31' defined on "APP"."TALENT", key ''. Import flow is (in ij): autocommit off; run('movies_insert.sql'); commit; Foreign keys are created with deferred constraints (deferrable initially deferred). Any help for a workaround or fix is highly appreciated. The only workaround which occurs to me is to replace your unique indexes with unique constraints. Thanks for the advice. This is what I did. Still no luck. Maybe I am doing something wrong here. I guess the question is how to insert data when foreign keys are already created. Tried to workaround this issue by creating the unique constraints (they should be primary keys). Does it have to do something with the created backing indexes which are not deferrable? Best regards, Bart -Rick Best regards, Bart
[jira] [Commented] (DERBY-6303) Add ability to defer enforcement of unique indexes until transaction end.
[ https://issues.apache.org/jira/browse/DERBY-6303?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17813401#comment-17813401 ] Bart van der Bilt commented on DERBY-6303: -- After fixing the data this works great :). Create schema, add foreign keys and import data last. Re-attached the corrected SQL files. Not sure how to reproduce this. > Add ability to defer enforcement of unique indexes until transaction end. > - > > Key: DERBY-6303 > URL: https://issues.apache.org/jira/browse/DERBY-6303 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.11.1.1 >Reporter: Richard N. Hillegas >Priority: Major > Attachments: movierentals26.sql, movies_insert_fk7.sql > > > Applications may need to defer the enforcement of unique indexes for the same > reasons that they need to defer the enforcement of unique constraints. See > DERBY-532. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (DERBY-6303) Add ability to defer enforcement of unique indexes until transaction end.
[ https://issues.apache.org/jira/browse/DERBY-6303?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Bart van der Bilt updated DERBY-6303: - Attachment: (was: fxmovierentals6-all.sql) > Add ability to defer enforcement of unique indexes until transaction end. > - > > Key: DERBY-6303 > URL: https://issues.apache.org/jira/browse/DERBY-6303 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.11.1.1 >Reporter: Richard N. Hillegas >Priority: Major > Attachments: movierentals26.sql, movies_insert_fk7.sql > > > Applications may need to defer the enforcement of unique indexes for the same > reasons that they need to defer the enforcement of unique constraints. See > DERBY-532. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (DERBY-6303) Add ability to defer enforcement of unique indexes until transaction end.
[ https://issues.apache.org/jira/browse/DERBY-6303?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Bart van der Bilt updated DERBY-6303: - Attachment: movies_insert_fk7.sql > Add ability to defer enforcement of unique indexes until transaction end. > - > > Key: DERBY-6303 > URL: https://issues.apache.org/jira/browse/DERBY-6303 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.11.1.1 >Reporter: Richard N. Hillegas >Priority: Major > Attachments: movierentals26.sql, movies_insert_fk7.sql > > > Applications may need to defer the enforcement of unique indexes for the same > reasons that they need to defer the enforcement of unique constraints. See > DERBY-532. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (DERBY-6303) Add ability to defer enforcement of unique indexes until transaction end.
[ https://issues.apache.org/jira/browse/DERBY-6303?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Bart van der Bilt updated DERBY-6303: - Attachment: movierentals26.sql > Add ability to defer enforcement of unique indexes until transaction end. > - > > Key: DERBY-6303 > URL: https://issues.apache.org/jira/browse/DERBY-6303 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.11.1.1 >Reporter: Richard N. Hillegas >Priority: Major > Attachments: movierentals26.sql, movies_insert_fk7.sql > > > Applications may need to defer the enforcement of unique indexes for the same > reasons that they need to defer the enforcement of unique constraints. See > DERBY-532. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (DERBY-6303) Add ability to defer enforcement of unique indexes until transaction end.
[ https://issues.apache.org/jira/browse/DERBY-6303?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Bart van der Bilt updated DERBY-6303: - Attachment: (was: movies_insert_fk7.sql) > Add ability to defer enforcement of unique indexes until transaction end. > - > > Key: DERBY-6303 > URL: https://issues.apache.org/jira/browse/DERBY-6303 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.11.1.1 >Reporter: Richard N. Hillegas >Priority: Major > Attachments: movierentals26.sql, movies_insert_fk7.sql > > > Applications may need to defer the enforcement of unique indexes for the same > reasons that they need to defer the enforcement of unique constraints. See > DERBY-532. -- This message was sent by Atlassian Jira (v8.20.10#820010)
Re: DERBY-6303: Add ability to defer enforcement of unique indexes until transaction end
When I run fxmovierentals6-all.sql, I get a violation of a different foreign key than the one mentioned in the comment at the end of the script. I get this error: ERROR 23516: The transaction was aborted because of a deferred constraint violation: Foreign key 'FK9_talent_talent_id_TalentPhoto_talent_id' defined on "APP"."TALENT" referencing constraint 'SQL000362-4dc6d895-018d-4f9a-090e-170eba31' defined on "APP"."TALENTPHOTO", key '(0,(2,90))'. You are missing 3 matching keys in the referenced table. The following query (added just before the failed COMMIT) returns the following result: ij> SELECT talent_id FROM talent WHERE talent_id NOT IN (SELECT talent_id FROM talentphoto); TALENT_ID --- 0 1 2 It appears that you need to fix your data. Hope this helps, -Rick On 1/31/24 1:06 PM, Bart van der Bilt wrote: On 1/31/24 00:51, Rick Hillegas wrote: On 1/30/24 12:52 PM, jazz wrote: Hi, It would be great if this issue could be implemented, I have a use case for testing. How do I get access to JIRA to update the ticket? (to include the sql files for testing). You can request a JIRA account here: https://selfserve.apache.org/jira-account.html Thanks, much appreciated. I added two SQL files in the JIRA ticket for testing. When importing data into the database (which has foreign keys and unique constraints) this is useful. Import fails due to a deferred constraint violation due to the unique index (SQL000140-0fe82566-018d-4f9a-090e-170eba31): ERROR 23516: The transaction was aborted because of a deferred constraint violation: Foreign key 'FOREIGN_KEY_director_talent_id_talent_talent_id' defined on "APP"."DIRECTOR" referencing constraint 'SQL000140-0fe82566-018d-4f9a-090e-170eba31' defined on "APP"."TALENT", key ''. Import flow is (in ij): autocommit off; run('movies_insert.sql'); commit; Foreign keys are created with deferred constraints (deferrable initially deferred). Any help for a workaround or fix is highly appreciated. The only workaround which occurs to me is to replace your unique indexes with unique constraints. Thanks for the advice. This is what I did. Still no luck. Maybe I am doing something wrong here. I guess the question is how to insert data when foreign keys are already created. Tried to workaround this issue by creating the unique constraints (they should be primary keys). Does it have to do something with the created backing indexes which are not deferrable? Best regards, Bart -Rick Best regards, Bart
Re: DERBY-6303: Add ability to defer enforcement of unique indexes until transaction end
On 1/31/24 00:51, Rick Hillegas wrote: On 1/30/24 12:52 PM, jazz wrote: Hi, It would be great if this issue could be implemented, I have a use case for testing. How do I get access to JIRA to update the ticket? (to include the sql files for testing). You can request a JIRA account here: https://selfserve.apache.org/jira-account.html Thanks, much appreciated. I added two SQL files in the JIRA ticket for testing. When importing data into the database (which has foreign keys and unique constraints) this is useful. Import fails due to a deferred constraint violation due to the unique index (SQL000140-0fe82566-018d-4f9a-090e-170eba31): ERROR 23516: The transaction was aborted because of a deferred constraint violation: Foreign key 'FOREIGN_KEY_director_talent_id_talent_talent_id' defined on "APP"."DIRECTOR" referencing constraint 'SQL000140-0fe82566-018d-4f9a-090e-170eba31' defined on "APP"."TALENT", key ''. Import flow is (in ij): autocommit off; run('movies_insert.sql'); commit; Foreign keys are created with deferred constraints (deferrable initially deferred). Any help for a workaround or fix is highly appreciated. The only workaround which occurs to me is to replace your unique indexes with unique constraints. Thanks for the advice. This is what I did. Still no luck. Maybe I am doing something wrong here. I guess the question is how to insert data when foreign keys are already created. Tried to workaround this issue by creating the unique constraints (they should be primary keys). Does it have to do something with the created backing indexes which are not deferrable? Best regards, Bart -Rick Best regards, Bart
[jira] [Commented] (DERBY-6303) Add ability to defer enforcement of unique indexes until transaction end.
[ https://issues.apache.org/jira/browse/DERBY-6303?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17812859#comment-17812859 ] Bart van der Bilt commented on DERBY-6303: -- I added 2 sql files as test case. Also when converting primary keys to unique constrains I cannot get this migration to work from IJ. run 'fxmovierentals6-all.sql'. Works fine without the foreign keys. > Add ability to defer enforcement of unique indexes until transaction end. > - > > Key: DERBY-6303 > URL: https://issues.apache.org/jira/browse/DERBY-6303 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.11.1.1 >Reporter: Richard N. Hillegas >Priority: Major > Attachments: fxmovierentals6-all.sql, movies_insert_fk7.sql > > > Applications may need to defer the enforcement of unique indexes for the same > reasons that they need to defer the enforcement of unique constraints. See > DERBY-532. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (DERBY-6303) Add ability to defer enforcement of unique indexes until transaction end.
[ https://issues.apache.org/jira/browse/DERBY-6303?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Bart van der Bilt updated DERBY-6303: - Attachment: movies_insert_fk7.sql > Add ability to defer enforcement of unique indexes until transaction end. > - > > Key: DERBY-6303 > URL: https://issues.apache.org/jira/browse/DERBY-6303 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.11.1.1 >Reporter: Richard N. Hillegas >Priority: Major > Attachments: fxmovierentals6-all.sql, movies_insert_fk7.sql > > > Applications may need to defer the enforcement of unique indexes for the same > reasons that they need to defer the enforcement of unique constraints. See > DERBY-532. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (DERBY-6303) Add ability to defer enforcement of unique indexes until transaction end.
[ https://issues.apache.org/jira/browse/DERBY-6303?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Bart van der Bilt updated DERBY-6303: - Attachment: fxmovierentals6-all.sql > Add ability to defer enforcement of unique indexes until transaction end. > - > > Key: DERBY-6303 > URL: https://issues.apache.org/jira/browse/DERBY-6303 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.11.1.1 >Reporter: Richard N. Hillegas >Priority: Major > Attachments: fxmovierentals6-all.sql, movies_insert_fk7.sql > > > Applications may need to defer the enforcement of unique indexes for the same > reasons that they need to defer the enforcement of unique constraints. See > DERBY-532. -- This message was sent by Atlassian Jira (v8.20.10#820010)
Re: DERBY-6303: Add ability to defer enforcement of unique indexes until transaction end
On 1/30/24 12:52 PM, jazz wrote: Hi, It would be great if this issue could be implemented, I have a use case for testing. How do I get access to JIRA to update the ticket? (to include the sql files for testing). You can request a JIRA account here: https://selfserve.apache.org/jira-account.html When importing data into the database (which has foreign keys and unique constraints) this is useful. Import fails due to a deferred constraint violation due to the unique index (SQL000140-0fe82566-018d-4f9a-090e-170eba31): ERROR 23516: The transaction was aborted because of a deferred constraint violation: Foreign key 'FOREIGN_KEY_director_talent_id_talent_talent_id' defined on "APP"."DIRECTOR" referencing constraint 'SQL000140-0fe82566-018d-4f9a-090e-170eba31' defined on "APP"."TALENT", key ''. Import flow is (in ij): autocommit off; run('movies_insert.sql'); commit; Foreign keys are created with deferred constraints (deferrable initially deferred). Any help for a workaround or fix is highly appreciated. The only workaround which occurs to me is to replace your unique indexes with unique constraints. -Rick Best regards, Bart
DERBY-6303: Add ability to defer enforcement of unique indexes until transaction end
Hi, It would be great if this issue could be implemented, I have a use case for testing. How do I get access to JIRA to update the ticket? (to include the sql files for testing). When importing data into the database (which has foreign keys and unique constraints) this is useful. Import fails due to a deferred constraint violation due to the unique index (SQL000140-0fe82566-018d-4f9a-090e-170eba31): ERROR 23516: The transaction was aborted because of a deferred constraint violation: Foreign key 'FOREIGN_KEY_director_talent_id_talent_talent_id' defined on "APP"."DIRECTOR" referencing constraint 'SQL000140-0fe82566-018d-4f9a-090e-170eba31' defined on "APP"."TALENT", key ''. Import flow is (in ij): autocommit off; run('movies_insert.sql'); commit; Foreign keys are created with deferred constraints (deferrable initially deferred). Any help for a workaround or fix is highly appreciated. Best regards, Bart
[jira] [Commented] (DERBY-6303) Add ability to defer enforcement of unique indexes until transaction end.
[ https://issues.apache.org/jira/browse/DERBY-6303?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13980125#comment-13980125 ] Dag H. Wanvik commented on DERBY-6303: -- That might have performance implications: deferrable indexes are physically non-unique, which gives a slightly slower code path than phsyically unique indexes. Next point: how would be make such indexes deferred (as opposed to deferrable)? For constraints, we use the SET CONSTRAINTS statement. > Add ability to defer enforcement of unique indexes until transaction end. > - > > Key: DERBY-6303 > URL: https://issues.apache.org/jira/browse/DERBY-6303 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.11.0.0 >Reporter: Rick Hillegas > > Applications may need to defer the enforcement of unique indexes for the same > reasons that they need to defer the enforcement of unique constraints. See > DERBY-532. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Commented] (DERBY-6303) Add ability to defer enforcement of unique indexes until transaction end.
[ https://issues.apache.org/jira/browse/DERBY-6303?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13965747#comment-13965747 ] Rick Hillegas commented on DERBY-6303: -- Hi Dag, I haven't worked out any syntax for this. Why not let all unique indexes be deferrable? Thanks. > Add ability to defer enforcement of unique indexes until transaction end. > - > > Key: DERBY-6303 > URL: https://issues.apache.org/jira/browse/DERBY-6303 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.11.0.0 >Reporter: Rick Hillegas > > Applications may need to defer the enforcement of unique indexes for the same > reasons that they need to defer the enforcement of unique constraints. See > DERBY-532. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Commented] (DERBY-6303) Add ability to defer enforcement of unique indexes until transaction end.
[ https://issues.apache.org/jira/browse/DERBY-6303?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13965733#comment-13965733 ] Dag H. Wanvik commented on DERBY-6303: -- Hi Rick, do you have any specific syntax in mind for this? Would such indexes be declared as deferrable? > Add ability to defer enforcement of unique indexes until transaction end. > - > > Key: DERBY-6303 > URL: https://issues.apache.org/jira/browse/DERBY-6303 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.11.0.0 >Reporter: Rick Hillegas > > Applications may need to defer the enforcement of unique indexes for the same > reasons that they need to defer the enforcement of unique constraints. See > DERBY-532. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Created] (DERBY-6303) Add ability to defer enforcement of unique indexes until transaction end.
Rick Hillegas created DERBY-6303: Summary: Add ability to defer enforcement of unique indexes until transaction end. Key: DERBY-6303 URL: https://issues.apache.org/jira/browse/DERBY-6303 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.11.0.0 Reporter: Rick Hillegas Applications may need to defer the enforcement of unique indexes for the same reasons that they need to defer the enforcement of unique constraints. See DERBY-532. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira