Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#24200 closed Cleanup/optimization (fixed)

Make introspection bypass statement caching on Oracle

Reported by: Shai Berger Owned by: Josh Smeaton
Component: Database layer (models, ORM) Version: 1.8alpha1
Severity: Normal Keywords: oracle introspection
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

This comes from looking at the Oracle errors encountered in the initial fix for #24163.

The Django Oracle backend currently uses a statement cache. This means that if a statement is re-executed (soon enough after the previous execution), a compiled statement is fetched from the cache and performance is greatly improved.

However, this causes problems with introspection during migrations; a compiled select * from T statement embeds in it the structure of table T, and if a column is dropped, then a cached select * from T will fail on "ORA-01007: variable not in select list". This is actually what happens -- get_table_description on Oracle uses a select * from T query.

We have run into the problem in the past, but haven't understood it properly; so we introduced a workaround of disconnecting from the database after relevant schema changes, and a database feature connection_persists_old_columns to control its use. But in fact, simple cache-busting (e.g. rephrasing the query as select * from %(table)s where 0 > %(counter)d with counter advancing each time) should solve the problem and make the disconnection, as well as the database feature, redundant.

I think this fix should go also into 1.8, but definitely into master.

Change History (10)

comment:1 Changed 5 years ago by Josh Smeaton

I don't think we need to get too crazy with regards to cache busting. Simply setting the stmtcachesize to 0 and back to 20 clears the cache. We could also try to request a connection with caching disabled for migrations.

(Pdb) cursor = connection.cursor()
(Pdb) cursor.execute('create table test_cache (int1 int, int2 int)')
(Pdb) cursor.execute('select * from test_cache')
<cx_Oracle.Cursor on <cx_Oracle.Connection to test_djangotests@django>>
(Pdb) cursor.execute('select * from test_cache').fetchmany()
[]
(Pdb) cursor.execute('alter table test_cache drop column int2')
(Pdb) cursor.execute('select * from test_cache').fetchmany()
*** django.db.utils.DatabaseError: ORA-01007: variable not in select list
(Pdb) cursor.execute('select * from test_cache').fetchmany()
*** django.db.utils.DatabaseError: ORA-01007: variable not in select list
(Pdb) cursor.connection
<cx_Oracle.Connection to test_djangotests@django>
(Pdb) cursor.connection.stmtcachesize
20
(Pdb) cursor.connection.stmtcachesize = 0
(Pdb) cursor.execute('select * from test_cache').fetchmany()
[]
(Pdb) cursor.connection.stmtcachesize = 20
(Pdb) cursor.execute('select * from test_cache').fetchmany()
[]

comment:2 Changed 5 years ago by Simon Charette

I seems like alter system flush shared_pool; would also work.

comment:3 Changed 5 years ago by Claude Paroz

Triage Stage: UnreviewedAccepted

comment:4 Changed 5 years ago by Josh Smeaton

The flush shared_pool just flushes server side statements AFAIK, and doesn't work with the recommended privileges (you need system-like privs).

comment:5 Changed 5 years ago by Josh Smeaton

Is this something that we want to include for 1.8, or is this going to be targeting master? I'll put together a patch for it within the next few days if inclusion for 1.8 is the goal.

comment:6 Changed 5 years ago by Tim Graham

You have until the beta release to submit non-release blocker bug fixes of which this seems to fall into.

comment:8 Changed 5 years ago by Josh Smeaton

Has patch: set
Owner: changed from nobody to Josh Smeaton
Status: newassigned
Triage Stage: AcceptedReady for checkin

https://github.com/django/django/pull/4101/

New PR implementing Shai's original proposal which turned out to be a much better idea.

comment:9 Changed 5 years ago by Shai Berger <shai@…>

Resolution: fixed
Status: assignedclosed

In 1fbe8a2de334bfec5e9b77e36f8a3c1cf2cd70be:

Fixed #24200 -- Made introspection bypass statement cache

comment:10 Changed 5 years ago by Shai Berger <shai@…>

In 3518d51697f1f2e0a516cd82dfc6e05f9f686dab:

[1.8.x] Fixed #24200 -- Made introspection bypass statement cache

Backport of 1fbe8a2de3 from master

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