Opened 9 years ago

Closed 9 years ago

Last modified 9 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


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 by Josh Smeaton, 9 years ago

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
(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 by Simon Charette, 9 years ago

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

comment:3 by Claude Paroz, 9 years ago

Triage Stage: UnreviewedAccepted

comment:4 by Josh Smeaton, 9 years ago

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 by Josh Smeaton, 9 years ago

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 by Tim Graham, 9 years ago

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

comment:8 by Josh Smeaton, 9 years ago

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

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

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

Resolution: fixed
Status: assignedclosed

In 1fbe8a2de334bfec5e9b77e36f8a3c1cf2cd70be:

Fixed #24200 -- Made introspection bypass statement cache

comment:10 by Shai Berger <shai@…>, 9 years ago

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