Duplicate key value violates unique constraint "sys_file_pkey"

Support
#1

Hello,

I have an entity that contains a list of documents:

@Table(name = "AGOV_AUDIT")
@Entity(name = "agov_Audit")
public class Audit extends StandardEntity {
    ...
    @JoinTable(name = "AGOV_AUDIT_FILE_DESCRIPTOR_LINK",
            joinColumns = @JoinColumn(name = "AUDIT_ID"),
            inverseJoinColumns = @JoinColumn(name = "FILE_DESCRIPTOR_ID"))
    @OnDelete(DeletePolicy.CASCADE)
    @ManyToMany(cascade = CascadeType.PERSIST)
    protected List<FileDescriptor> documents;
    ...
}

The edit screen looks as follows:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<window xmlns="http://schemas.haulmont.com/cuba/screen/window.xsd"
        xmlns:app="http://schemas.agentes.de/agov/ui-component.xsd"
        caption="msg://editorCaption"
        messagesPack="de.agentes.agov.web.screens.audit">
    <data>
        <instance id="auditDc"
                  class="de.agentes.agov.aud.Audit"
                  view="audit-edit">
            <loader/>
            <collection id="documentsDc" property="documents"/>
            <collection id="previousAuditsDc" property="previousAudits"/>
        </instance>
        ...
    </data>
    ...
</window>

The upload button and the table with the documents are in one fragment:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<fragment xmlns="http://schemas.haulmont.com/cuba/screen/fragment.xsd">
    <data>
        <collection id="documentsDc" class="com.haulmont.cuba.core.entity.FileDescriptor" provided="true"/>
    </data>
    <layout expand="documentDropZone">
        <vbox id="documentDropZone" expand="documentsTable">
            <table textSelectionEnabled="true" id="documentsTable" width="100%" dataContainer="documentsDc">
                <actions>
                    <action id="show" caption="mainMsg://actions.View" icon="FOLDER_OPEN_O" trackSelection="true"/>
                    <action id="remove" type="remove"/>
                </actions>
                <columns>
                    <column id="name"/>
                    <column id="extension"/>
                    <column id="createDate"/>
                </columns>
                <rowsCount/>
                <buttonsPanel id="buttonsPanel" alwaysVisible="true">
                    <upload id="documentUploadBtn" dropZone="documentDropZone"
                            uploadButtonCaption="mainMsg://uploadBtn.caption"
                            uploadButtonDescription="mainMsg://uploadBtn.description"
                            uploadButtonIcon="icons/upload.png"/>
                    <button id="showBtn" action="documentsTable.show"/>
                    <button id="removeBtn" action="documentsTable.remove"/>
                </buttonsPanel>
            </table>
        </vbox>
    </layout>
</fragment>

The method for the Upload button is implemented as follows

    @Subscribe("documentUploadBtn")
    private void onDocumentUploadBtnFileUploadSucceed(FileUploadField.FileUploadSucceedEvent event) {
        UUID fileId = documentUploadBtn.getFileId();
        FileDescriptor fc = documentUploadBtn.getFileDescriptor();

        if (fc != null) {
            uploadedFiles.put(fc, documentUploadBtn.getFileContent());

            try {
                fileUploadingAPI.putFileIntoStorage(fileId, fc);
                fc = dataManager.commit(fc);
                documentsDc.getMutableItems().add(fc);

            } catch (FileStorageException e) {
                log.error("Error while saving document!", e);
                dialogs.createExceptionDialog()
                        .withCaption(messages.getMainMessage("global.dialogs.error"))
                        .withMessage(messages.getMainMessage("facility.fileupload.error"))
                        .withThrowable(e);
            }
        }
    }

If I now add documents to an existing audit entity this works fine. But when I create a new audit entity, add documents and then save it, the following error message appears:

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.6-cuba): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "sys_file_pkey"
  Detail: Key (id)=(af20e5ba-b540-b81a-6bb5-eceb96c3bb31) already exists.
Error Code: 0
Call: INSERT INTO SYS_FILE (ID, CREATE_DATE, CREATE_TS, CREATED_BY, DELETE_TS, DELETED_BY, EXT, NAME, FILE_SIZE, UPDATE_TS, UPDATED_BY, VERSION) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	bind => [af20e5ba-b540-b81a-6bb5-eceb96c3bb31, 2020-01-23 07:03:44.917, 2020-01-23 07:03:53.899, admin, null, null, png, 2019-10-22 11_33_32-Lifesize.png, 219653, 2020-01-23 07:03:53.899, null, 1]
Query: InsertObjectQuery(com.haulmont.cuba.core.entity.FileDescriptor-af20e5ba-b540-b81a-6bb5-eceb96c3bb31 [detached])

Actually, he doesn’t need to make an insert on the table SYS_FILE anymore, because the FileDescriptor has already been saved in the method onDocumentUploadBtnFileUploadSucceed.

Does anyone have a solution for this?

Greetings
Andreas

#2

Hello @Andreas.Brueck

It will save us a lot of time if you’ll share some sample project to investigate the problem. Is it possible?

Regards,
Daniil

#3

Hello @tsarev,

no problem. I create a sample app.

Greetings
Andreas

#4

Great, will be waiting

#5

Hello @tsarev,

while creating the example app, i found the cause of the problem:

The exception occurs when the cascade option is set in the @ManyToMany annotation:

    ...
    @JoinTable(name = "AGOV_AUDIT_FILE_DESCRIPTOR_LINK",
            joinColumns = @JoinColumn(name = "AUDIT_ID"),
            inverseJoinColumns = @JoinColumn(name = "FILE_DESCRIPTOR_ID"))
    @OnDelete(DeletePolicy.CASCADE)
    @ManyToMany(cascade = CascadeType.PERSIST)
    protected List<FileDescriptor> documents;
    ...

If you remove this, the exception will no longer occur:

    ...
    @JoinTable(name = "AGOV_AUDIT_FILE_DESCRIPTOR_LINK",
            joinColumns = @JoinColumn(name = "AUDIT_ID"),
            inverseJoinColumns = @JoinColumn(name = "FILE_DESCRIPTOR_ID"))
    @OnDelete(DeletePolicy.CASCADE)
    @ManyToMany
    protected List<FileDescriptor> documents;
    ...

The documentation also advises against using the cascade option.:

The usage of JPA cascade annotation attribute is not recommended. The entities persisted and merged implicitly using such declaration will bypass some system mechanisms. In particular, the EntityStates bean does not detect the managed state correctly and entity listeners are not invoked at all.

Greetings
Andreas

3 Likes