Thread: Commands per transaction

Commands per transaction

From
Rod Taylor
Date:
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
-- 



Re: Commands per transaction

From
Rod Taylor
Date:
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.


-- 



Re: Commands per transaction

From
Alvaro Herrera
Date:
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)


Re: Commands per transaction

From
Heikki Linnakangas
Date:
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


Re: Commands per transaction

From
Rod Taylor
Date:
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).

-- 



Re: Commands per transaction

From
Tom Lane
Date:
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


Re: Commands per transaction

From
Tom Lane
Date:
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


Re: Commands per transaction

From
Tino Wildenhain
Date:
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? ;)


Re: Commands per transaction

From
Rod Taylor
Date:
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.

--