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

[Giuseppe Broccolo]

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 at 2ndQuadrant.it | www.2ndQuadrant.it
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161101/e190eb14/attachment.html>