search

OpenFn / wcs-wildmeat

Wildlife Conservation Society ConSocSci Project
1 stars 0 forks source link

"ON CONFLICT DO UPDATE command cannot affect row a second time" ERROR on Upsert Rural Consumption #37

Closed daissatou2 closed 2 years ago

daissatou2 commented 2 years ago

Describe the bug

Why are we trying to upsert the same tbl_wildmeat row two times? We should have a unique id for each row, but this error is telling us that we're updating the same row 2x.

To Reproduce

  1. Here is a link to a failed run on OpenFn.org wihch is indicative of the bug: https://www.openfn.org/projects/wildmeat/runs/061a4b3f-07e4-75c0-a7b2-3eb1d3544adf

expression.js

Link to the job itself in Github: https://github.com/OpenFn/wcs-wildmeat/blob/master/jobs/2.b.consommationRurale.js

state.json

https://www.openfn.org/projects/wildmeat/messages/061a4b3f-0651-71c3-94d7-925c211c54b3

To test/resolve

  1. After the desired output is working locally (from the CLI), please [push commits to master || open a pull request].
  2. [Please test the change on OpenFn.org by re-running this run (link) and confirming success.]
lakhassane commented 2 years ago

@daissatou2 currently the mapping does not build unique_id but just insert the rows from the state as is. Can you add to the spec if:

  1. you need a unique_id column to be added (and add that column to the DB)? Or,
  2. build a unique array of rows before inserting? Or
  3. add the index of each row to the unique columns to make them as unique ids.
daissatou2 commented 2 years ago

@lakhassane it looks like this error is happening because there are two items in the message with the same species. To mitigate this, can you please update the job to store the following in wildmeat_id on tbl_wildmeat:

foodItem['group_begin/group_food/species'] + [the position of the item in the array]. This will make sure wildmeat_id will always be unqiue.

We have already implemented something similar here: https://github.com/OpenFn/wcs-wildmeat/blob/master/jobs/2.d.Offtake.js#L80

lakhassane commented 2 years ago

@daissatou2 I added the position. Find run here: https://www.openfn.org/projects/wildmeat/runs/061adf9f-0f17-7122-a7d2-d18617b6b1f7