[MAPSERVER-USERS] postgres transaction warnings

[rich.fromm]

rich.fromm wrote:
> 
> Did some more looking around on my own.
> 

The bug:
http://trac.osgeo.org/mapserver/ticket/2497
says:
All postgis queries are "DECLARE CURSOR ..." so a transaction is necessary

The patch:
http://trac.osgeo.org/mapserver/attachment/ticket/2497/ms_postgis_begin_to_connect.diff
also echos this sentiment:
/* start a transaction, since it's required by all subsequent (DECLARE
CURSOR) queries */ 

I can't find anything in the postgres SQL documentation that explicitly
states
this, searching through DECLARE:
http://www.postgresql.org/docs/8.1/interactive/sql-declare.html
FETCH:
http://www.postgresql.org/docs/8.1/interactive/sql-fetch.html
and CLOSE:
http://www.postgresql.org/docs/8.1/interactive/sql-close.html

Although there are a number of places in the docs that somewhat imply it.

DECLARE mentions trasactions:

--- begin ---
WITH HOLD specifies that the cursor may continue to be used after the
transaction that created it successfully commits. WITHOUT HOLD specifies
that
the cursor cannot be used outside of the transaction that created it. 
--- end ---

as does CLOSE:

--- begin ---
Every non-holdable open cursor is implicitly closed when a transaction is
terminated by COMMIT or ROLLBACK. A holdable cursor is implicitly closed if
the transaction that created it aborts via ROLLBACK. If the creating
transaction successfully commits, the holdable cursor remains open until an
explicit CLOSE is executed, or the client disconnects.
--- end ---

--- begin ---
And the example in FETCH uses one:

BEGIN WORK;

-- Set up a cursor:
DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;

# ...

-- Close the cursor and end the transaction:
CLOSE liahona;
COMMIT WORK;
--- end ---

So I think that maybe effectively answers my question (2), why transactions.

But I'm still baffled by the rest of (1), why there are no COMMIT or END
statements.  Although the patch for bug 2497 does seem to have gotten rid of
my warning messages.

- Rich

-- 
View this message in context: http://www.nabble.com/postgres-transaction-warnings-tp15749333p15751359.html
Sent from the Mapserver - User mailing list archive at Nabble.com.