Merge option not merging in ETL

LabKey Support Forum (Inactive)
Merge option not merging in ETL james welch  2015-02-26 09:18
Status: Closed
 
Hi - I'm new to LabKey - it's a great tool, but I've come across a problem I hope someone can help me with.

I'm trying to create an example ETL that sucks data out of an external PostgresQL table and copies it into a list.
My ETL xml file is defined as follows.

<code>
<?xml version="1.0" encoding="UTF-8"?>
<etl xmlns="http://labkey.org/etl/xml">
    <name>Example: Cities to Labkey List</name>
    <transforms>
        <transform id="transfer1">
            <description>description</description>
            <source schemaName="cities" queryName="city"/>
            <destination schemaName="lists" queryName="MyList" targetOption="merge"/>
        </transform>
    </transforms>
    <incrementalFilter className="ModifiedSinceFilterStrategy" timestampColumnName="updated" />
    <schedule><poll interval="2m" /></schedule>
</etl>
</code>

This works for inserting newly added data. But when I modify a pre-existing row, the ETL fails with the following error:

ERROR: duplicate key value violates unique constraint "c2d22_mylist_pk"
...
Detail: Key (id)=(30) already exists.
Where: SQL statement "INSERT INTO list.c2d22_mylist (...)

It looks as though the filter is trying to INSERT on a duplicate key, rather than UPDATE as intended.
Am I doing something wrong, or have I misunderstood the documentation?

Many thanks.
 
 
Jon (LabKey DevOps) responded:  2015-02-26 12:28
Hi James,

This is actually a know bug that is being actively worked on.

According to the bug ticket that is open (https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=19838), it looks like we're targeting our 15.2 release for a fix, which is in mid-July.

At this time, we unfortunately do not have a workaround listed for this. We apologize for any inconvenience this may cause.

Please let us know if you have any further questions.

Regards,

Jon
 
Jon (LabKey DevOps) responded:  2015-02-26 12:55
James,

Discussing this with my developers, upon further review, there is a possibility that you might be able to work around this by just going directly into a dataset or custom schema rather than using the list mechanism, but beyond that, there isn't anything else that would allow this feature to work with the list.

Regards,

Jon
 
james welch responded:  2015-02-26 15:18
Jon,
Thanks very much for your quick response.
I'm sorry - I had a quick look through the issues, but did not see that it had already been reported.
I'll have a go at importing into a custom schema and see if that works.

Many thanks,

James