Thread: Values larger than 1/3 of a buffer page cannot be indexed.

hi,

when trying to insert a long-long value, I get the following error:

 index row size 3120 exceeds maximum 2712 for index "ir_translation_ltns"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

is there a way to generate this recommended function index of an md5 hash on an already existing database and tables (filled with data)?

thanks,
Viktor

Hey Viktor,




--
// Dmitriy.

Brian Hirt wrote:
> On Mar 13, 2011, at 12:05 PM, Dmitriy Igrishin wrote:
>
>> Hey Viktor,
>>
>> 2011/3/13 Viktor Nagy <viktor.nagy@toolpart.hu
>> <mailto:viktor.nagy@toolpart.hu>>
>>
>>     hi,
>>
>>     when trying to insert a long-long value, I get the following error:
>>
>>      index row size 3120 exceeds maximum 2712 for index
>>     "ir_translation_ltns"
>>     HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
>>     Consider a function index of an MD5 hash of the value, or use
>>     full text indexing.
>>
>>     is there a way to generate this recommended function index of an
>>     md5 hash on an already existing database and tables (filled with
>>     data)?
>>
>> Just create index this way, e.g.
>> CREATE INDEX ir_translation_ltns ON tab ((md5(col)));
>> where "tab" and "col" are table and column of which you want
>> to create btree index.
>
> This probably goes without saying, but you'll have to use col =
> md5('blahblahblahblah') in your qualifiers to get the benefit of the
> index.
>
> --brian

Unless the point is to guarantee uniqueness of the "long-long value"s.

On Sun, Mar 13, 2011 at 4:37 PM, Rob Sargent <robjsargent@gmail.com> wrote:
>
>
> Brian Hirt wrote:
>>
>> On Mar 13, 2011, at 12:05 PM, Dmitriy Igrishin wrote:
>>
>>> Hey Viktor,
>>>
>>> 2011/3/13 Viktor Nagy <viktor.nagy@toolpart.hu
>>> <mailto:viktor.nagy@toolpart.hu>>
>>>
>>>    hi,
>>>
>>>    when trying to insert a long-long value, I get the following error:
>>>
>>>     index row size 3120 exceeds maximum 2712 for index
>>>    "ir_translation_ltns"
>>>    HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
>>>    Consider a function index of an MD5 hash of the value, or use
>>>    full text indexing.
>>>
>>>    is there a way to generate this recommended function index of an
>>>    md5 hash on an already existing database and tables (filled with
>>>    data)?
>>>
>>> Just create index this way, e.g.
>>> CREATE INDEX ir_translation_ltns ON tab ((md5(col)));
>>> where "tab" and "col" are table and column of which you want
>>> to create btree index.
>>
>> This probably goes without saying, but you'll have to use col =
>> md5('blahblahblahblah') in your qualifiers to get the benefit of the index.
>>
>> --brian
>
> Unless the point is to guarantee uniqueness of the "long-long value"s.

md5 will do that too: the main thing you lose going to hash indexing
is ordering.

merlin

thanks, this worked.

On 03/14/2011 09:25 PM, Merlin Moncure wrote:

>> Unless the point is to guarantee uniqueness of the "long-long value"s.
>
> md5 will do that too: the main thing you lose going to hash indexing
> is ordering.

MD5 will *probably* guarantee the uniqueness of the values. Personally
I'm not a big fan of betting on that, and tend to like to test for
equality against the hash first and then, if the hashes are equal,
against the values.

I have several md5 collisions in one of my tables, so it's far from
impossible. In fact, it's much more likely than you'd expect thanks to
the birthday paradox.

--
Craig Ringer

On Mon, Mar 28, 2011 at 10:28 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 03/14/2011 09:25 PM, Merlin Moncure wrote:
>
>>> Unless the point is to guarantee uniqueness of the "long-long value"s.
>>
>> md5 will do that too: the main thing you lose going to hash indexing
>> is ordering.
>
> MD5 will *probably* guarantee the uniqueness of the values. Personally I'm
> not a big fan of betting on that, and tend to like to test for equality
> against the hash first and then, if the hashes are equal, against the
> values.
>
> I have several md5 collisions in one of my tables, so it's far from
> impossible. In fact, it's much more likely than you'd expect thanks to the
> birthday paradox.

I'll agree that 128 bit key is just on the cusp of discomfort if you
have a large data set and/or want to be really safe from random
collision, but any larger key and it's just worry for the sake of
worrying.

You mentioned the birthday attack -- let's consult the chart.  For MD5
(128 bits), if you are ok with 10^-15 chance of *one* collision (odds
1 in 1,000,000,000,000,000), you can safely hash up to 100,000,000,000
unique values.  Of course, md5 is not perfectly distributing so the
numbers are not in fact that good,  but those are pretty tall odds.
If you are concerned about safety, you are much better off jumping to
sha-1 (or sha-256 if you're really nervous)  than comparing the base
string, since your chance of random bump is now well below a number of
other low probability events, such as a drive bit error rate (meaning,
your base string comparison is useless) or getting hit by a comet.

Now, if you're worried about intentionally forged data, that's
different, but it doesn't sound like that's the case here.  When NIST
hash competition resolves (my money is on skein), you'll likely be
able to to just pick an output size that matches your requirements.

merlin