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

Hi,

On 2017/08/29 13:32, Matej Mihelič wrote:
> On Tue, 29 Aug 2017 08:05:41 +0900
> "Inoue, Hiroshi" <h-inoue(at)dream(dot)email(dot)ne(dot)jp> wrote:
>
>> 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?
> Hi,
>
> No, what I am looking for is an option to COMMIT the state immediately after the OPEN CURSOR statement and keep this cursor open until EOF.

Though it's possible, the server executes the query thoroughly and saves
all the results in the server
before the completion of COMMIT for subsequent FETCH operations. Though
such a implementation
is not necessary for sequential or index scan theoretically, no one has
tried other implementations.

regards,
Hiroshi Inoue

> The problem we face is that the first statement will open a transaction together with the cursor and keep the transaction open until this cursor is fetched to the end. All other statements we send on the same connection get nested within this transaction (savepoints), and the connection state will be in idle-in-transaction until the initial cursor is closed/commited
>
> Since we open the first cursor on a huge result set that will probably never get fetched to the end this creates a problem. We cannot close this statement as the software components attached to it expect a working cursor for scrolling forward as needed. PostgreSQL WITH HOLD cursor will survive COMMIT and operate as expected so I am looking at a way to achieve this behavior through the ODBC.
>
> A simple "grep" (grep -i "'close \"SQL_") over psqlodbc_*.log shows what is currently happening. Roughly at line 700 the huge lookup table is opened and everything from this point on is nested in this transaction.
>
> Line 662: [32.788]PQsendQuery: 07D1B408 'close "SQL_CUR09101C50";commit'
> Line 685: [32.861]PQsendQuery: 07D1B408 'close "SQL_CUR08FEEAF8";commit'
> Line 700: [32.892]PQsendQuery: 07D1B408 'close "SQL_CUR08FEE2F8";commit'
> Line 727: [33.116]PQsendQuery: 07D1B408 'close "SQL_CUR090F7A50"'
> Line 739: [33.183]PQsendQuery: 07D1B408 'close "SQL_CUR090F7A50"'
> Line 762: [33.647]PQsendQuery: 07D1B408 'close "SQL_CUR090F7A50"'
> Line 808: [34.026]PQsendQuery: 07D1B408 'close "SQL_CUR09101C50"'
> Line 838: [34.213]PQsendQuery: 07D1B408 'close "SQL_CUR09101C50"'
> Line 873: [34.795]PQsendQuery: 07D1B408 'close "SQL_CUR090F7D00"'
> Line 880: [44.953]PQsendQuery: 07D1B408 'close "SQL_CUR090F7D00"'
> Line 922: [45.507]PQsendQuery: 07D1B408 'close "SQL_CUR090F7D00"'
> Line 941: [45.625]PQsendQuery: 07D1B408 'close "SQL_CUR090F7D00"'
> Line 949: [45.692]PQsendQuery: 07D1B408 'close "SQL_CUR090F7D00"'
> Line 988: [45.774]PQsendQuery: 07D1B408 'close "SQL_CUR090F7D00"'
> Line 1000: [45.814]PQsendQuery: 07D1B408 'close "SQL_CUR08FDCBF8"'
> Line 1014: [45.868]PQsendQuery: 07D1B408 'close "SQL_CUR08FDCBF8"'
> Line 1025: [45.891]PQsendQuery: 07D1B408 'close "SQL_CUR08FDCBF8"'
> Line 1036: [45.922]PQsendQuery: 07D1B408 'close "SQL_CUR08FDCBF8"'
>
> Regards, Matej.
>> 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.