On 2014-05-17 23:46:39 +0300, Heikki Linnakangas wrote:
> On 05/17/2014 11:12 PM, Tom Lane wrote:
> >I wrote:
> >>BTW, the *real* problem with all this stuff is that the gbtreekeyNN types
> >>are declared as having int alignment, even though some of the opclasses
> >>store double-aligned types in them. I imagine it's possible to provoke
> >>bus errors on machines that are picky about alignment. The first column
> >>of an index is safe enough because index tuples will be double-aligned
> >>anyway, but it seems like there's a hazard for lower-order columns.
> >
> >And indeed there is:
> >
> >regression=# create extension btree_gist ;
> >CREATE EXTENSION
> >regression=# create table tt (f1 int2, f2 float8);
> >CREATE TABLE
> >regression=# create index on tt using gist(f1,f2);
> >CREATE INDEX
> >regression=# insert into tt values(1,2);
> >INSERT 0 1
> >regression=# insert into tt values(2,4);
> >INSERT 0 1
> >regression=# set enable_seqscan TO 0;
> >SET
> >regression=# explain select * from tt where f1=2::int2 and f2=4;
> > QUERY PLAN
> >------------------------------------------------------------------------
> > Index Scan using tt_f1_f2_idx on tt (cost=0.14..8.16 rows=1 width=10)
> > Index Cond: ((f1 = 2::smallint) AND (f2 = 4::double precision))
> > Planning time: 1.043 ms
> >(3 rows)
> >
> >regression=# select * from tt where f1=2::int2 and f2=4;
> ><< bus error >>
> >
> >>This is something we cannot fix compatibly :-(
Too bad.
> >Another issue is what the heck btree_gist's extension upgrade script ought
> >to do about this. It can't just go and modify the type declarations.
> >
> >Actually, on further thought, this isn't an issue for pg_upgrade at all,
> >just for the extension upgrade script. Maybe we just have to make it
> >poke through the catalogs looking for at-risk indexes, and refuse to
> >complete the extension upgrade if there are any?
Hm. I guess it could just add a C function to do that job. I think
pg_upgrade already has some.
> I think it would be best to just not allow pg_upgrade if there are any
> indexes using the ill-defined types. The upgrade script could then simply
> drop the types and re-create them. The DBA would need to drop the affected
> indexes before upgrade and re-create them afterwards, but I think that would
> be acceptable.
> I doubt there are many people using btree_gist on int8 or float8
> columns.
I don't think it's that unlikely. It can make a fair amount of sense to
have multicolumn indexes where one columns is over an int8 and the other
over the datatype requiring the gist index to be used. I've certainly
done that.
Greetings,
Andres Freund
-- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services