From: Alex Stewart <astewart@freedomintelligence.com>
Message-Id: <200006061755.NAA03625@quill.freedomintelligence.com>
Subject: Re: Transactionless ODBC
To: tip@pc10.radnoti-szeged.sulinet.hu (Kovacs Zoltan Sandor)
Date: Tue, 6 Jun 2000 13:55:14 -0400 (EDT)
Cc: pgsql-interfaces@postgresql.org
In-Reply-To: 
 <Pine.LNX.4.05.10006061529340.230-100000@pc10.radnoti-szeged.sulinet.hu>
	from "Kovacs Zoltan Sandor" at Jun 06, 2000 03:43:10 PM
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

> > SQL>create user roger
> > Error creating the table;
> > ERROR:  CREATE USER: may not be called in a transaction block, SQLSTATE=S0001
> A workaround for this problem is: try inserting rows into pg_shadow
> (and also into pg_group if it is important for you).

Thanks for the workaround.  However, only the postgres super-user can change
pg_shadow, so I would need to connect as him.  I might be able to make do,
but I would like to know if there is a cleaner solution.
 
> We had similar problems with transactions on Windows. Please check if "Use
> DeclareFetch" option is off (configuring the ODBC driver): if it is on,
> most statement implies a transaction BEGIN. (I don't know that you
> have the opportunity to configure the ODBC driver on Unix, on Windows you
> can.)

I've set globals.use_declarefetch to 0 via /etc/odbcinst.ini with the
following two lines:
[PostgreSQL]
UseDeclareFetch = 0

That seems to set use_declarefetch to the right value, but I'm still in a
transaction because STMT_UPDATE(self) is true (i.e.
stmt->statement_type > STMT_TYPE_SELECT i.e. the statement type is one of:
INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, or REVOKE).  Is this
the intended behaviour of STMT_TYPE_SELECT?  i.e. I thought cursors (the
reason for doing everything in a transaction as stated in the comment) were
only for getting the results back from a SELECT statement.  So why do we
use transactions when we don't need cursors?  Mind you, I've only recently
come in contact with database programming, so the answer could be very
obvious.

The troubling code is (if I have already posted it, I apologize)

/*      Begin a transaction if one is not already in progress */
/*      The reason is because we can't use declare/fetch cursors without
        starting a transaction first.
*/
if ( ! self->internal && ! CC_is_in_trans(conn) && (globals.use_declarefetch || STMT_UPDATE(self))) {

Alex Stewart