Potential BRIN Index Corruption

[Huan Ruan]

Hi All

We have a table with 623 million records. It appears a BRIN index of this
table on a timestamp column is missing some records, as illustrated below
in a cut-down version with additional columns and indices omitted.

We cannot work out a reproducible case but we have a copy of the offending
database. I was hoping to know

   1. if anyone else has experienced similar issues
   2. if anyone can shed some light on what to collect in order to fire a
   useful bug report

Version

   - centos-release-7-7.1908.0.el7.centos.x86_64
   - PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
   20150623 (Red Hat 4.8.5-39), 64-bit


Table DDL
CREATE TABLE large_table_with_623m_records (
  date_with_btree_index date,
  ts_with_brin_index timestamp without time zone not null
);

CREATE INDEX date_bree_index ON large_table_with_623m_records
  USING btree (date_with_btree_index COLLATE pg_catalog."default"
, date_with_btree_index);

CREATE INDEX ts_brin_index ON large_table_with_623m_records
  USING brin (ts_with_brin_index);


Query
SELECT
  *
FROM large_table_with_623m_records
WHERE
  ts_with_brin_index >= '2018-06-29 12:12:50' AND ts_with_brin_index <
'2018-06-29 12:13:00'
  AND date_with_btree_index = '2013-05-21'

This query uses Index Scan on date_bree_index and correctly returns 1
record that has ts_with_brin_index = '2018-06-29 12:12:58:081'.

If I remove the last line (AND date_with_btree_index = '2013-05-21'), the
query uses Bitmap Index Scan on ts_brin_index and incorrectly returns 0
record.

After a reindex of ts_brin_index, both variations of the query correctly
return 1 record.

Thanks
Huan

Re: Potential BRIN Index Corruption

[Huan Ruan]

>
> Thanks Alvaro for pointing me to pageinspect. I will give it a try and
> report back. It might take a few days. I knew this module but have never
> tried it before.
>

Re: Potential BRIN Index Corruption

[Huan Ruan]

HI Alvaro

Unfortunately those records were written a while ago and we no longer keep
their WAL logs. Thanks for your help anyway.

Huan

On Fri, 27 Nov 2020 at 08:40, Huan Ruan  wrote:

> Thanks Alvaro for pointing me to pageinspect. I will give it a try and
>> report back. It might take a few days. I knew this module but have never
>> tried it before.
>>
>

Re: Potential BRIN Index Corruption

[Huan Ruan]

Hi Tomas

The records have a timestamp column so we do know the time they were
written. We didn't find any I/O issues that match that time but
unfortunately as it's been a while we are not confident with that finding.

>  Are there any other corrupted indexes on the table?

That was one of my first questions too. I don't see any physical errors in
pg log so not sure if there are other corruptions. One thing we consider
doing is to turn on checksums.

Regards
Huan

PITR and Temp Tables

[Huan Ruan]

Hi All

Let's say at T0 a database has N session based temp tables. They would have
corresponding records in the catalog tables like pg_class and pg_attribute
that are visible to other sessions.

At T1, I do a PITR to T0. That recovered database should not have those
temp tables because the sessions they were created in are not present. My
question is what events trigger the deletion of those temp tables' catalog
records (e.g. pg_class and pg_attribute etc.) in the recovered database?

Thanks
Huan

Re: PITR and Temp Tables

[Huan Ruan]

Hi Patrick

Thanks for your reply.

>
>
> My guess is that temp table entries will still be in your catalog until
> you do a VACUUM FULL of the pg_class / pg_attribute tables.
>
> But you should not care about these entries if these tables are vacuumed
> at regular intervals.
>
What I observed in one instance seems* to be like this - by the time I
connected to the recovered database (as soon as I could), an autovacuuum
has run on those catalog tables and they don't have those temp table
entries. Normally, autovacuum removes dead tuples, but those temp table
records are live tuples at T0. So if it was autovacuum that removed them in
the recovered database, were they live or dead tuples? If they are dead,
what did the deletion that made them dead?

*I would like to confirm to be 100% sure but was wondering if I can get an
explanation here.

Regards
Huan

Re: PITR and Temp Tables

[Huan Ruan]

Many thanks for the explanation, Tom and Greg. That all makes sense.

Cheers
Huan