int24_ops and int42_ops are bogus
От | Tom Lane |
---|---|
Тема | int24_ops and int42_ops are bogus |
Дата | |
Msg-id | 28999.961374238@sss.pgh.pa.us обсуждение исходный текст |
Ответы |
Re: int24_ops and int42_ops are bogus
(Bruce Momjian <pgman@candle.pha.pa.us>)
Re: int24_ops and int42_ops are bogus (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
Our documentation claims (eg in the CREATE INDEX ref page) that : The int24_ops operator class is useful for constructing indices on int2 : data, and doing comparisons against int4 data in query : qualifications. Similarly, int42_ops support indices on int4 data that : is to be compared against int2 data in queries. But as far as I can tell, it is not actually possible for these opclasses to work as claimed, and never has been. The reason is that there is only one set of associated operators for an opclass. To have an opclass that works as suggested above, you would need *two* sets of operators identified for the opclass. For example, in the case of int24_ops, you'd need to point at both of: 1. int2 vs. int4 operators (eg, int24lt) --- the planner must see these in order to know that an "int2 < int4" WHERE clausehas any relevance to the index. 2. int2 vs. int2 operators (eg, int2lt) --- the index access method itself needs these for internal operations on the index,such as comparing a new datum to the ones already in the index for insertion. Currently we only reference the first set of operators, which means that internal operations are wrong for these opclasses. Thus, for example: create table foo (f1 int4); create unique index foo42i on foo (f1 int42_ops); insert into foo values(65537); insert into foo values(1); ERROR: Cannot insert a duplicate key into unique index foo42i In the case of btree operations it's barely possible that we could get around this by using the three-way comparison support procedure (int2cmp or int4cmp in these cases) for *all* internal comparisons in the index, and being careful to use the amop operators --- the right way round! --- for all comparisons to external values. The btree code is not that careful now, and I'm not sure it can be made that careful; it's not clear that the low-level operations can tell whether the key they are working with is an about-to-be-inserted value (same type as the index entries) or a comparison key (not same type as the index entries). Even if we could make it work, it'd be horribly fragile in the face of future code changes --- people are just too used to assuming that "a < b" and "b > a" are equivalent ways of coding a test. And we don't have any way of automatically checking the code, given that all these values are Datum as far as the compiler knows. I think we ought to assume that index manipulation deals with only one datatype for any given index, and therefore these two opclasses are broken by design and must be removed. Comments? regards, tom lane
В списке pgsql-hackers по дате отправления:
Предыдущее
От: "Hiroshi Inoue"Дата:
Сообщение: RE: OK, OK, Hiroshi's right: use a seperately-generated filename