Thread: werid error"index row size 3040 exceeds btree maximum, 2712" occur randomly

werid error"index row size 3040 exceeds btree maximum, 2712" occur randomly

From
"Huang, Suya"
Date:

Hi,

 

OK, first, I know the reason of this error “index row size 3040 exceeds btree maximum, 2712” and know that we cannot create index on certain columns with size larger than 1/3 buffer page size.

 

The question is, no matter if I deleted records that caused the problem or all records of the table, the error still occurred and would disappear after a while randomly, like 1 or 2 minutes or so.

 

Therefore I suspect if this is a bug or any postgresql internal mechanism I was not aware would lead to this problem?

 

See my test as below:

 

pgdb=# drop table test;

DROP TABLE

pgdb=# create table test as select * from tbl_weekly;

SELECT

 

pgdb=# create index idx_test on test USING btree (term) WITH (fillfactor=100) WHERE catid = 1;

ERROR:  index row size 3040 exceeds btree maximum, 2712

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.

 

 

--because this is a TOAST table, so the size should be determined by pg_column_size(), not octet_length()

 

pgdb=# select length(term), pg_column_size(term),octet_length(term),catid from test  where length(term)>=2000 order  by 1;

length | pg_column_size | octet_length | catid

--------+----------------+--------------+-------

   2088 |           1430 |         2088 |    80

   2088 |           1430 |         2088 |   125

   2088 |           1430 |         2088 |     1

   2190 |           1450 |         2190 |    50

   2190 |           1450 |         2190 |     1

   2190 |           1450 |         2190 |    30

   2205 |           1184 |         2205 |    80

   2205 |           1184 |         2205 |     1

   2205 |           1184 |         2205 |   100

   2586 |           1894 |         2586 |   100

   2586 |           1894 |         2586 |    80

   2586 |           1894 |         2586 |   320

   2586 |           1894 |         2586 |     1

   5179 |           3028 |         5179 |     1

   5179 |           3028 |         5179 |   125

   5179 |           3028 |         5179 |    80

(16 rows)

 

--so the fix is to delete records with pg_column_size>2700, in this case, to delete records with pg_column_size=3028 (length=5179) and catid=1.

pgdb=# delete from test where  length(term) =5179 and catid=1;

DELETE 1

 

pgdb=# select length(term), pg_column_size(term),octet_length(term),catid from test  where length(term)>=2000 order by 1;

length | pg_column_size | octet_length | catid

--------+----------------+--------------+-------

   2088 |           1430 |         2088 |    80

   2088 |           1430 |         2088 |     1

   2088 |           1430 |         2088 |   125

   2190 |           1450 |         2190 |     1

   2190 |           1450 |         2190 |    30

   2190 |           1450 |         2190 |    50

   2205 |           1184 |         2205 |    80

   2205 |           1184 |         2205 |     1

   2205 |           1184 |         2205 |   100

   2586 |           1894 |         2586 |    80

   2586 |           1894 |         2586 |   320

   2586 |           1894 |         2586 |   100

   2586 |           1894 |         2586 |     1

   5179 |           3028 |         5179 |   125

   5179 |           3028 |         5179 |    80

(15 rows)

 

 

--even deleted the record that cause the problem, the index creation SQL fail again

 

pgdb=#  create index idx_test on test USING btree (term) WITH (fillfactor=100) WHERE catid = 1;

ERROR:  index row size 3040 exceeds btree maximum, 2712

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.

pgdb=# vacuum full analyze test;

VACUUM

pgdb=#  create index idx_test on test USING btree (term) WITH (fillfactor=100) WHERE catid = 1;

ERROR:  index row size 3040 exceeds btree maximum, 2712

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.

 

-- After a while, run index creation sql again, it succeeded.

pgdb=# create index idx_test on test USING btree (term) WITH (fillfactor=100) WHERE catid = 1;

CREATE INDEX

Re: werid error"index row size 3040 exceeds btree maximum, 2712" occur randomly

From
Tomas Vondra
Date:
Hi,

On 14.10.2013 05:47, Huang, Suya wrote:
> Hi,
>
> OK, first, I know the reason of this error “index row size 3040
> exceeds btree maximum, 2712” and know that we cannot create index on
> certain columns with size larger than 1/3 buffer page size.
>
> The question is, no matter if I deleted records that caused the
> problem or all records of the table, the error still occurred and
> would disappear after a while randomly, like 1 or 2 minutes or so.

I'd bet what you see is a caused by MVCC. The deleted records are not
deleted immediately, but marked as deleted and then eventually freed by
(auto)vacuum background process, once no other sessions need them.

But those records need to be indexed, as other sessions may still need
to access them (depending on the transaction isolation level used), so
we can't just skip them when creating the index.

See this for more details on this topic:
http://www.postgresql.org/docs/9.3/static/transaction-iso.html

Try running VACUUM on the table before creating the index, and make sure
there are no other connections accessing the table. That should do the
trick.

That being said, I wonder why you need to create a gin index on such
long values. Any particular reason why you decided not to use a MD5 hash
of the value, as suggested by the HINT message?

regards
Tomas


Re: werid error"index row size 3040 exceeds btree maximum, 2712" occur randomly

From
"Huang, Suya"
Date:
Thanks Tomas!

However, in the example I sent,  I already did a vacuum full right after deleted the rows causing problem, before
createdthe index and got an error even the table is vacuumed. Note, the table is I temporarily created using create
tableas select *... so no other people is accessing that table, except me for the testing purpose. 

Any ideas? And today, while I did the same thing, I can create index on the table right after I deleted the rows
causingproblem, without vacuum.  

Anything I missed here?

Thanks,
Suya

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tomas Vondra
Sent: Tuesday, October 15, 2013 7:09 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] werid error"index row size 3040 exceeds btree maximum, 2712" occur randomly

Hi,

On 14.10.2013 05:47, Huang, Suya wrote:
> Hi,
>
> OK, first, I know the reason of this error "index row size 3040
> exceeds btree maximum, 2712" and know that we cannot create index on
> certain columns with size larger than 1/3 buffer page size.
>
> The question is, no matter if I deleted records that caused the
> problem or all records of the table, the error still occurred and
> would disappear after a while randomly, like 1 or 2 minutes or so.

I'd bet what you see is a caused by MVCC. The deleted records are not deleted immediately, but marked as deleted and
theneventually freed by (auto)vacuum background process, once no other sessions need them. 

But those records need to be indexed, as other sessions may still need to access them (depending on the transaction
isolationlevel used), so we can't just skip them when creating the index. 

See this for more details on this topic:
http://www.postgresql.org/docs/9.3/static/transaction-iso.html

Try running VACUUM on the table before creating the index, and make sure there are no other connections accessing the
table.That should do the trick. 

That being said, I wonder why you need to create a gin index on such long values. Any particular reason why you decided
notto use a MD5 hash of the value, as suggested by the HINT message? 

regards
Tomas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: werid error"index row size 3040 exceeds btree maximum, 2712" occur randomly

From
Tomas Vondra
Date:
On 15.10.2013 03:44, Huang, Suya wrote:
> Thanks Tomas!
>
> However, in the example I sent,  I already did a vacuum full right
> after deleted the rows causing problem, before created the index and
> got an error even the table is vacuumed. Note, the table is I
> temporarily created using create table as select *... so no other
> people is accessing that table, except me for the testing purpose.
>
> Any ideas? And today, while I did the same thing, I can create index
> on the table right after I deleted the rows causing problem, without
> vacuum.
>
> Anything I missed here?

Not sure. The only thing I can think of is another transaction
preventing the autovacuum from removing the rows, but I can't reproduce
it. What PostgreSQL version are you using?

BTW take a look at this contrib module:

   http://www.postgresql.org/docs/9.1/static/pageinspect.html

It allows looking directly on items on a data page like this:

   select * from heap_page_items(get_raw_page('test', 0));

Maybe that'll help you identify the problem.

Tomas


Re: werid error"index row size 3040 exceeds btree maximum, 2712" occur randomly

From
"Huang, Suya"
Date:
Thanks Tomas!

I'll spend some time on the link you sent, new learner of Postgres :-)

Thanks,
Suya

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tomas Vondra
Sent: Wednesday, October 16, 2013 9:06 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] werid error"index row size 3040 exceeds btree maximum, 2712" occur randomly

On 15.10.2013 03:44, Huang, Suya wrote:
> Thanks Tomas!
>
> However, in the example I sent,  I already did a vacuum full right
> after deleted the rows causing problem, before created the index and
> got an error even the table is vacuumed. Note, the table is I
> temporarily created using create table as select *... so no other
> people is accessing that table, except me for the testing purpose.
>
> Any ideas? And today, while I did the same thing, I can create index
> on the table right after I deleted the rows causing problem, without
> vacuum.
>
> Anything I missed here?

Not sure. The only thing I can think of is another transaction preventing the autovacuum from removing the rows, but I
can'treproduce it. What PostgreSQL version are you using? 

BTW take a look at this contrib module:

   http://www.postgresql.org/docs/9.1/static/pageinspect.html

It allows looking directly on items on a data page like this:

   select * from heap_page_items(get_raw_page('test', 0));

Maybe that'll help you identify the problem.

Tomas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general