Обсуждение: BUG #12292: index row size 1480 exceeds maximum 1352 for index

The following bug has been logged on the website:

Bug reference:      12292
Logged by:          robert thaler
Email address:      robert.thaler@cellent.at
PostgreSQL version: 9.4.0
Operating system:   linux ubuntu 14.04.1 LTS x86_64
Description:

I tried to upgrade from postgres 9.3.5 and imported a database export
created by pg_dump. the import shows the following error:
ERROR: index row size 1480 exceeds
maximum 1352 for index "idx_sm_post_content"

manually creating the gin index for fulltext search does not work too and
produces the same error.

create index idx_sm_post_content
on client.sm_post using gin (to_tsvector('english', "content"))

on postgres 9.3.5 creating the index on the same data works fine.

kind regards

robert.thaler@cellent.at writes:
> I tried to upgrade from postgres 9.3.5 and imported a database export
> created by pg_dump. the import shows the following error:
> ERROR: index row size 1480 exceeds
> maximum 1352 for index "idx_sm_post_content"

> manually creating the gin index for fulltext search does not work too and
> produces the same error.

> create index idx_sm_post_content
> on client.sm_post using gin (to_tsvector('english', "content"))

> on postgres 9.3.5 creating the index on the same data works fine.

Hm, can you provide sample data that triggers this?  The GIN code
looks like it's supposed to avoid this limit at higher logic levels,
but evidently that's dropping the ball somewhere.  Hard to tell where
without a concrete example though.

            regards, tom lane

robert.thaler@cellent.at writes:
> I tried to upgrade from postgres 9.3.5 and imported a database export
> created by pg_dump. the import shows the following error:
> ERROR: index row size 1480 exceeds
> maximum 1352 for index "idx_sm_post_content"

I've looked into this (thanks to Robert for the test data), and the short
answer is that commit 36a35c55 approximately halved GinMaxItemSize:

 #define GinMaxItemSize \
-    MAXALIGN_DOWN(((BLCKSZ - SizeOfPageHeaderData - \
-        MAXALIGN(sizeof(GinPageOpaqueData))) / 3 - sizeof(ItemIdData)))
+    Min(INDEX_SIZE_MASK, \
+        MAXALIGN_DOWN(((BLCKSZ - SizeOfPageHeaderData -                    \
+                        MAXALIGN(sizeof(GinPageOpaqueData))) / 6 - sizeof(ItemIdData))))

What was the rationale for deciding that GIN has to be able to fit six
tuples per page???  This is going to create serious dump/reload hazards
for a lot of users.

            regards, tom lane

On Thu, Dec 25, 2014 at 1:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> robert.thaler@cellent.at writes:
>> I tried to upgrade from postgres 9.3.5 and imported a database export
>> created by pg_dump. the import shows the following error:
>> ERROR: index row size 1480 exceeds
>> maximum 1352 for index "idx_sm_post_content"
>
> I've looked into this (thanks to Robert for the test data), and the short
> answer is that commit 36a35c55 approximately halved GinMaxItemSize:
>
>  #define GinMaxItemSize \
> -    MAXALIGN_DOWN(((BLCKSZ - SizeOfPageHeaderData - \
> -        MAXALIGN(sizeof(GinPageOpaqueData))) / 3 - sizeof(ItemIdData)))
> +    Min(INDEX_SIZE_MASK, \
> +        MAXALIGN_DOWN(((BLCKSZ - SizeOfPageHeaderData -                    \
> +                        MAXALIGN(sizeof(GinPageOpaqueData))) / 6 - sizeof(ItemIdData))))
>
> What was the rationale for deciding that GIN has to be able to fit six
> tuples per page???  This is going to create serious dump/reload hazards
> for a lot of users.
Not completely related, but I just recalled seeing the same
limitations with jsonb:
http://www.postgresql.org/message-id/CAB7nPqT8OYHCPKU4nMYdqa_xZH1+8JFbtP=B+kjk6RZU5zGxGg@mail.gmail.com
--
Michael

On 12/24/2014 06:44 PM, Tom Lane wrote:
> robert.thaler@cellent.at writes:
>> I tried to upgrade from postgres 9.3.5 and imported a database export
>> created by pg_dump. the import shows the following error:
>> ERROR: index row size 1480 exceeds
>> maximum 1352 for index "idx_sm_post_content"
>
> I've looked into this (thanks to Robert for the test data), and the short
> answer is that commit 36a35c55 approximately halved GinMaxItemSize:
>
>   #define GinMaxItemSize \
> -    MAXALIGN_DOWN(((BLCKSZ - SizeOfPageHeaderData - \
> -        MAXALIGN(sizeof(GinPageOpaqueData))) / 3 - sizeof(ItemIdData)))
> +    Min(INDEX_SIZE_MASK, \
> +        MAXALIGN_DOWN(((BLCKSZ - SizeOfPageHeaderData -                    \
> +                        MAXALIGN(sizeof(GinPageOpaqueData))) / 6 - sizeof(ItemIdData))))
>
> What was the rationale for deciding that GIN has to be able to fit six
> tuples per page???  This is going to create serious dump/reload hazards
> for a lot of users.

Oh. I went to look at the discussions on this patch, and that change was
present already in the very early versions that were posted around. I
don't know the reason for that, and I failed to catch it before
committing; it certainly looks bogus.

I'll revert that, and add a comment above GinMaxItemSize explaining that
we need to fit at least 3 items on each page. If such a comment had been
in place, I'm sure someone would've caught this earlier.

Actually, I think we would only need to fit 2 items on each page in GIN.
In b-tree, we need to fit three: the high key, and two data keys. But in
the GIN entry tree, we don't store high keys explicitly, we just use the
rightmost key on the page. That works because we never delete items from
the entry tree. (I don't dare to change the above to /2, nevertheless)

- Heikki