You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.34928
Client Operating System
Windows 10 ProBuild 19042.631
Python version
Python 3.7.3
Table schema
column
dtype
id (PK)
int8
symbol
varchar(64)
price
float8
timestamp
timestamp
Problem description
Expected behaviour
Open a redshift_connector connection object: rs_cnx
query table using rs_cnx and receive result set of cardinality X
New data is inserted to table by external process
query table using rs_cnx and receive result set of cardinality > X
Actual behaviour
Open a redshift_connector connection object: rs_cnx
query table using rs_cnx and receive result set of cardinality X
New data is inserted to table by external process
query table using rs_cnx and receive result set of cardinality == X
Other details
If the redshift_connector connection object is closed and reopened between queries, then the expected behaviour occurs.
Python Driver trace logs
Reproduction code
import time
import pandas as pd
import redshift_connector
query = "select * from db.table where symbol = 'symbol_name';"
rs_cnx = redshift_connector.connect(
host="hostname",
user="username",
password="password",
database="db"
)
try:
# initial query
df = pd.read_sql(query, con=rs_cnx)
print(df.shape)
with rs_cnx.cursor() as cursor:
cursor.execute(query)
print(len(cursor.fetchall()))
# wait for new data to be inserted
time.sleep(20)
# re-query
df = pd.read_sql(query, con=rs_cnx)
print(df.shape)
with rs_cnx.cursor() as cursor:
cursor.execute(query)
print(len(cursor.fetchall()))
finally:
rs_cnx.close()
@binzabinza , could you please try enabling autocommit or manual commit/rollback before executing the select statements? This issue is seen due to the both select statements being in the same transaction, which becomes "stale" after the table is modified by the external process. I've verified manual rollback/commit and enabling autocommit resolve this in the repro you provided.
To enable autocommit, set the autocommit property on the connection object to True. To manually commit or rollback you can call the commit() or rollback() method on the Connection object.
Please let me know if you continue to see this issue.
Ah this makes sense. I suppose the default isolation level is REPEATABLE READ which has the behavior of returning the same results for two select statements inside one transaction. I was expecting the behavior of READ COMMITTED.
Committing select transactions absolutely fixes the issue. Appreciate your help!
Activity
Brooke-white commentedon Jan 31, 2022
Hi @binzabinza ,
Thank you for opening this issue and providing the reproduction. I will look into this on my end and provide an update shortly. Linking #87.
Brooke-white commentedon Jan 31, 2022
@binzabinza , could you please try enabling autocommit or manual commit/rollback before executing the select statements? This issue is seen due to the both
selectstatements being in the same transaction, which becomes "stale" after the table is modified by the external process. I've verified manual rollback/commit and enabling autocommit resolve this in the repro you provided.To enable autocommit, set the
autocommitproperty on the connection object toTrue. To manually commit or rollback you can call thecommit()orrollback()method on the Connection object.Please let me know if you continue to see this issue.
binzabinza commentedon Jan 31, 2022
Ah this makes sense. I suppose the default isolation level is
REPEATABLE READwhich has the behavior of returning the same results for two select statements inside one transaction. I was expecting the behavior ofREAD COMMITTED.Committing select transactions absolutely fixes the issue. Appreciate your help!