Thread: status of CURSORs after DISCONNECT

Hello,

When an ESQL/C written process issues a

EXEC SQL DISCONNECT [connection];

do the opened CURSOR(s) still survive? We run into the problem that the
father process issues DISCONNECT before forking children, the forked child
CONNECTs to the same server and database again and "thinks" it has to CLOSE the
CURSOR (perhaps a long standing bug in our application we port now from
Sybase(...) to PostgreSQL).

Thanks

    matthias
-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

"Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen durchschaut"
"Believe little, scrutinise all, think by your own: How see through manipulations"
ISBN-10: 386489218X

Matthias Apitz <guru@unixarea.de> writes:
> When an ESQL/C written process issues a
> EXEC SQL DISCONNECT [connection];
> do the opened CURSOR(s) still survive?

No.  Cursors are purely session-local objects in Postgres.
I'm a bit surprised to hear it might be different in Sybase.

            regards, tom lane

On Thu, 28 Nov 2019 07:27:15 +0100, Matthias Apitz <guru@unixarea.de>
wrote:

>When an ESQL/C written process issues a
>
>EXEC SQL DISCONNECT [connection];
>
>do the opened CURSOR(s) still survive? We run into the problem that the
>father process issues DISCONNECT before forking children, the forked child
>CONNECTs to the same server and database again and "thinks" it has to CLOSE the
>CURSOR (perhaps a long standing bug in our application we port now from
>Sybase(...) to PostgreSQL).
>
>Thanks
>
>    matthias

Cursors are session specific so if the PG backend exits due to the
disconnect, any cursors it is managing will be closed.  

However, if you are using a connection pooler to keep PG backend
processes alive for reuse, then disconnecting does not necessarily end
the session.  In this case, you need to make sure any cursors are
closed because the next connection to the same backend process may
know nothing about them.

In any event, it's good practice always to close cursors when you are
done with them - if for no reason other than to release resources they
are holding.

George

On Thu, 28 Nov 2019 09:58:50 -0500, Tom Lane <tgl@sss.pgh.pa.us>
wrote:

>Matthias Apitz <guru@unixarea.de> writes:
>> When an ESQL/C written process issues a
>> EXEC SQL DISCONNECT [connection];
>> do the opened CURSOR(s) still survive?
>
>No.  Cursors are purely session-local objects in Postgres.
>I'm a bit surprised to hear it might be different in Sybase.
>
>            regards, tom lane
>

Sybase has a different notion of "session" that permits multiple
connections, and certain per session objects such as temp tables can
(optionally) be shared among all connections to the same session.

I'm not sure whether cursors similarly can be shared. Sybase allows
cursors and temp tables to be statically declared in the schema DDL.
Declared cursors and temp tables do not need to be "created"
explicitly - if they do not already exist, they can be instantiated
simply by mention in a query.

I have never tried sharing a cursor (or even using a declared cursor).
Queries in Sybase are *connection* specific, so I would think it would
be an error for multiple queries to try to reference the same cursor
name simultaneously.  But I haven't worked with Sybase for some years
so I am not up to date on the current software.

George