On 2013-02-07 11:15:46 -0800, Jeff Janes wrote: > On Thu, Feb 7, 2013 at 10:09 AM, Pavan Deolasee > <pavan.deola...@gmail.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
diff --git a/contrib/Makefile b/contrib/Makefile index 36e6bfe..aca12a7 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -54,7 +54,8 @@ SUBDIRS = \ tsearch2 \ unaccent \ vacuumlo \ - worker_spi + worker_spi \ + xidfuncs ifeq ($(with_openssl),yes) SUBDIRS += sslinfo diff --git a/contrib/xidfuncs/Makefile b/contrib/xidfuncs/Makefile new file mode 100644 index 0000000..6977a3b --- /dev/null +++ b/contrib/xidfuncs/Makefile @@ -0,0 +1,18 @@ +# contrib/xidfuncs/Makefile + +MODULE_big = xidfuncs +OBJS = xidfuncs.o + +EXTENSION = xidfuncs +DATA = xidfuncs--1.0.sql + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/xidfuncs +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/xidfuncs/xidfuncs.c b/contrib/xidfuncs/xidfuncs.c new file mode 100644 index 0000000..e11b4fb --- /dev/null +++ b/contrib/xidfuncs/xidfuncs.c @@ -0,0 +1,50 @@ +/*------------------------------------------------------------------------- + * contrib/xidfuncs/xidfuncs.c + * + * Copyright (c) 2013, PostgreSQL Global Development Group + * + * IDENTIFICATION + * contrib/xidfuncs/xidfuncs.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "access/transam.h" +#include "access/xact.h" +#include "funcapi.h" +#include "storage/proc.h" + +PG_MODULE_MAGIC; + +PG_FUNCTION_INFO_V1(burnxids); + +extern Datum burnxids(PG_FUNCTION_ARGS); + +Datum +burnxids(PG_FUNCTION_ARGS) +{ + int nxids = PG_GETARG_INT32(0); + int i; + TransactionId last; + + if (nxids <= 1) + elog(ERROR, "can't burn a negative amount of xids"); + + if (nxids > 500000) + elog(ERROR, "burning too many xids is dangerous"); + + 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; + + return Int64GetDatum((int64)last); +} diff --git a/contrib/xidfuncs/xidfuncs.control b/contrib/xidfuncs/xidfuncs.control new file mode 100644 index 0000000..bca7194 --- /dev/null +++ b/contrib/xidfuncs/xidfuncs.control @@ -0,0 +1,5 @@ +# xidfuncs extension +comment = 'xid debugging functions' +default_version = '1.0' +module_pathname = '$libdir/xidfuncs' +relocatable = true
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers