Re: [ODBC] Q: Is there a way to force psqlODBC with enabledUseDeclareFetch 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
ODBCdriver operate in such fashion. I was hoping there is some combination of (statement) settings that would tell the
driverthat 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
wouldfree significant resources, is not really worth the effort on PostgreSQL. The other issue that I am thinking of is
thefact that these sessions will be terminated on "idle_in_transaction_session_timeout" and rollback the outer
transactionand all consequently, all nested savepoints. 
>
> I would really appreciate a comment from someone more knowledgeable about these two assumptions. I am still in my
initialstrides with PG. 
>
> -- Regards, Matej.