Re: ODBC & Transactions?

> I think the feature Tom is referring to is called "Use
> Declare/Fetch" and can be found under Advanced Options for the
> ODBC Driver.

Declare/Fetch ... the driver automatically uses declare cursor/fetch to
handle SELECT statements and keeps 100 rows in a cache ... results in the
driver not sucking down lots of memory to buffer the entire result set ...

With this option on;

1) the first time - the rollback worked; *joy*
2) the second time - error message - This option not supported by ODBC
driver *no joy*
3) repeat 1,2 five times
4) any subsequent times - rollback does not work; but no "not supported"
error message

hmmm..

> > If you want to know what's really going on, try enabling query logging
> > at the postmaster, then look in the postmaster's log output
> > to see what
> > queries are really getting sent by ODBC.

How do I do this?  I have set the postmaster debug level to 2 and this is
what it came up with...

DEBUG:  StartTransactionCommand
DEBUG:  query: BEGIN WORK
DEBUG:  ProcessUtility: BEGIN WORK
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: update Practical set Description = 'An introduction to
adventure through a Canadian-
[SNIP]
where PracticalID = 1
DEBUG:  ProcessQuery
DEBUG:  query: SELECT oid FROM ONLY "staff" WHERE "staffid" = $1 FOR UPDATE
OF "staff"
DEBUG:  query: SELECT oid FROM ONLY "practicalcategory" WHERE
"practicalcategoryid" = $1 FOR UPDATE OF "practicalcategory"
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: ROLLBACK WORK
DEBUG:  ProcessUtility: ROLLBACK WORK
DEBUG:  CommitTransactionCommand

You can see my BEGIN WORK, update and ROLLBACK WORK queries, but there are a
lot of surrounding
StartTransactionCommand and CommitTransactionCommand.
What are the DEBUG:StartTransactionCommand and the
DEBUG:CommitTransactionCommand lines?

<SPECULATION>
It *looks* to me like something is wrapping all the queries in a
transaction.  Is this an autocommit feature?  If so, is it from ODBC or
postgres itself.  How does one turn it off - apparently "BEGIN WORK" *does
not* turn it off...
</SPECULATION>

Any help appreciated.

Regards,
Mike Miller
Application Software Developer,
School Of Physical Education,
University Of Otago,
+64 3 479 9123
http://physed.otago.ac.nz