Newbie questions relating to transactions - Mailing list pgsql-general

Hi,

I'm new to pgsql (but not databases in general) and I've run into a roadblock
that I'm having difficulty figuring out how to get around.  Any help would be
greatly appreciated!

I'm using the database to store the results of calculations over data in some
tables.  There are a lot of data records (~13,000), with the calculations
done over many (tens of thousands?) different subsets of those records, and
results stored for each subset.  (If a flag is set, each step of a subset
calculation is saved as well, but that flag isn't set in this particular
case.)

I have written a PL/pgSQL function that performs these calculations by reading
the needed data, calculating, and saving the results.  When run over a
smaller set of data, it works fine.  But when I tried to run it over this
larger set of data, I got the error message "ERROR:  cannot have more than
2^32-1 commands in a transaction".

I have looked into trying to control the transaction within my function, but
apparently this results in nested transactions, which is not supported by
pgsql 8.2 (my current version).  I've done some googling, and found
discussions mentioning savepoints, but they don't seem applicable.  (Most
discussions about savepoints are concerned with exceptions and rolling back
to a savepoint.  My issue is I need a commit in the middle of a transaction
so that the transaction doesn't get too big.)  If I just put commits within
the function, I get either "ERROR:  SPI_execute_plan failed executing
query "commit": SPI_ERROR_TRANSACTION" (just "commit;") or "ERROR:  cannot
begin/end transactions in PL/pgSQL" (with "execute 'commit';").

Here are my questions:

- Can I execute this logic without transaction control at all?  I don't really
need it in this case.  In fact, I'd prefer if every database change were
autocommitted when encountered (so I can watch progress; this takes a long
time to run).  If something fails, I can just re-run the routine.  The only
way that I have found (so far) to run the function is with a SELECT
statement, which I assume is starting the transaction.

- Is there such a thing as a "stored procedure" (as in DB2 or MS SQL) where I
can control transactions better than in a function?

- The message "HINT:  Use a BEGIN block with an EXCEPTION clause instead." was
displayed with the last error quoted above.  I need to look into this
suggestion further, as I was not able to readily find it in the manuals.
Will this solve my issue?  (I'm more than happy to research what I need to
do, if this will work...)

- Is there a way to commit a transaction mid way through it?  (I know, this
kinda defeats the purpose of a transaction, but thought I'd ask antway. :->)

- Does version 8.3 support nested transactions, or something else that would
resolve this issue.  I don't have a problem with upgrading, if necessary.

- Might you have some other way to get around this issue, short of changing
the function to only process a subset of the calculations at a time?  (This
wouldn't be terribly difficult to do, but it introduces some "messiness" into
the whole thing that I'd rather not introduce.)

Thanks for the help,

Carl