On Mon, Sep 28, 2020 at 04:42:39PM -0400, John Naylor wrote:
On Thu, Sep 24, 2020 at 7:50 PM Tomas Vondra
<tomas.von...@2ndquadrant.com> wrote:
On Thu, Sep 24, 2020 at 05:18:03PM -0400, John Naylor wrote:
>Hmm, how ugly would it be to change the default range size depending
>on the opclass?
>
Not sure. What would happen for multi-column BRIN indexes with different
opclasses?
Sounds like a can of worms. In any case I suspect if there is no more
graceful way to handle too-large filters than ERROR out the first time
trying to write to the index, this feature might meet some resistance.
Not sure what to suggest, though.
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"
I'm a bit puzzled, though, because both of these things seem to work:
1) insert before creating the index
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);
-- and there actually is a non-empty summary with real data
select * from brin_page_items(get_raw_page('x_a_b_c_idx', 2),
'x_a_b_c_idx'::regclass);
2) insert "small" row before inserting the over-sized one
create table x (a text, b text, c text);
insert into x select random_str(10), random_str(10), random_str(10);
insert into x select random_str(1000), random_str(1000), random_str(1000);
create index on x using brin (a,b,c);
-- and there actually is a non-empty summary with the "big" values
select * from brin_page_items(get_raw_page('x_a_b_c_idx', 2),
'x_a_b_c_idx'::regclass);
I find this somewhat strange - how come we don't fail here too?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
