search

cybertec-postgresql / pgfaceting

Faceted query acceleration for PostgreSQL using roaring bitmaps
BSD 3-Clause "New" or "Revised" License
150 stars 10 forks source link

Integer out of range #1

Open rudibroekhuizen opened 1 year ago

rudibroekhuizen commented 1 year ago

Nice project! My id's have bigint as datatype, I get this error when I run the populate_facets function:

ERROR:  integer out of range
CONTEXT:  SQL statement "INSERT INTO explore.gbif_enriched_facets 
SELECT facet_id, (gbifid >> 20) chunk_id, facet_value collate "POSIX", rb_build_agg(gbifid::int4 ORDER BY gbifid)
FROM explore.gbif_enriched d,
    LATERAL (
        VALUES (1, date_trunc('decade', eventdate)::text),
               (2, recordedby::text),
               (3, preparations::text),
               (4, stateprovince::text),
               (5, lifestage::text)
    ) t(facet_id, facet_value)
GROUP BY facet_id, facet_value collate "POSIX", chunk_id
    "
PL/pgSQL function faceting.populate_facets(oid,boolean,boolean) line 23 at EXECUTE
SQL statement "SELECT faceting.populate_facets(v_table_id, false)"
PL/pgSQL function faceting.add_faceting_to_table(regclass,name,faceting.facet_definition[],text,boolean,boolean) line 60 at PERFORM
SQL state: 22003
ants commented 1 year ago

Added support for bigint typed columns in 90c9a83b8f3d3c16c39e7a1dd9ec9242ca3e7717. It will still cause out of range though for values > 2^51. However the roaringbitmap datatype is quite inefficient for sparse datatypes anyway so for globally unique id's youd want to maintain a denser sequentially assigned id column anyway. I will add a documentation example for that.