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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
