Обсуждение: Re: [GENERAL] TOAST not working
On Sat, Jun 10, 2006 at 05:10:06PM -0400, Christopher Browne wrote: > Centuries ago, Nostradamus foresaw when listman@elkenserver.net (list_man) would write: > > Can someone tell me if I have to 'enable' TOAST on columns to have it > > kick in. According to my research, numeric data types are toastable. > > TOAST is only used on individual columns that exceed 8K in size. > > The only way you'll be TOASTing numeric columns is if they are > individually defined to occupy > 8192 bytes. Actually, it's BLCKSZ/4. From http://www.postgresql.org/docs/8.1/interactive/storage-toast.html: "The TOAST code is triggered only when a row value to be stored in a table is wider than BLCKSZ/4 bytes (normally 2Kb)." BTW, 'row value' seems a bit prone to confusion (could be interpreted as the row itself). It'd probably be better to say 'field'. Barring objections, I'll submit a patch. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes: > Actually, it's BLCKSZ/4. From > http://www.postgresql.org/docs/8.1/interactive/storage-toast.html: > "The TOAST code is triggered only when a row value to be stored in a > table is wider than BLCKSZ/4 bytes (normally 2Kb)." > BTW, 'row value' seems a bit prone to confusion (could be interpreted as > the row itself). It'd probably be better to say 'field'. No, because that would be wrong; the statement is correct as written. The toaster tries to do something about rows that are wider than BLCKSZ/4 (well, actually MaxTupleSize/4 ... try grepping the source for TOAST_TUPLE_THRESHOLD). The OP's problem is that he's got too dang many fields. A TOAST pointer is 20 bytes wide (on most machines, at least) so even if we toast every single field out-of-line, we can't support more than about 400 toastable fields in a row. The FAQ says Maximum number of columns in a table? 250-1600 depending on column types but the 1600 figure is for datatypes like int4 that only take 4 bytes anyway. The OP was trying to store numeric(11,2) fields. If I'm counting on my fingers correctly, such a value would occupy 16 bytes natively, which means that pushing it out-of-line would be a dead loss anyway. But he's still not going to get more than 512 of them into an 8K page. regards, tom lane
Dropping -general On Sat, Jun 10, 2006 at 10:58:49PM -0400, Tom Lane wrote: > "Jim C. Nasby" <jnasby@pervasive.com> writes: > > Actually, it's BLCKSZ/4. From > > http://www.postgresql.org/docs/8.1/interactive/storage-toast.html: > > > "The TOAST code is triggered only when a row value to be stored in a > > table is wider than BLCKSZ/4 bytes (normally 2Kb)." > > > BTW, 'row value' seems a bit prone to confusion (could be interpreted as > > the row itself). It'd probably be better to say 'field'. > > No, because that would be wrong; the statement is correct as written. > The toaster tries to do something about rows that are wider than > BLCKSZ/4 (well, actually MaxTupleSize/4 ... try grepping the source > for TOAST_TUPLE_THRESHOLD). I'll argue that the docs are still confusing though, since 'row value' could be interpreted as 'field', especially since the first paragraph contains "In release 7.1 and later, this limit is overcome by allowing large field values to be compressed and/or broken up into multiple physical rows." -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461