search

mfenniak / pg8000

A Pure-Python PostgreSQL Driver
http://pythonhosted.org/pg8000/
Other
222 stars 55 forks source link

changing current schema does not invalidate cache #136

Closed MathieuRoger closed 7 years ago

MathieuRoger commented 7 years ago

Let's say we have one database with too different schemas : schema1, schema2 containing the same table definition T1.

cursor.execute('set search_path schema1') cursor.execute('select * from T1') ... cursor.execute('set search_path schema2') cursor.execute('select * from T1') # here gives the same result than above because it searches it in the cache A possible simple correction is to invalidate the cache when a SET statement is performed. For example by adding set to the DDL_COMMANDS variable :

DDL_COMMANDS = b("ALTER"), b("CREATE"), b("SET")

tlocke commented 7 years ago

I think that clearing the cache with each SET would be overly broad. There could be lots of SET TRANSACTION... statements. So I've admitted defeat and put a note in the README saying to avoid changing the search_path https://github.com/mfenniak/pg8000/commit/d38e0c3ea4254d884e6df1a2951f234510c26352.

MathieuRoger commented 5 years ago

At least, could it be possible to add and maintain a method that clear the cache on Connection. So that someone could use it if he need to.