Skip site navigation (1) Skip section navigation (2)

Re: count on transaction ID

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "ddgs" <gsmaths(at)gmail(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: count on transaction ID
Date: 2012-03-12 16:05:51
Message-ID: 4F5DD88F020000250004610B@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
ddgs <gsmaths(at)gmail(dot)com> wrote:
 
> It is just a simple idea syntax, not the exact one.
 
Then it doesn't seem possible to give an exact answer as to what it
will do.  The effect on transaction IDs will depend on whether
you're talking about one DELETE statement with a range of values in
the WHERE clause or a series of DELETE statements, one for each row,
which are not bounded by a transaction through some other means.
  
> Anyway, I am wonder how to get the 2^31 transaction IDs to cause
> the failure
 
One of the easiest ways to get to such a failure is to disable
autovacuum or make it less aggressive.
 
> But I get the wraparound error warning when I delete a large no.
> of rows.  So the wraparound failure is due to what reason, that I
> still have no idea (at least not the transaction limit, I guess) 
 
>From what little information you've provided, it's hard to tell.  It
might be that the DELETEs are generating a very large number of
database transactions, each of which is consuming a transaction ID. 
It might be that your DELETE is running for so long that it's
interfering with autovacuum's ability to clean up after a high
volume of other transactions.  It could be that you are simply not
vacuuming aggressively enough, and the DELETE happened to come along
at the point where the issue became critical, and is thus an
"innocent bystander" and not the culprit.
 
-Kevin

In response to

Responses

pgsql-performance by date

Next:From: Tomas VondraDate: 2012-03-12 16:10:28
Subject: Re: Advice on Controller card for SAS disks
Previous:From: Tomas VondraDate: 2012-03-12 16:03:59
Subject: Re: Tuning wizard

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group