Re: [PATCHES] [BUGS] BUG #2704: pg_class.relchecks overflow problem

[Toru SHIMOGAKI]

How about this patch?

Of course, it might be a rare case that such check is necessary...


Toru SHIMOGAKI wrote:
> The following bug has been logged online:
> 
> Bug reference:  2704
> Logged by:      Toru SHIMOGAKI
> Email address:  [EMAIL PROTECTED]
> PostgreSQL version: 8.1.4
> Operating system:   Red Hat Enterprise Linux AS 4
> Description:pg_class.relchecks overflow problem
> Details: 
> 
> Hi, 
> 
> pg_class.relchecks is defined as int2. But the upper bound of this value is
> not checked and it overflows.
> 
> 
> I found it at the following case:
> 
> 1. I tried to add check constraints:
> 
> "alter table test_a add check (aaa > i);" (0 <= i <= 32767)
> 
> 
> 2. When I added the 32768th check constraint, the value of pg_class.relchecs
> became -32768.
> 
> postgres=# alter table test_a add check ( aaa > 32768 );
> ALTER TABLE
> postgres=# select relname, relchecks from pg_class where relname =
> 'test_a';
> relname | relchecks
> -+---
> test_a | -32768
> (1 row)
> 
> 
> 3. The following error message was found when I added the next one:
> 
> postgres=# alter table test_a add check ( aaa > 32769 );
> ERROR: unexpected constraint record found for rel test_a
> postgres=# select relname, relchecks from pg_class where relname =
> 'test_a';
> relname | relchecks
> -+---
> test_a | -32768
> (1 row)
> 
> 
> Best regards,
> 
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
> 
> 

-- 
Toru SHIMOGAKI<[EMAIL PROTECTED]>
diff -cpr postgresql-8.1.5-orig/src/backend/catalog/heap.c 
postgresql-8.1.5/src/backend/catalog/heap.c
*** postgresql-8.1.5-orig/src/backend/catalog/heap.c2006-04-24 
10:40:39.0 +0900
--- postgresql-8.1.5/src/backend/catalog/heap.c 2006-10-23 16:50:22.0 
+0900
*** AddRelationRawConstraints(Relation rel,
*** 1525,1530 
--- 1525,1535 
continue;
Assert(cdef->cooked_expr == NULL);
  
+   if (numchecks == 0x7FFF)
+   ereport(ERROR,
+   
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("cannot have more than 2^15-1 checks in a 
table")));
+ 
/*
 * Transform raw parsetree to executable expression.
 */

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] [PATCHES] [BUGS] BUG #2704: pg_class.relchecks overflow

[Toru SHIMOGAKI]

Tom Lane wrote:
> While there's not anything wrong with this proposed patch in itself,
> I have to admit that I don't see the point.  

The points are:
1. It is just unpleasant to leave the overflow.
2. It is not easy for users to understand what they should do when they
encounter the error message. At least users can't understand that it is because
of the upper limit:

 ERROR: unexpected constraint record found for rel test_a


I haven't found such a case in real practice. But I think the limit will be a
little closer than that is now, because constraint exclusion is expanded for
UPDATE/DELETE in 8.2 and the opportunity of using check constraint will increase
 . So I investigated the upper limit and found it.

By the way, as you said, it would impose an extra burden on message translators
and I can understand your opinion. But will it lead directly to the reason that
we don't fix it?

Best regards,

-- 
Toru SHIMOGAKI<[EMAIL PROTECTED]>


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

[PATCHES] doc patch for Linux memory overcommit

[Toru SHIMOGAKI]

The attached is a doc patch for Linux memory overcommit and an additional way of
avoiding a problem that postmaster is suddenly killed by OOM-Killer.

http://archives.postgresql.org/pgsql-docs/2007-03/msg0.php

Best regards,

-- 
Toru SHIMOGAKI<[EMAIL PROTECTED]>
*** runtime.sgml.orig   2007-03-06 16:23:10.0 +0900
--- runtime.sgml2007-03-06 17:14:38.0 +0900
***
*** 1207,1215 
 
  
 
! On Linux 2.6 and later, a better solution is to modify the kernel's
! behavior so that it will not overcommit memory.  This is
! done by selecting strict overcommit mode via sysctl:
  
  sysctl -w vm.overcommit_memory=2
  
--- 1207,1224 
 
  
 
! In addition, increasing swap area on OS can avoid the problem too.
! Out-of-Memory-Killer(OOM-Killer) is invoked whenever physical memory and 
! swap area are exhausted. Increasing swap area is easy to set and it 
! doesn't have harmful influence.
!
! 
!
! On Linux 2.6 and later, a better solution is to modify the kernel's 
! behavior so that it will not overcommit memory.  Though this 
! setting can't prevent OOM-Killer from invoking directly, we can expect 
! healty memory allocation. This is done by selecting strict overcommit 
! mode via sysctl:
  
  sysctl -w vm.overcommit_memory=2
  

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq