Thread: Commands per transaction
Is there any way of bumping this limit or am I stuck breaking up the transaction? ERROR: cannot have more than 2^32-1 commands in a transaction --
On Sat, 2006-01-21 at 12:48 -0300, Alvaro Herrera wrote: > Rod Taylor wrote: > > Is there any way of bumping this limit or am I stuck breaking up the > > transaction? > > Wow, I never heard of anyone reaching the limit :-( Sorry, you are > stuck (short of changing CommandId to 64 bits, which would bloat your > tables considerably ...) It was a complex plpgsql function iterating through a large volume of data. It would appear that each IF statement in plpgsql is taken as a separate statement because I don't believe I was dealing with more than about 1.2B rows in a FOR ... EXECUTE 'SELECT ...' loop. --
Rod Taylor wrote: > Is there any way of bumping this limit or am I stuck breaking up the > transaction? Wow, I never heard of anyone reaching the limit :-( Sorry, you are stuck (short of changing CommandId to 64 bits, which would bloat your tables considerably ...) -- Alvaro Herrera Developer, http://www.PostgreSQL.org "[PostgreSQL] is a great group; in my opinion it is THE best open source development communities in existence anywhere." (Lamar Owen)
On Sat, 21 Jan 2006, Alvaro Herrera wrote: > Rod Taylor wrote: >> Is there any way of bumping this limit or am I stuck breaking up the >> transaction? > > Wow, I never heard of anyone reaching the limit :-( Sorry, you are > stuck (short of changing CommandId to 64 bits, which would bloat your > tables considerably ...) Would it be possible to increment the command counter only for commands that do updates? - Heikki
On Sat, 2006-01-21 at 12:48 -0300, Alvaro Herrera wrote: > Rod Taylor wrote: > > Is there any way of bumping this limit or am I stuck breaking up the > > transaction? > > Wow, I never heard of anyone reaching the limit :-( Sorry, you are > stuck (short of changing CommandId to 64 bits, which would bloat your > tables considerably ...) Does a subtransaction receive an independent command counter? If so, I could shove a bunch of work into large subtransactions allowing me to avoid bumping the main transactions command counter. Heck, PostgreSQL could automatically create a subtransaction (savepoint) with no rollback name. The client sending a commit or rollback always operates on the parent transaction and the child follows (committing or rolling back when required). --
Heikki Linnakangas <hlinnaka@iki.fi> writes:
> Would it be possible to increment the command counter only for commands
> that do updates?
Probably that would work, but I'm unsure whether we have infrastructure
that would let you detect that reliably...
regards, tom lane
Rod Taylor <pg@rbt.ca> writes:
> Does a subtransaction receive an independent command counter?
No such luck, and IIRC that would not be easy at all to change :-(
regards, tom lane
Rod Taylor schrieb: > On Sat, 2006-01-21 at 12:48 -0300, Alvaro Herrera wrote: > >>Rod Taylor wrote: >> >>>Is there any way of bumping this limit or am I stuck breaking up the >>>transaction? >> >>Wow, I never heard of anyone reaching the limit :-( Sorry, you are >>stuck (short of changing CommandId to 64 bits, which would bloat your >>tables considerably ...) > ... As a quick fix (as is quickly fixed, quick running ;) You could load your update data to a temp table via COPY and then update a large table in one sweep. I guess you dont have 2^31 tables to update? ;)
On Sat, 2006-01-21 at 22:30 +0100, Tino Wildenhain wrote: > Rod Taylor schrieb: > > On Sat, 2006-01-21 at 12:48 -0300, Alvaro Herrera wrote: > > > >>Rod Taylor wrote: > >> > >>>Is there any way of bumping this limit or am I stuck breaking up the > >>>transaction? > >> > >>Wow, I never heard of anyone reaching the limit :-( Sorry, you are > >>stuck (short of changing CommandId to 64 bits, which would bloat your > >>tables considerably ...) > As a quick fix (as is quickly fixed, quick running ;) > You could load your update data to a temp table via > COPY and then update a large table in one sweep. > I guess you dont have 2^31 tables to update? ;) I wouldn't have written a plpgsql function if I was doing the same thing with every tuple. Running multiple statements across the 40GB heap would take much longer to complete. I've resorted to a client side script that COPYs the data out of various partitions in the main structure, applies the logic, then COPYs it back in again to a new partition. Once complete drop the original partition so the datasets swap. This is somewhat slower than my original method of doing it all on the server side but not by too much since the reduced CPU load seems to offset the increased network IO. --