Bug in BRIN support for PostGIS datatypes

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email] | www.2ndQuadrant.it

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users