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

Re: [SQL] Problem with large tuples.

From: Dirk Lutzebaeck <lutzeb(at)aeccom(dot)com>
To: Patrik Kudo <kudo(at)partitur(dot)se>
Cc: "pgsql-sql(at)postgreSQL(dot)org" <pgsql-sql(at)postgreSQL(dot)org>
Subject: Re: [SQL] Problem with large tuples.
Date: 2000-01-21 15:38:37
Message-ID: 14472.32125.181522.797383@ampato.aeccom.com (view raw or flat)
Thread:
Lists: pgsql-sql
Patrik Kudo writes:
 > I've noticed that some of my tables with large text tuples have
 > problem when these exceed some certain size. I know about the
 > 8k tuplesize limit, but it seems the problem appears earlier
 > than that. This is what I've been able to recreate:
 > 
 > CREATE TABLE sizetest (id int, txt text);
 > INSERT INTO sizetest (id, txt) VALUES (1, '...'); // ... = string of
 > 4000 chars
 > vacuum analyze; // <-- works nicely
 > INSERT INTO sizetest (id, txt) VALUES (2, '...'); // ... = string of
 > 4100 chars
 > vacuum analyze;
 > ERROR:  Tuple is too big: size 8152, max size 8140
 > 
 > How come the insert works while the vacuum fails?

It's a bug. You need this patch against 6.5.3:

*** src/backend/commands/vacuum.c.orig	Wed Aug 25 08:01:45 1999
--- src/backend/commands/vacuum.c	Tue Jan  4 12:15:17 2000
***************
*** 2405,2414 ****
  					stup = heap_formtuple(sd->rd_att, values, nulls);
  
  					/* ----------------
! 					 *	insert the tuple in the relation and get the tuple's oid.
  					 * ----------------
  					 */
! 					heap_insert(sd, stup);
  					pfree(DatumGetPointer(values[3]));
  					pfree(DatumGetPointer(values[4]));
  					pfree(stup);
--- 2405,2425 ----
  					stup = heap_formtuple(sd->rd_att, values, nulls);
  
  					/* ----------------
! 					 *	Watch out for oversize tuple, which can happen if
! 					 *	both of the saved data values are long.
! 					 *	Our fallback strategy is just to not store the
! 					 *	pg_statistic tuple at all in that case.  (We could
! 					 *	replace the values by NULLs and still store the
! 					 *	numeric stats, but presently selfuncs.c couldn't
! 					 *	do anything useful with that case anyway.)
  					 * ----------------
  					 */
! 					if (MAXALIGN(stup->t_len) <= MaxTupleSize)
! 					{
! 						/* OK to store tuple */
! 						heap_insert(sd, stup);
! 					}
! 
  					pfree(DatumGetPointer(values[3]));
  					pfree(DatumGetPointer(values[4]));
  					pfree(stup);

After patching you should remove your statistics with
'DELETE FROM pg_statistic'; 

You might also need to patch vio.c. Get it from the latest snapshot...

Go back in the pgsql-bugs archive to read more about this problem. It
was discussed some weeks ago...

Dirk



In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2000-01-21 16:23:02
Subject: Re: [SQL] Duplicate tuples with unique index
Previous:From: Patrik KudoDate: 2000-01-21 15:09:33
Subject: Duplicate tuples with unique index

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