Re: Q: Is there a way to force psqlODBC with enabled UseDeclareFetch to commit statements and avoid nesting transactions (savepoints)?

Hi,

On 2017/08/25 16:45, Matej Mihelič wrote:
> Q: Is there a way to force psqlODBC with enabled UseDeclareFetch to commit statements and avoid nesting transactions (savepoints)?
>
> Since generated cursors are declared WITH HOLD they would survive COMMIT, and I assume, release resources on the server. I know that this would not be appropriate for all statements!
>
> I am trying to determine if there is a combination of statement attributes or some other settings that would make the ODBC driver operate in such fashion. I was hoping there is some combination of (statement) settings that would tell the driver that we are sending a forward only, read only, statement.

Normal? read only and forward only cursors are closed automatically when
reached EOF.
Cursors are closed when statements are closed via SQLCloseCursor() or
SQLFreeStmt(.., SQL_CLOSE).

Do you want another option that cursors are closed at transaction end?

regards,
Hiroshi Inoue

> My hope is that this would allow the driver to avoid keeping the transaction open and prevent the application from staying "idle in transaction" until all cursors are fetched completely.
>
> What I'd like to achieve is an equivalent of the following "psql" script:
> BEGIN;
> declare cur1 NO SCROLL CURSOR WITH HOLD FOR SELECT * FROM pg_type;
> FETCH FORWARD 5 FROM cur1;
> COMMIT;
> BEGIN;
> declare cur2 NO SCROLL CURSOR WITH HOLD FOR SELECT * FROM pg_roles;
> FETCH FORWARD 5 FROM cur2;
> COMMIT;
>
> FETCH FORWARD 5 FROM cur1;
> FETCH FORWARD 5 FROM cur2;
> close cur1;
> close cur2;
>
> Perhaps my assumption, as I am coming from a different DB environment, that releasing transactions in such cases would free significant resources, is not really worth the effort on PostgreSQL. The other issue that I am thinking of is the fact that these sessions will be terminated on "idle_in_transaction_session_timeout" and rollback the outer transaction and all consequently, all nested savepoints.
>
> I would really appreciate a comment from someone more knowledgeable about these two assumptions. I am still in my initial strides with PG.
>
> -- Regards, Matej.