geography btree: ERROR: index row requires 9960 bytes, maximum size is 8191

[strk]

When trying to create an index on a geography column I get this message:

strk=# create index idx_cshapeswdatevalid_geog on  cshapeswdatevalid (geog);
ERROR:  index row requires 9960 bytes, maximum size is 8191

This is with PostgreSQL 8.4.10 on x86_64-pc-linux-gnu, 64bit and PostGIS 2.0.0beta2SVN r9419.

The dataset is the one attached to ticket #1679 with the "geog" column just created using alter table and update.

[strk]

I think my command is actually trying to produce a binary tree

[strk]

Yup, a GiST index works:

strk=# create index idx_cshapeswdatevalid_geog on  cshapeswdatevalid using gist (geog);
CREATE INDEX

Well, I guess urgency reduces, but it's still a fact you can't create a btree on geography (what should it do is a different topic)

[pramsey]

Same thing will happen in geometry, no? Indeed, it does

cded=# create index roads_gg on roads (geom);
ERROR:  index row requires 19328 bytes, maximum size is 8191

[strk]

This is weird, aren't we supposed to be using BBOX based indexing for btree ? How can it get so big ? May there be an hidden bug here ?

[strk]

r9476 erroneously points to this ticket instead of #1679 (sorry for that)

[pramsey]

It's a long-standing bug if that's the case. Since btree can handle varchar, it should be able to handle geometry, but I guess our implementation is missing some magic sauce. Maybe re-open and punt forward.