New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Named cursor is not closed when no query run #746

Closed
Tishka17 opened this Issue Jul 11, 2018 · 5 comments

Comments

Projects
None yet
2 participants
@Tishka17

Tishka17 commented Jul 11, 2018

  1. We use stored functions in postgresql, that return named cursor as out paramter.
    It has code like (it is not prodution code but shows an idea)
CREATE OR REPLACE FUNCTION list_myobj(
  OUT    o_rs     refcursor
) AS $$
BEGIN
   o_rs = 'result';
   OPEN o_rs FOR SELECT obj_name from myobj;
END
$$ language plpgsql;

We have many similar functions

  1. In python i call these functions and read the contents on o_rs. Code is similar to
with closing(conn.cursor()) as cursor:
  cursor.callproc("list_myobj")
  result = cursor.fetchone()
  with closing(self.dbo.cursor(result[0])) as cur2:
    return cur2.fetchall()
  1. It worked on version 2.7.4 of psycopg2, but on 2.7.5 it breaks when several funcitons called in same connection. I got an exception
psycopg2.ProgrammingError: cursor "result" already in use

As i can see in changes made for #716, cursor is not closed when no query is ran, which is not correct.

@dvarrazzo

This comment has been minimized.

Member

dvarrazzo commented Jul 11, 2018

I don't see how #716 is related to this. Or why you are using closing. Can you provide a failing test?

@Tishka17

This comment has been minimized.

Tishka17 commented Jul 11, 2018

it does not matter if i use closing(...) or close cursor manually.

In commit 7aea6b8 (related to that issue) i see check if (!self->query). I'm not sure, but probably there would be never any query in cursor, which was returned from a stored function. But at the same time it is not unused.

I'll try to prepare minimal example

@dvarrazzo

This comment has been minimized.

Member

dvarrazzo commented Jul 11, 2018

Uhm, I think I understand what you mean. Probably the test to choose whether to CLOSE the cursor or not shouldn't be on if a query has been run, rather if consuming has been attempted.

@Tishka17

This comment has been minimized.

Tishka17 commented Jul 13, 2018

This code works on psycopg 2.7.4 and raises exception on 2.7.5

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import psycopg2
from psycopg2.extensions import connection, cursor

conn: connection = psycopg2.connect(host="192.168.1.1", dbname="name", user="user", password="pass")
cur: cursor = conn.cursor()

cur.execute("""
CREATE OR REPLACE FUNCTION list_hello(
  OUT    o_rs     refcursor
) AS $$
BEGIN
   o_rs = 'result';
   OPEN o_rs FOR SELECT 'hello';
END
$$ language plpgsql;
""")

cur.callproc("list_hello")
res = cur.fetchone()[0]
cur2: cursor = conn.cursor(res)
print(cur2.fetchall())
cur2.close()

cur.callproc("list_hello")
res = cur.fetchone()[0]
cur2: cursor = conn.cursor(res)
print(cur2.fetchall())
cur2.close()
@dvarrazzo

This comment has been minimized.

Member

dvarrazzo commented Jul 13, 2018

Thank you very much: I'll try to work on it ASAP.

@dvarrazzo dvarrazzo modified the milestones: 2.7.6, psycopg 2.7.5 Jul 13, 2018

dvarrazzo added a commit that referenced this issue Jul 21, 2018

@dvarrazzo dvarrazzo closed this in 6d8f4f9 Jul 25, 2018

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