Skip to content

Cached query results #88

Closed
Closed

Description

@binzabinza

Driver version

redshift-connector==2.0.903

Redshift version

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

  1. Open a redshift_connector connection object: rs_cnx
  2. query table using rs_cnx and receive result set of cardinality X
  3. New data is inserted to table by external process
  4. query table using rs_cnx and receive result set of cardinality > X

Actual behaviour

  1. Open a redshift_connector connection object: rs_cnx
  2. query table using rs_cnx and receive result set of cardinality X
  3. New data is inserted to table by external process
  4. 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()

Activity

  1. Brooke-white commented on Jan 31, 2022

    @Brooke-white
    Contributor

    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.

  2. Brooke-white commented on Jan 31, 2022

    @Brooke-white
    Contributor

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

  3. binzabinza commented on Jan 31, 2022

    @binzabinza
    Author

    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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions