Is it safe to run multiple calls to get_or_create in parallel?

[waldner]

Basically, I have a call where I'm getting tens or possibly hundreds of items that I need to sync into the db (that is, if an item exists, get and update it, otherwise create it). The table has a primary key which is a serial column, but since incoming items don't have that id, there is also a unique composite index that is used to check whether an item exists. So to avoid long processing times, I was planning to do something like:

# main code
tasks = []
for item_dict in items:
    tasks.append(asyncio.create_task(upsert_item(item_dict)))
await asyncio.gather(*tasks)

async def upsert_item(item_dict):

    item = await Items.objects().get_or_create(
        (Items.type == item_dict["type"]) & (Items.unique_id == item_dict["unique_id"]),
        defaults=item_dict,
    )

    if not item._was_created:
        # Update existing item
        item.description = item_dict["description"]
        item.start = item_dict["start"]
        await item.save()

Is this safe to do? Can there be issues with the serial id for the table? Or for example if duplicated items exist in the input? In the latter case, my expectation would be that if the item did not exist in the db, one would be inserted and the other would be an update of the previously inserted one (I don't mind which one is what, as long as the result is consistent), or if it was already present, then both would be updates (again, order doesn't matter, as long as the result is consistent).

I'm using postgresql as the backing DB.

[dantownsend]

For something like this, you can use you can try an INSERT with ON CONFLICT:

https://piccolo-orm.readthedocs.io/en/latest/piccolo/query_clauses/on_conflict.html#do-update

>>> await Items.insert(
...     Items(**item_dict)
... ).on_conflict(
...     action="DO UPDATE",
...     target=[Items.unique_id]
... )

So if an item already exists with that unique_id it gets updated, otherwise it gets created.

For it to work, the unique_id column needs a unique constraint on it in the database.

In the future we might support the Postgres MERGE command, which is similar but also does deletes.

[waldner]

What about the case where I can guarantee that no duplicates are present in the input items (this can easily be achieved by preprocessing the list)? Would my proposed code be safe then?

[dantownsend]

I think your code is fine. As you say, if you remove any duplicates then this removes the risks from running a bunch of get_or_create concurrently.

[waldner]

Well it would still run a bunch of them concurrently (that's the whole point of the exercise after all), just never two or more for the same item. Thanks.

[waldner]

For the record, when syncing about 400 items this cut down the time from ~3.5 minutes when done serially, to about 10 seconds.

[dantownsend]

Nice!