Re: Vacuum/visibility is busted

On 2013-02-07 11:15:46 -0800, Jeff Janes wrote:
> On Thu, Feb 7, 2013 at 10:09 AM, Pavan Deolasee
> <pavan(dot)deolasee(at)gmail(dot)com> wrote:
> >
> > Right. I don't have the database handy at this moment, but earlier in
> > the day I ran some queries against it and found that most of the
> > duplicates which are not accessible via indexes have xmin very close
> > to 2100345903. In fact, many of them are from a consecutive range.
>
> Does anyone have suggestions on how to hack the system to make it
> fast-forward the current transaction id? It would certainly make
> testing this kind of thing faster if I could make transaction id
> increment by 100 each time a new one is generated. Then wrap-around
> could be approached in minutes rather than hours.

I had various plpgsql functions to do that, but those still took quite
some time. As I needed it before I just spent some minutes hacking up a
contrib module to do the job.

I doubt it really think it makes sense as a contrib module on its own
though?

postgres=# select * FROM burnxids(500000);select * FROM
burnxids(500000);
burnxids
----------
5380767
(1 row)

Time: 859.807 ms
burnxids
----------
5880767
(1 row)

Time: 717.700 ms

It doesn't really work in a nice way:
if (GetTopTransactionIdIfAny() != InvalidTransactionId)
elog(ERROR, "can't burn xids in a transaction with xid");

for (i = 0; i < nxids; i++)
{
last = GetNewTransactionId(false);
}

/* don't keep xid as assigned */
MyPgXact->xid = InvalidTransactionId;

but it seems to work ;)

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services