Re: [ODBC] Q: Is there a way to force psqlODBC with enabledUseDeclareFetch to commit statements and avoid nesting transactions(savepoints)? - Mailing list pgsql-odbc

On Tue, 29 Aug 2017 08:05:41 +0900
"Inoue, Hiroshi" <h-inoue@dream.email.ne.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
theODBC driver operate in such fashion. I was hoping there is some combination of (statement) settings that would tell
thedriver 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
cursoropen until EOF.
 
The problem we face is that the first statement will open a transaction together with the cursor and keep the
transactionopen until this cursor is fetched to the end. All other statements we send on the same connection get nested
withinthis transaction (savepoints), and the connection state will be in idle-in-transaction until the initial cursor
isclosed/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
forwardas needed. PostgreSQL WITH HOLD cursor will survive COMMIT and operate as expected so I am looking at a way to
achievethis behavior through the ODBC.
 

A simple "grep" (grep -i "'close \"SQL_") over psqlodbc_*.log shows what is currently happening. Roughly at line 700
thehuge 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
fromstaying "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.


-- 
Matej Mihelič <matej.mihelic@neosys.si>