Here is a patch that will warn you during VACUUM when you are within 75%
of oid/xid wraparound.

I will apply it if 7.2 has no solution to the wraparounds.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.195
diff -c -r1.195 vacuum.c
*** src/backend/commands/vacuum.c	2001/05/25 15:45:32	1.195
--- src/backend/commands/vacuum.c	2001/06/12 23:25:58
***************
*** 159,166 ****
  static bool enough_space(VacPage vacpage, Size len);
  static void init_rusage(VacRUsage *ru0);
  static char *show_rusage(VacRUsage *ru0);
  
- 
  /*
   * Primary entry point for VACUUM and ANALYZE commands.
   */
--- 159,166 ----
  static bool enough_space(VacPage vacpage, Size len);
  static void init_rusage(VacRUsage *ru0);
  static char *show_rusage(VacRUsage *ru0);
+ static void check_limits(void);
  
  /*
   * Primary entry point for VACUUM and ANALYZE commands.
   */
***************
*** 236,241 ****
--- 236,242 ----
  
  	/* clean up */
  	vacuum_shutdown();
+ 	check_limits();
  }
  
  /*
***************
*** 2645,2648 ****
--- 2646,2672 ----
  			 (int) (ru1.tv.tv_usec - ru0->tv.tv_usec) / 10000);
  
  	return result;
+ }
+ 
+ /*
+  *	check if we are near OID or XID wraparound
+  */
+ static void check_limits(void)
+ {
+ 	Oid nextOid;
+ 
+ 	/* If we are 75% to the limit, warn the user */
+ 	if (GetCurrentTransactionId() >
+ 		~(TransactionId)0 - (~(TransactionId)0) / 4)
+ 		elog(NOTICE,"You are within 75%% of the limit for transaction ids.\n"
+ 			"Dumping your databases, running initdb, and reloading will reset\n"
+ 			"the transaction id counter.");
+ 
+ 	/* If we are 75% to the limit, warn the user */
+ 	GetNewObjectId(&nextOid);
+ 	if (nextOid > ~(Oid)0 - (~(Oid)0) / 4)
+ 		elog(NOTICE,"You are within 75%% of the limit for object ids.\n"
+ 			"If you are not using object ids as primary keys, dumping your\n"
+ 			"databases, running initdb, and reloading will reset\n"
+ 			"the oid counter.");
  }
Tue, 12 Jun 2001 19:33:15 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Patch to warn about oid/xid wraparound
On Tuesday 12 June 2001 19:33, Bruce Momjian wrote:
> Here is a patch that will warn you during VACUUM when you are within 75%
> of oid/xid wraparound.

> I will apply it if 7.2 has no solution to the wraparounds.

Is this something a potential 7.1.3 release might need?  I consider this a 
bug workaround, actually.  Or even a feature patch against 7.1.x that people 
can download if they think they need it?

As PostgreSQL gets used in bigger and bigger database installations, the 
wraparound problems become more and more likely.  And it's likely to be at 
least six months before 7.2beta, if the track record stays the same as it has 
the last three releases.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Wed, 13 Jun 2001 10:54:48 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: Patch to warn about oid/xid wraparound
> On Tuesday 12 June 2001 19:33, Bruce Momjian wrote:
> > Here is a patch that will warn you during VACUUM when you are within 75%
> > of oid/xid wraparound.
> 
> > I will apply it if 7.2 has no solution to the wraparounds.
> 
> Is this something a potential 7.1.3 release might need?  I consider this a 
> bug workaround, actually.  Or even a feature patch against 7.1.x that people 
> can download if they think they need it?
> 
> As PostgreSQL gets used in bigger and bigger database installations, the 
> wraparound problems become more and more likely.  And it's likely to be at 
> least six months before 7.2beta, if the track record stays the same as it has 
> the last three releases.

Here is an new patch that updates the percentage display when it is run,
rather than just displaying 75%.  I had to do the computation using
floats to prevent overflow.

Again, I will put this in 7.2 if we have no solution for rollover.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.195
diff -c -r1.195 vacuum.c
*** src/backend/commands/vacuum.c	2001/05/25 15:45:32	1.195
--- src/backend/commands/vacuum.c	2001/06/13 18:46:01
***************
*** 159,166 ****
  static bool enough_space(VacPage vacpage, Size len);
  static void init_rusage(VacRUsage *ru0);
  static char *show_rusage(VacRUsage *ru0);
  
- 
  /*
   * Primary entry point for VACUUM and ANALYZE commands.
   */
--- 159,166 ----
  static bool enough_space(VacPage vacpage, Size len);
  static void init_rusage(VacRUsage *ru0);
  static char *show_rusage(VacRUsage *ru0);
+ static void check_limits(void);
  
  /*
   * Primary entry point for VACUUM and ANALYZE commands.
   */
***************
*** 236,241 ****
--- 236,242 ----
  
  	/* clean up */
  	vacuum_shutdown();
+ 	check_limits();
  }
  
  /*
***************
*** 2645,2648 ****
--- 2646,2674 ----
  			 (int) (ru1.tv.tv_usec - ru0->tv.tv_usec) / 10000);
  
  	return result;
+ }
+ 
+ /*
+  *	check if we are near OID or XID wraparound
+  */
+ static void check_limits(void)
+ {
+ 	Oid nextOid;
+ 
+ 	/* If we are 75% to the limit, warn the user */
+ 	if (GetCurrentTransactionId() >
+ 		~(TransactionId)0 - (~(TransactionId)0) / 4)
+ 		elog(NOTICE,"You are %.0f%% from the limit for transaction ids.\n"
+ 			"\t Dumping your databases, running initdb, and reloading will reset\n"
+ 			"\t the transaction id counter.",
+ 			GetCurrentTransactionId() - (float)(~(TransactionId)0) * 100);
+ 
+ 	/* If we are 75% to the limit, warn the user */
+ 	GetNewObjectId(&nextOid);
+ 	if (nextOid > ~(Oid)0 - (~(Oid)0) / 4)
+ 		elog(NOTICE,"You are %.0f%% from the limit for object ids.\n"
+ 			"\t If you are not using object ids as primary keys, dumping your\n"
+ 			"\t databases, running initdb, and reloading will reset\n"
+ 			"\t the oid counter.",
+ 			(float)nextOid / (float)~(Oid)0 * 100);
  }
Wed, 13 Jun 2001 15:56:06 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Patch to warn about oid/xid wraparound
Bruce Momjian writes:

> Here is an new patch that updates the percentage display when it is run,
> rather than just displaying 75%.  I had to do the computation using
> floats to prevent overflow.

You could use ldiv().

Also, UINT_MAX (for transaction id) and OID_MAX (for Oid) might be
preferred over ~0.

Btw., there is a typo here:

>     GetCurrentTransactionId() - (float)(~(TransactionId)0) * 100);

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Wed, 13 Jun 2001 22:35:26 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Patch to warn about oid/xid wraparound
> Bruce Momjian writes:
> 
> > Here is an new patch that updates the percentage display when it is run,
> > rather than just displaying 75%.  I had to do the computation using
> > floats to prevent overflow.


Sorry, I should have been doing this on patches list.

> You could use ldiv().

It is overflow that I am worried about:  curr/max * 100
I don't see how ldiv helps here.

> 
> Also, UINT_MAX (for transaction id) and OID_MAX (for Oid) might be
> preferred over ~0.

Done.  Patch attached.

> 
> Btw., there is a typo here:
> 
> >     GetCurrentTransactionId() - (float)(~(TransactionId)0) * 100);

Thanks.  Fixed.  I hadn't gotten to testing the transaction code yet,
just the oid test.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.195
diff -c -r1.195 vacuum.c
*** src/backend/commands/vacuum.c	2001/05/25 15:45:32	1.195
--- src/backend/commands/vacuum.c	2001/06/13 21:01:37
***************
*** 17,22 ****
--- 17,23 ----
  #include <fcntl.h>
  #include <unistd.h>
  #include <time.h>
+ #include <limits.h>
  #include <sys/time.h>
  #include <sys/types.h>
  #include <sys/file.h>
***************
*** 159,166 ****
  static bool enough_space(VacPage vacpage, Size len);
  static void init_rusage(VacRUsage *ru0);
  static char *show_rusage(VacRUsage *ru0);
  
- 
  /*
   * Primary entry point for VACUUM and ANALYZE commands.
   */
--- 160,167 ----
  static bool enough_space(VacPage vacpage, Size len);
  static void init_rusage(VacRUsage *ru0);
  static char *show_rusage(VacRUsage *ru0);
+ static void check_limits(void);
  
  /*
   * Primary entry point for VACUUM and ANALYZE commands.
   */
***************
*** 236,241 ****
--- 237,243 ----
  
  	/* clean up */
  	vacuum_shutdown();
+ 	check_limits();
  }
  
  /*
***************
*** 2645,2648 ****
--- 2647,2674 ----
  			 (int) (ru1.tv.tv_usec - ru0->tv.tv_usec) / 10000);
  
  	return result;
+ }
+ 
+ /*
+  *	check if we are near OID or XID wraparound
+  */
+ static void check_limits(void)
+ {
+ 	Oid nextOid;
+ 
+ 	/* If we are 75% to the limit, warn the user */
+ 	if (GetCurrentTransactionId() > UINT_MAX - UINT_MAX / 4)
+ 		elog(NOTICE,"You are %.0f%% toward the limit for transaction ids.\n"
+ 			"\t Dumping your databases, running initdb, and reloading will reset\n"
+ 			"\t the transaction id counter.",
+ 			GetCurrentTransactionId() / (float)UINT_MAX * 100);
+ 
+ 	/* If we are 75% to the limit, warn the user */
+ 	GetNewObjectId(&nextOid);
+ 	if (nextOid > OID_MAX - OID_MAX / 4)
+ 		elog(NOTICE,"You are %.0f%% toward the limit for object ids.\n"
+ 			"\t If you are not using object ids as primary keys, dumping your\n"
+ 			"\t databases, running initdb, and reloading will reset\n"
+ 			"\t the oid counter.",
+ 			(float)nextOid / OID_MAX * 100);
  }
Wed, 13 Jun 2001 17:04:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Patch to warn about oid/xid wraparound