'[postgis-users] Bug in BRIN support for PostGIS datatypes'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    [postgis-users] Bug in BRIN support for PostGIS datatypes
From:       Giuseppe Broccolo <giuseppe.broccolo () 2ndquadrant ! it>
Date:       2016-11-01 21:03:42
Message-ID: CAFzmHiUc2p+zxH99PgA5AsbC8hx2QDQ47miLKZXF511_a7552A () mail ! gmail ! com
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]

Hi,

We have just found a bug involving the recently added BRIN support for
PostGIS datatype.

When new entries are added in an already indexed field, the stored bounding
box is updated
if necessary, but the change is not then logged into the WALs: the
resulting in memory updated
index continue to work properly and there are no effects or reported
errors, but it is not crash safe,
and results to be corrupted both on a master server and on its relative
standbys. The index
is neither restorable also through PITR from a physical backup.

Below it is reported a simple example showing the bug effects:

=# CREATE TABLE bench(id integer, geom geometry);
CREATE TABLE
=# CREATE INDEX ON bench USING brin(geom);
CREATE INDEX
=# INSERT INTO bench SELECT i, ST_MakeBox2D(ST_MakePoint(i, i),
ST_MakePoint(i+0.1, i+0.1))
-# FROM generate_series(1, 3) AS i;
INSERT 2 2
=# SET enable_seqscan TO on;
SET
=# SET enable_bitmapscan TO off;
SET
=# SELECT count(*) FROM bench WHERE ST_Contains('BOX(2 2, 3000
3000)'::box2d, geom);

  count
 --------
     2
(1 row)

=# SET enable_seqscan TO off;
SET
=# SET enable_bitmapscan TO on;
SET
=# SELECT count(*) FROM bench WHERE ST_Contains('BOX(2 2, 3000
3000)'::box2d, geom);

  count
 --------
     0
(1 row)

=# SELECT value FROM brin_page_items(get_raw_page('bench_geom_idx', 2),
'bench_geom_idx');

                                     value
 --------------------------------------------------------------------
 {BOX2DF(1 1, 1.10000002384 1.10000002384) .. f .. f}
(1 row)

So the in memory index has the bounding box correctly setup with regards to
the initial
data, but records inserted after cannot be queried using the index.

A patch containing the fix has already been submitted to the PostGIS dev
team, that should then
been released with the next updates of PostGIS.
In the meantime, since the bug should not affect just-initiated indexes,
the workaround is to recreate
a new index everytime new entries are added in the indexed fields (consider
that BRINs can be
quickly rebuilt).

Regards,
Giuseppe, Julien & Ronan.

-- 
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it

[Attachment #5 (text/html)]

<div dir="ltr">Hi,<br><br>We have just found a bug involving the recently added BRIN \
support for PostGIS datatype.<br><br>When new entries are added in an already indexed \
field, the stored bounding box is updated<br>if necessary, but the change is not then \
logged into the WALs: the resulting in memory updated<br>index continue to work \
properly and there are no effects or reported errors, but it is not crash \
safe,<br>and results to be corrupted both on a master server and on its relative \
standbys. The index<br>is neither restorable also through PITR from a physical \
backup.<br><br>Below it is reported a simple example showing the bug \
effects:<br><br>=# CREATE TABLE bench(id integer, geom geometry);<br>CREATE \
TABLE<br>=# CREATE INDEX ON bench USING brin(geom);<br>CREATE INDEX<br>=# INSERT INTO \
bench SELECT i, ST_MakeBox2D(ST_MakePoint(i, i), ST_MakePoint(i+0.1, i+0.1))<br>-# \
FROM generate_series(1, 3) AS i;<br>INSERT 2 2<br>=# SET enable_seqscan TO \
on;<br>SET<br>=# SET enable_bitmapscan TO off;<br>SET<br>=# SELECT count(*) FROM \
bench WHERE ST_Contains(&#39;BOX(2 2, 3000 3000)&#39;::box2d, geom);<br><br>   \
count<br>  --------<br>         2<br>(1 row)<br><br>=# SET enable_seqscan TO \
off;<br>SET<br>=# SET enable_bitmapscan TO on;<br>SET<br>=# SELECT count(*) FROM \
bench WHERE ST_Contains(&#39;BOX(2 2, 3000 3000)&#39;::box2d, geom);<br><br>   \
count<br>  --------<br>         0<br>(1 row)<br><br>=# SELECT value FROM \
brin_page_items(get_raw_page(&#39;<wbr>bench_geom_idx&#39;, 2), \
&#39;bench_geom_idx&#39;);<br><br>                                                    \
<wbr>             value<br>  \
-----------------------------<wbr>------------------------------<wbr>---------<span \
class="gmail-im"><br>  {BOX2DF(1 1, 1.10000002384 1.10000002384) .. f .. \
f}<br></span>(1 row)<br><br>So the in memory index has the bounding box correctly \
setup with regards to the initial <br>data, but records inserted after cannot be \
queried using the index.<br><br>A patch containing the fix has already been submitted \
to the PostGIS dev team, that should then <br>been released with the next updates of \
PostGIS. <br>In the meantime, since the bug should not affect just-initiated indexes, \
the workaround is to recreate <br>a new index everytime new entries are added in the \
indexed fields (consider that BRINs can be <br>quickly \
rebuilt).<br><br>Regards,<br>Giuseppe, Julien &amp; Ronan.<br><br>-- <br><div \
class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><span></span>Giuseppe \
Broccolo - 2ndQuadrant Italy <br>PostgreSQL &amp; PostGIS Training, Services and \
Support <br><a href="mailto:giuseppe.broccolo@2ndQuadrant.it" \
target="_blank">giuseppe.broccolo@2ndQuadrant.it</a> | <a \
href="http://www.2ndQuadrant.it" target="_blank">www.2ndQuadrant.it</a> \
</div></div></div></div> </div>

[Attachment #6 (text/plain)]

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread] 