[ODBC] Q: Is there a way to force psqlODBC with enabled UseDeclareFetch tocommit statements and avoid nesting transactions (savepoints)?
От | Matej Mihelič |
---|---|
Тема | [ODBC] Q: Is there a way to force psqlODBC with enabled UseDeclareFetch tocommit statements and avoid nesting transactions (savepoints)? |
Дата | |
Msg-id | 2dc8921cc299459eb8bcc91bab1ae37b@neosys.si обсуждение исходный текст |
Ответы |
Re: [ODBC] Q: Is there a way to force psqlODBC with enabledUseDeclareFetch to commit statements and avoid nesting transactions(savepoints)?
|
Список | pgsql-odbc |
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.
В списке pgsql-odbc по дате отправления: