Date: Thu, 1 Oct 2020 20:41:33 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: John Naylor <john.naylor@2ndquadrant.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: WIP: BRIN multi-range indexes
Message-ID: <20201001184133.oq5uq75sb45pu3aw@development>
References: <20200918114014.sz5a3nwt7zont4rz@development>
 <CACPNZCvjt_Td2q3OqZdo_qZUjX_j+9Gotru1eEe4UVq02fLM+A@mail.gmail.com>
 <20200918222702.omsieaphfj3ctqg3@development>
 <CACPNZCsnUJjGbC6pMWj0CZF9w2rRcjjohX+6X2xU5+nQesiJ8w@mail.gmail.com>
 <20200921195607.gt6xbia7v55bhuwq@development>
 <CACPNZCvvq9W8tbE4PQbyPJyLXWnJCPTJ099LTWrbvb5NDs86qg@mail.gmail.com>
 <20200924235045.bgxsilvf7askukph@development>
 <CACPNZCtvdOjYvYMj2y1+gbVhy_3udJR8iJRo4zkQFg=NT3_9pA@mail.gmail.com>
 <20200929021228.bf2vtpl3negue4y4@development>
 <CACPNZCtz6v+hH_ES9hYoELTSGSfCb-VTUQwf3_9uwr7FkN+ZgA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: 
 <CACPNZCtz6v+hH_ES9hYoELTSGSfCb-VTUQwf3_9uwr7FkN+ZgA@mail.gmail.com>
Precedence: bulk

On Wed, Sep 30, 2020 at 07:57:19AM -0400, John Naylor wrote:
>On Mon, Sep 28, 2020 at 10:12 PM Tomas Vondra
><tomas.vondra@2ndquadrant.com> wrote:
>
>> Is it actually all that different from the existing BRIN indexes?
>> Consider this example:
>>
>> create table x (a text, b text, c text);
>>
>> create index on x using brin (a,b,c);
>>
>> create or replace function random_str(p_len int) returns text as $$
>> select string_agg(x, '') from (select chr(1 + (254 * random())::int ) as x from generate_series(1,$1)) foo;
>> $$ language sql;
>>
>> test=# insert into x select random_str(1000), random_str(1000), random_str(1000);
>> ERROR:  index row size 9056 exceeds maximum 8152 for index "x_a_b_c_idx"
>
>Hmm, okay. As for which comes first, insert or index creation, I'm
>baffled, too. I also would expect the example above would take up a
>bit over 6000 bytes, but not 9000.
>

OK, so this seems like a data corruption bug in BRIN, actually.

The ~9000 bytes is actually about right, because the strings are in
UTF-8 so roughly 1.5B per character seems about right. And we have 6
values to store (3 columns, min/max for each), so 6 * 1500 = 9000.

The real question is how come INSERT + CREATE INDEX actually manages to
create an index tuple. And the answer is pretty simple - brin_form_tuple
kinda ignores toasting, happily building index tuples where some values
are toasted.

Consider this:

     create table x (a text, b text, c text);
     insert into x select random_str(1000), random_str(1000), random_str(1000);

     create index on x using brin (a,b,c);
     delete from x;
     vacuum x;

     set enable_seqscan=off;

     insert into x select random_str(10), random_str(10), random_str(10);
     ERROR:  missing chunk number 0 for toast value 16530 in pg_toast_16525

     explain analyze select * from x where a = 'xxx';
     ERROR:  missing chunk number 0 for toast value 16530 in pg_toast_16525

     select * from brin_page_items(get_raw_page('x_a_b_c_idx', 2), 'x_a_b_c_idx'::regclass);
     ERROR:  missing chunk number 0 for toast value 16547 in pg_toast_16541


Interestingly enough, running the select before the insert seems to be
working - not sure why.

Anyway, it behaves like this since 9.5 :-(


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services