Make introspection bypass statement caching on Oracle

[Shai Berger]

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.

[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()
[]

[Simon Charette]

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

[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).

[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.

[Tim Graham]

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

[Josh Smeaton]

See ​https://github.com/django/django/pull/4090

[Josh Smeaton]

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

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

[Shai Berger <shai@…>]

In 1fbe8a2de334bfec5e9b77e36f8a3c1cf2cd70be:

Fixed #24200 -- Made introspection bypass statement cache

[Shai Berger <shai@…>]

In 3518d51697f1f2e0a516cd82dfc6e05f9f686dab:

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

Backport of 1fbe8a2de3 from master