Re: BUG #17926: Segfault in SELECT

> Op 9 mei 2023, om 10:38 heeft Daniel Gustafsson <daniel@yesql.se> het volgende geschreven:
>
>> On 8 May 2023, at 22:12, PG Bug reporting form <noreply@postgresql.org> wrote:
>
>> The query uses a 3 partitions of a table where all fields have an brin
>> index.
>
> One index per column or a multi-colum index?

One index per column.  However, if  I add a multicolumn index (on ts and source) it will not crash.

>
>> This query will segfault:
>> SELECT count(*)
>> FROM dw
>> WHERE (dw.ts >= '2022-09-01' AND dw.ts <= '2022-10-30')
>> AND dw.source='type3' AND dw.customer='123.456';
>>
>> The query should return 0 because the customer does not exist.
>> Removing the dw.customer or dw.source constraint will make the segfault not
>> occur.
>> Also using 'set enable_bitmapscan to off' will not trigger the segfault.
>
> I was unable to reproduce this, can you share the table schema with the index
> creations (or a version of the schema which reproduce the problem).
>

create table if not exists dw (
    activity_id bigserial,
    activity text not null,
    ts timestamptz not null,
    customer text,
    impact int default 0,
    feature_1 text,
    feature_2 text,
    feature_3 jsonb,
    link text,
    source text,
    source_id text,
    environment text
) partition by range(ts);

CREATE INDEX IF NOT EXISTS index_activity ON dw USING brin(activity);
CREATE INDEX IF NOT EXISTS index_customer ON dw USING brin(customer);
CREATE INDEX IF NOT EXISTS index_source ON dw USING brin(source);
CREATE INDEX IF NOT EXISTS index_source_id ON dw USING brin(source_id);
CREATE INDEX IF NOT EXISTS index_ts ON dw USING brin(ts);
CREATE INDEX IF NOT EXISTS index_environment ON dw USING brin(environment);

> Have you tried recreating the indexes, in case there is corruption, or tried it
> on a table which has the same schema but no index?

It still segfaults after recreating the indexes, Without index no segfault.

>
> --
> Daniel Gustafsson
>
>

Erik.