ERROR: index row requires 9984 bytes, maximum size is 8191

Hi all -

I have added a column to a table is which of datatype text. I am
trying to create an index

CREATE OR REPLACE FUNCTION reverse(varchar) RETURNS varchar AS $$
$reversed = reverse $_[0];
return $reversed;
$$ LANGUAGE plperlu IMMUTABLE;

CREATE INDEX rev_email ON users( (reverse(email) ) varchar_pattern_ops );

I am getting the following error ERROR: index row requires 9984
bytes, maximum size is 8191

my logs are not showing any thing else , but the error. Can you please help?

Regards

In response to akp geek <akpgeek(at)gmail(dot)com>:

> Hi all -
>
> I have added a column to a table is which of datatype text. I am
> trying to create an index
>
> CREATE OR REPLACE FUNCTION reverse(varchar) RETURNS varchar AS $$
> $reversed = reverse $_[0];
> return $reversed;
> $$ LANGUAGE plperlu IMMUTABLE;
>
>
> CREATE INDEX rev_email ON users( (reverse(email) ) varchar_pattern_ops );
>
>
> I am getting the following error ERROR: index row requires 9984
> bytes, maximum size is 8191
>
>
> my logs are not showing any thing else , but the error. Can you please help?

Indexes have a maximum size. I don't think there's much overhead on
a varchar index, so it would seem that your email column has at least
one value that is about 9984 bytes long. That doesn't seem like a
valid email address to me.

Could also be an error with your function, although it looks simple
enough.

What is the output of
SELECT MAX(octet_length(email)) FROM users;
and
SELECT MAX(octet_length(reverse(email))) FROM users;

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/