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. My hope is that this would allow the driver to avoid
keepingthe transaction open and prevent the application from staying "idle in transaction" until all cursors are
fetchedcompletely.
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
freesignificant resources, is not really worth the effort on PostgreSQL. The other issue that I am thinking of is the
factthat these sessions will be terminated on "idle_in_transaction_session_timeout" and rollback the outer transaction
andall 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.