Обсуждение: Re: What happens when you run out of transaction ID's ???

On Tue, 28 Jan 2003, Greg Patnude wrote:

> What the problem was seemed to be this:
>
> I have a co-programmer who  was working on a web-based system that runs
> against a separate database on a dedicated postgreSQL 7.2.1 / FreeBSD
> 4.6 / Apache server...
>
> He had created a "login" routine that basically did this:
>
>     BEGIN
>
>         while {
>
> -->             BEGIN
>
>         }
>
>     END
>
> His code NEVER exited the WHILE statement so he kept on beginning over
> and over again... After about 3-4 hours, postgreSQL would just go away.
> I enabled some extended logging and restarted the data server....
>
> A couple days later -- it happened again and the server quit... I looked
> in the logs and saw begin transaction and transaction ID's up around
> 2,147,000,000 before postgreSQL quit -- I figured that his error was
> basically running postgreSQL out of transaction ID's -- It never got a
> commit or rollback.... Just 2 bzillion BEGIN's inside the WHILE loop...
>
> I am looking for some daily affirmation that my diagnosis was on
> track...

I can't say for 7.2 (have access to one at work but not home), but on my
dev 7.4 box, a sequence of 100000 begins followed by a single end doesn't
seem to drive up the transaction id counter since my next transaction
isn't offset by that number.  I'll check at work tomorrow if noone else
jumps in before then.

>> His code NEVER exited the WHILE statement so he kept on beginning over
>> and over again... After about 3-4 hours, postgreSQL would just go away.
>> I enabled some extended logging and restarted the data server....

Now that I think about it, 4 billion consecutive BEGINs (or a BEGIN
followed by 4 billion commands of any kind) would eventually hit the
CommandCounter wraparound limit.  This doesn't cause a crash though.
This is what it looks like in 7.2:

regression=# begin;
NOTICE:  BEGIN: already a transaction in progress
BEGIN
regression=# begin;
NOTICE:  BEGIN: already a transaction in progress
ERROR:  You may only have 2^32-1 commands per transaction
regression=# begin;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
regression=# begin;
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
*ABORT STATE*
regression=# begin;

(No, I didn't really execute 4 billion begins, just twiddled the counter
with a debugger ...)

But Greg still hasn't told us exactly what undesirable behavior he's
seeing.

            regards, tom lane