1

Here's a very simple reproducible case that I haven't seen an answer for. If I use no name on the cursor (default), everything works fine. If, however, I use a named cursor, all of a sudden there is an error that select INTO is not allowed. Does anybody know why psycopg2/pg would behave differently for the query syntax between a named and not named cursor in this case?

(note, this is part of a large multi-step query with 2 temporary tables and a full outer join to get a comparison at the end. The objective of using the named cusor is so that the potentially very large end result doesn't use up all of the memory and end up dying. I might have millions of rows returned from the outer join.)

Works:

>>> cur = conn.cursor()
>>> cur.execute("select NOW() into temporary table foo")
>>> cur.close()

Doesn't work:

>>> cur = conn.cursor("diff_query")
>>> cur.execute("select NOW() into temporary table foo")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.errors.SyntaxError: SELECT ... INTO is not allowed here
LINE 1: ...URSOR WITHOUT HOLD FOR select NOW() into temporary table foo
Doug Hughes
  • 11
  • 1
  • Note: I'm using the named cursor as part of the technique to deal automatically with large potential result sets as described in the top answer here: https://stackoverflow.com/questions/17199113/psycopg2-leaking-memory-after-large-query/17203872 Maybe that's the wrong thing to do. – Doug Hughes Apr 04 '21 at 14:19
  • 1
    It is. From here [Declare](https://www.postgresql.org/docs/current/sql-declare.html): "query A SELECT or VALUES command which will provide the rows to be returned by the cursor." `SELECT INTO` is basically `CREATE TABLE AS`. – Adrian Klaver Apr 04 '21 at 15:19
  • yeah, it's definately the wrong thing to do when the query is DDL and is not returning rows to the caller. – Jasen Apr 05 '21 at 20:32

0 Answers0