Is it possible to foreign key on bulk_create() Django?

Question

I have the need to create many EVENT objects at once and subsequently create many ARCHIVED_EVENT objects that have a foreign key to their corresponding event.

My code looks like something like this:

events = []
archivedEvents = []
for _ in range(1000):
    event = Event(name="Test")
    archivedEvent = ArchivedEvent(event_id=event.id)
    archivedEvents.append(archivedEvent)
    events.append(event)

Event.objects.bulk_create(events)
ArchivedEvent.objects.bulk_create(archivedEvents)

Unfortunately all Archived Events created here have a NULL foreign key to an EVENT. I understand that the primary key for an object is not generated until it is saved to the database. But I save the events before creating the archived events. Am I missing something? Should I refresh the cache before bulk creating the archived events?

ruddra · Answer 1 · 2020-06-15T07:37:23.870

0

event.id won't be created unless it does through bulk_create(). Probably you can use another loop to create ArchivedEvent. Like this:

events = []
archivedEvents = []

for _ in range(1000):
    event = Event(name="Test")
    events.append(event)
events = Event.objects.bulk_create(events)

for event in events:
    archivedEvent = ArchivedEvent(event_id=event.id)
    archivedEvents.append(archivedEvent)
ArchivedEvent.objects.bulk_create(archivedEvents)

This solution works for Postgresql as per documentation. If you have other databases, then try like this:

events = []
archivedEvents = []

for _ in range(1000):
    event = Event(name="Test")
    events.append(event)
Event.objects.bulk_create(events)

events = Event.objects.all().order_by('-id')[:1000][::-1]
for event in events:
    archivedEvent = ArchivedEvent(event_id=event.id)
    archivedEvents.append(archivedEvent)
ArchivedEvent.objects.bulk_create(archivedEvents)

To avoid race conditions, you can pre-generate primary keys, then use them. For example:

last_event_id = Event.objects.last().id + 1000 # getting last event id and adding thousand to avoid duplicates.

events = [x+last_event_id for x in range(1000)]
archivedEvents = []

for e in events:
    event = Event(name="Test",pk=e)
    events.append(event)
    archivedEvent = ArchivedEvent(event_id=e)
    archivedEvents.append(archivedEvent)

Event.objects.bulk_create(events)
ArchivedEvent.objects.bulk_create(archivedEvents)

edited Jun 15 '20 at 07:37

answered Jun 15 '20 at 07:14

ruddra

50,746
7
78
101

because event.id has a value now. – ruddra Jun 15 '20 at 07:16
1

This did not work, the archived events still have NULL as their event_id. – Ludovico Verniani Jun 15 '20 at 07:18
what database are you using? as per documentation, it should work in postgresql. – ruddra Jun 15 '20 at 07:19
I am using MySQL – Ludovico Verniani Jun 15 '20 at 07:19
then you need to make a query to get last thousand items and iterate through them. I have updated the answer. – ruddra Jun 15 '20 at 07:24
1

Have you considered the race conditions with this approach? If Event objects were created between the bulk_create() and the query, everything breaks horrendously. Is the best option really to save() individually? – Ludovico Verniani Jun 15 '20 at 07:29
Please see the updated answer. again this is not tested in MySQL – ruddra Jun 15 '20 at 07:37
1

It appears the race condition continues. If the view that runs this code is called twice at the same time, all hell breaks loose. – Rage Jun 15 '20 at 07:44
1

then this beats the purpose of using bulk create here. this code is not suppose to be used with view rather in a management command or shell. So, As Ludovico suggested, you should use individual save(). – ruddra Jun 15 '20 at 08:07
Ok, back to the stoneage-saves it is. Thank you for your time. – Rage Jun 15 '20 at 08:27
In this case, you need put this inserting logic in a transaction and with a lock to avoid race condition. See my answer. – Fogmoon Jun 15 '20 at 08:31

score 0 · Answer 2 · answered Jun 15 '20 at 08:31

0

Here is a solution to avoid race condition with db's lock based on @ruddra's other database solution. Hope this can help you.

from django.db import transaction

with transaction.atomic():

   # lock first row to avoid race condition
   # note: first row of Event table must have content,
   #       if not, you need use other tables's not empty row to add lock.
   Event.objects.select_for_update().first()

   # just @ruddra's mysql solution
   events = []
   archivedEvents = []

   for _ in range(1000):
       event = Event(name="Test")
       events.append(event)
   Event.objects.bulk_create(events)

   event_ids = Event.objects.values_list('id', flat=True).order_by('-id')[:1000][::-1]
   for event_id in event_ids:
       archivedEvent = ArchivedEvent(event_id=event_id)
       archivedEvents.append(archivedEvent)
   ArchivedEvent.objects.bulk_create(archivedEvents)

answered Jun 15 '20 at 08:31

Fogmoon

569
5
16

What exactly is happening here? Why are we locking the first row? – Rage Jun 15 '20 at 08:42
It's just a lock provided by db to avoid race condition, you can use any other table row. Usually, I use first row as the lock. See [select-for-update](https://docs.djangoproject.com/en/3.0/ref/models/querysets/#select-for-update) – Fogmoon Jun 15 '20 at 08:48
Hmm, perhaps this lock technique would take more time than single saves() as this view could now run simultaneously but not really as some threads would need to wait. – Rage Jun 15 '20 at 09:13
I don't think so. Eventually all data need to wait lock at db level when insert. See [Which is faster: multiple single INSERTs or one multiple-row INSERT?](https://stackoverflow.com/questions/1793169/which-is-faster-multiple-single-inserts-or-one-multiple-row-insert), although the view now run simultaneously, it can handle the request more fast. Anyway, for the time cost, you'd better have a test. – Fogmoon Jun 15 '20 at 11:59

Is it possible to foreign key on bulk_create() Django?

2 Answers2