| 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: | Whole Thread | Raw Message | 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
| 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 |