Thread: 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@gmail.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/