Cursors are being closed explicitly in autocommit mode

[Ali Teoman Unay]

Recently we started to get this exception time to time:

OperationalError: cursor “_django_curs_<id>” does not exist

especially when our traffic is higher than usual. Each time the error is in a different line of the code so it is not easy to follow but appearantly it is a synchronisation issue; the cursor is being closed before the transaction ended.

In django.db.models.sql.compiler.SQLAggregateCompiler:

def cursor_iter(cursor, sentinel, col_count, itersize):
    """
    Yield blocks of rows from a cursor and ensure the cursor is closed when
    done.
    """
    try:
        for rows in iter((lambda: cursor.fetchmany(itersize)), sentinel):
            yield rows if col_count is None else [r[:col_count] for r in rows]
    finally:
        cursor.close()

According to ​psycopg documentation, server-side cursors should not be closed explicitly if it is in autocommit mode.

According to ​Django documentation, Django sets autocommit mode true in default settings.

Of course it is possible to set withhold setting to false. In this case, cursor.close() must be called at the end but otherwise, according to the documentation, it should not be called at all. So if I am not mistaken there should be a conditional statement before calling cursor.close().

[Tim Graham]

Duplicate of #29257?

[Ali Teoman Unay]

Replying to Tim Graham:

Duplicate of #29257?

No they are not the same so I don't believe it's a duplicate. This issue is not about the creation of the cursor but closing of the cursor. It should not be closed explicitly at all if the autocommit mode is set to true. Yet, the solution (wrapping with try/except) might be okay for this issue also.

[Tim Graham]

Can you give steps to reproduce the error?

I read the psycopg2 docs that you linked to but I didn't spot the place where it says "server-side cursors should not be closed explicitly if it is in autocommit mode."

[Ali Teoman Unay]

Replying to Tim Graham:

Can you give steps to reproduce the error?

I read the psycopg2 docs that you linked to but I didn't spot the place where it says "server-side cursors should not be closed explicitly if it is in autocommit mode."

"Named cursors are usually created WITHOUT HOLD, meaning they live only as long as the current transaction. Trying to fetch from a named cursor after a commit() or to create a named cursor when the connection is in autocommit mode will result in an exception. It is possible to create a WITH HOLD cursor by specifying a True value for the withhold parameter to cursor() or by setting the withhold attribute to True before calling execute() on the cursor. It is extremely important to always close() such cursors, otherwise they will continue to hold server-side resources until the connection will be eventually closed. Also note that while WITH HOLD cursors lifetime extends well after commit(), calling rollback() will automatically close the cursor."

from ​here

It is hard to reproduce the error since it appears each time in a different line of our code and it is very rare. I believe it appears when the cursor is destroyed before the transaction ended and it points to a synchronization error which might be related to this issue.

[Tim Graham]

Yes, I saw that text but I don't understand how it translates into what you said. I guess we can accept the ticket even if the resolution is unclear.

[David Dahl]

I had this happen to me where my models.py module had been updated but I had not yet run makemigrations or migrate. Once I migrated, everything cleared up.

[Kurt Wheeler]

I can't figure out how to delete this comment but I read a bit more documentation and realized the answer to my issue is explicitly mentioned somewhere.

[Simon Charette]

For the record the above comment was related to the use of pg_bouncer which is documented to be causing issue with iterator().

I believe it appears when the cursor is destroyed before the transaction ended and it points to a synchronization error which might be related to this issue.

Ali, I'm not sure I'm following along here. The ticket mentions autocommit but you're a mentioning transactions here. Do you happen to use connection polling as well?

[Barış Yeşilçiçek]

I work with the OP and can confirm that this is a duplicate of #28062.