BRIN index and aborted transaction

From: Tatsuo Ishii <ishii(at)postgresql(dot)org>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: BRIN index and aborted transaction
Date: 2015-07-18 00:21:26
Message-ID: 20150718.092126.1195663014858338389.t-ishii@sraoss.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Forgive me if this has been already discussed somewhere.

When a transaction aborts, it seems a BRIN index leaves summary data
which is not valid any more. Is this an expected behavior? I guess
the answer is yes, because it does not affect correctness of a query
result, but I would like to make sure.

Second question is when the wrong summary data is gone? It seems
vacuum does not help. Do I have to recreate the index (or reindex)?

test=# begin;
BEGIN
test=# insert into t1 values(1000001);
INSERT 0 1
test=# SELECT * FROM brin_page_items(get_raw_page('brinidx', 2),'brinidx');
itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value
------------+--------+--------+----------+----------+-------------+---------------------
1 | 0 | 1 | f | f | f | {1 .. 28928}
2 | 128 | 1 | f | f | f | {28929 .. 57856}
3 | 256 | 1 | f | f | f | {57857 .. 86784}
[snip]
34 | 4224 | 1 | f | f | f | {954625 .. 983552}
35 | 4352 | 1 | f | f | f | {983553 .. 1000001}
(35 rows)

test=# abort;
ROLLBACK
test=# SELECT * FROM brin_page_items(get_raw_page('brinidx', 2),'brinidx');
itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value
------------+--------+--------+----------+----------+-------------+---------------------
1 | 0 | 1 | f | f | f | {1 .. 28928}
2 | 128 | 1 | f | f | f | {28929 .. 57856}
3 | 256 | 1 | f | f | f | {57857 .. 86784}
[snip]
34 | 4224 | 1 | f | f | f | {954625 .. 983552}
35 | 4352 | 1 | f | f | f | {983553 .. 1000001}
(35 rows)

test=# vacuum t1;
VACUUM
test=# SELECT * FROM brin_page_items(get_raw_page('brinidx', 2),'brinidx');
itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value
------------+--------+--------+----------+----------+-------------+---------------------
1 | 0 | 1 | f | f | f | {1 .. 28928}
2 | 128 | 1 | f | f | f | {28929 .. 57856}
3 | 256 | 1 | f | f | f | {57857 .. 86784}
[snip]
33 | 4096 | 1 | f | f | f | {925697 .. 954624}
34 | 4224 | 1 | f | f | f | {954625 .. 983552}
35 | 4352 | 1 | f | f | f | {983553 .. 1000001}
(35 rows)

test=# select max(i) from t1;
max
---------
1000000
(1 row)
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2015-07-18 00:29:59 Re: pg_resetsysid
Previous Message Jim Nasby 2015-07-18 00:03:48 Re: Support retrieving value from any sequence