Opened 3 months ago

Closed 3 months ago

Last modified 3 months ago

#35655 closed Bug (fixed)

Oracle > 23.3 does not support bulk insert with multiple rows

Reported by: Suraj Shaw Owned by: Sarah Boyce
Component: Database layer (models, ORM) Version: 5.1
Severity: Release blocker Keywords:
Cc: Suraj Shaw, Csirmaz Bendegúz, Mariusz Felisiak Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Suraj Shaw)

The recent optimisation in Django for multi-row insert in case of Oracle db version greater than 23+ is crashing with specific db versions. The issue is being reported https://github.com/oracle/python-oracledb/issues/356. The upcoming version 5.1 release have this changes and may cause crashes with database versions greater than 23.3.
Link to the PR which added this optimisation - https://github.com/django/django/pull/17969/

Change History (10)

comment:1 by Suraj Shaw, 3 months ago

Summary: Django crashes with oracle_db_version > 23.3Django crashes with Oracle database Version > 23.3

comment:2 by Suraj Shaw, 3 months ago

The issue is reproducible.

Probable Fixes:

1 -> Switch to earlier version for multi-row insert (using select-union combination) till the issue is being resolved.
2-> Migrate to ExecuteMany() instead of Execute() for multi-row insert. When the number of bind variables are higher ExecuteMany() will be performing much better. So yet another reason for migration.
3-> Migrate from NVARCHAR2 to VARCHAR2 as it also resolves the issue with multi-row insert.

Last edited 3 months ago by Suraj Shaw (previous) (diff)

comment:3 by Suraj Shaw, 3 months ago

Easy pickings: unset

comment:4 by Suraj Shaw, 3 months ago

Description: modified (diff)

comment:5 by Sarah Boyce, 3 months ago

Cc: Csirmaz Bendegúz Mariusz Felisiak added

comment:6 by Mariusz Felisiak, 3 months ago

As for me, it's an issue in Oracle not in Django itself. (2) and (3) are not doable (at least on this stage of our release cycle, (3) probably never, TBH). I'm not sure it's worth keeping 175b04942afaff978013db61495f3b39ea12989b for 23.3 > Oracle >= 23.0. Let's revert it, we can also close #35295 as "wontfix" (waiting for Oracle to make it work as promised).

comment:7 by Sarah Boyce, 3 months ago

Thank you, I will do the revert and backport shortly

comment:8 by Sarah Boyce, 3 months ago

Has patch: set
Owner: changed from Suraj Shaw to Sarah Boyce
Summary: Django crashes with Oracle database Version > 23.3Oracle > 23.3 does not support bulk insert with multiple rows
Triage Stage: UnreviewedAccepted

comment:9 by Sarah Boyce <42296566+sarahboyce@…>, 3 months ago

Resolution: fixed
Status: assignedclosed

In 5424151:

Fixed #35655 -- Reverted "Fixed #35295 -- Used INSERT with multiple rows on Oracle 23c."

This reverts commit 175b04942afaff978013db61495f3b39ea12989b due to a crash when Oracle > 23.3.

comment:10 by Sarah Boyce <42296566+sarahboyce@…>, 3 months ago

In 03051db:

[5.1.x] Fixed #35655 -- Reverted "Fixed #35295 -- Used INSERT with multiple rows on Oracle 23c."

This reverts commit 175b04942afaff978013db61495f3b39ea12989b due to a crash when Oracle > 23.3.

Backport of 5424151f96252e1289e9a6f7eb842cd1dc87850a from main.

Note: See TracTickets for help on using tickets.
Back to Top