Query works in postgres shell but sometimes fails to return results in psycopg2

user_14709214 0
Viewed 217

I'm completely stumped.

The query looks something like this:

WITH e AS(
    INSERT INTO TEAMS(TEAM_NAME, SPORT_ID, TEAM_GENDER)
        VALUES ('Cameroon U23','1','M')
    ON CONFLICT (TEAM_NAME, SPORT_ID, TEAM_GENDER)
    DO NOTHING
    RETURNING TEAM_ID
)
SELECT * FROM e
UNION
    SELECT TEAM_ID FROM TEAMS WHERE LOWER(TEAM_NAME)=LOWER('Cameroon U23') AND SPORT_ID='1' AND LOWER(TEAM_GENDER)=LOWER('M');

And the python code like this:

sqlString = """WITH e AS(
                        INSERT INTO TEAMS(TEAM_NAME, SPORT_ID, TEAM_GENDER)
                            VALUES (%s,%s,%s)
                        ON CONFLICT (TEAM_NAME, SPORT_ID, TEAM_GENDER)
                        DO NOTHING
                        RETURNING TEAM_ID
                    )
                    SELECT * FROM e
                    UNION
                        SELECT TEAM_ID FROM TEAMS WHERE LOWER(TEAM_NAME)=LOWER(%s) AND SPORT_ID=%s AND LOWER(TEAM_GENDER)=LOWER(%s);"""

cur.execute(sqlString, (TEAM_NAME, SPORT_ID, TEAM_GENDER, TEAM_NAME, SPORT_ID, TEAM_GENDER,))
fetch = cur.fetchone()[0]

The error that I get is on "cur.fetchone()[0]" because "cur.fetchone()" doesn't return any values for some reason. I have also tried "cur.fetchall()" but it's the same issue.

This query works every time without fail in the normal postgres shell. However, in my python code using psycopg2, it will sometimes error out and not return anything. When I check the DB from the shell, the data I am looking for is there so it is the select query that should be returning something but isn't.

I am not sure if this is relevant, but I am creating concurrent connections (not connection pools) and doing multiple of these queries at once. Each query has a different team, however, to prevent deadlock.

psycopg2 postgresql sql python database
1 Answers

I have found the issue. It was to do with me using concurrency. I was wrong in saying that each query has a different team. The teams might sometimes be the same.

But the main issue was occurring because my INSERT would try and put some data in and find a duplicate because a concurrent query was also trying to put the same data in. But then for some reason, the SELECT wouldn't find that data. I don't exactly what the issue is but that's my understanding.

I had to change to doing a SELECT, checking if there was a result, then doing an INSERT if there wasn't and then doing a final SELECT if the INSERT didn't return anything. The INSERT does not return anything sometimes because it encounters a conflict with an entry that appeared after the first SELECT was executed.

EDIT: Nevermind. The problem was, in fact, that my deadlock_timeout was too low. My program wasn't actually reaching deadlock (where two processes are waiting on each other and cannot because they are also dependent on the other finishing). So increasing the deadlock_timeout to be larger than the average time for one of my processes to complete was the solution. THIS WILL NOT WORK if your program is actually reaching deadlock. In that case, fix it, because it should not be reaching deadlock ever.

Hope this helps someone.

user_147092140
Related