Cached query results

[binzabinza]

I noticed that when re-querying a redshift table (with the same connection and query) that I receive the same result set, even when new data has been added to the table. Based on the query written, I would expect the result set to increase in size when the query is sent at a later time. This occurred when using pandas.read_sql() passing a redshift_connector connection object as well as opening a cursor from a redshift_connector connection object.
If I close and reopen the connection, I do see a result set with the correct cardinality.
Does this behavior have to do with how redshift itself is designed or is it more of a bug in the library?

[Brooke-white]

Hi @binzabinza ,

Thank you for reporting this issue, we will look into this behavior. Could you provide us with the version of redshift_connector you are using as well as a code snippet reproducing this issue?

From your description it sounds like the Redshift table is being modified by some outside process (i.e. not by redshift_connector). Is this assumption correct?

[binzabinza]

redshift-connector==2.0.903

Yes this is correct, there is a separate ETL process pushing data into redshift. When I open a connection to the db in order read in data, it does not seem to capture new data.

I'm not sure exactly what code to give you as you'll need to set up a table that is updating somewhat frequently. I also caught this by opening a connection using DBeaver and running a count on the table vs reading the results in python using redshift_connector. What I saw was the count going higher as time went on but the result set in python using redshift_connector stayed the same.

I will open a proper issue.

[Brooke-white]

Thank you for these additional details, @binzabinza .

I'm not sure exactly what code to give you as you'll need to set up a table that is updating somewhat frequently.

An example table (i.e. create table my_test (c1 int);) could work for this. That way we can avoid exposing the details of your project and provide a reproducible example. The simplest case we can reproduce this issue in should be sufficient here.

With that said, I'm wondering if this behavior is seen when directly using the redshift_connector library. If it is not apparent then, my guess would be that there is something going wrong during this integration with the other tools. If it's directly apparent with redshift_connector than it should be a bit easier to target the root cause :).

[binzabinza]

in the issue I created a simplified version of the table. I can verify that the other process is indeed inserting new records into the table properly and that these new records are not returned when using the redshift_connector connection object unless i kill the existing connection object and reconnect.

Hypothesis here is that the redshift_connector connection object is possibly looking at cached data? I'm not positive this is an issue with this library - maybe it has something to do with how redshift itself handles client connections.

[Brooke-white]

Thanks, @binzabinza ! I'll take a look at the issue and dig into redshift_connector's behavior to see what's going on here. I'll update in the issue as I get more information.

[binzabinza]

Default transaction isolation level is REPEATABLE READ which will return the same results for all queries in a single transaction even when a different transaction has changed the underlying data. Many people (like myself) might expect the behavior to be that of transaction isolation level READ COMMITTED which will always reflect committed transactions to the table. By setting autocommit=True (or by manually committing after every select) you will guarantee that each select is the end of a transaction and that each new select is a new transaction. This will give the expected READ COMMITTED behavior.

See Issue #88