Re: What happens when you run out of transaction ID's ???

"codeWarrior" <GPatnude@adelphia.net> writes:

Is this a plausible scenario ??? Is it possible to run postgreSQL into the
ground by using up all of the transaction ID's ?

Not in 7.2. How about providing some details about what's happening?
"something keeps killing postgreSQL" is not a useful bug report.

regards, tom lane

On Tue, 21 Jan 2003, codeWarrior wrote:

So ... Any takers ???

I don't think anyone saw the original, or at least I never received the
quoted message.

"codeWarrior" <GPatnude@adelphia.net> wrote in message
news:b0hpc2$pd9$1@news.hub.org...

PG 7.2.1

I have a co-programmer working on a separate Web-project that runs against

a

shared postgreSQL data server... something keeps killing postgreSQL -- I
believe that he has a nested loop around a BEGIN {transaction} block that
never issues a COMMIT.... and then END or ROLLBACK...

In the postgreSQL error logs -- (I have been restarting postgres with
'pg_ctl restart -D /usr/local/pgsql/data -m smart -l pgdblog.log'.. ) I

have

occasionally seen transaction ID's up in the 2 million + range -- shortly

million or billion? You should be fine up until you're in the billions.
At which point I think you need to vacuum everything.

Does the log show anything around the time it shuts down that might give a
hint?

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