Re: Extensible mechanism for type promotion / demotion - Mailing list pgsql-hackers

I believe that the basis for such a mechanism should be a model of the
semantic type inheritance for primitive data types.  Note that type
inheritance is a completely different concept than representation inheritance,
as witnessed by the confusion over the now implemented proposal to correct the
semantics of table inheritance.

Logically, a sub-type expresses the idea that any instance of the sub-type is
also an instance of the super-type.

For example, semantically speaking, a smallint is an integer because the set
of all small integers is a subset of the set of all integers.

We could represent this fact with something like a the pg_inherits table with
entries for conversion functions to convert the canonical representation of
the 
sub-type into the canonical representation of the super-type and vice versa.

In a normal implementation, the index scan boundary values should be stored
internally using the representation of the lowest common super-type.  That way
you can get a correct result for queries like(*):
 select * from table where smallint_column < 100000

Alternatively, the query engine could internally down cast the value to be
compared to the index column type extended with flags like the following:

COMPATIBLE_VALUE_GREATER   - value is comparable and always greater than                            any instance of
columntype
 
COMPATIBLE_VALUE_LESS      - value is comparable and always less than                            any instance of column
type
INCOMPATIBLE_VALUE         - value is not comparable to column type

The type down-conversion function would need to clear the resulting value and
set the appropriate flag if the conversion does not succeed.

The flags would then be used to calculate which index scan boundary values are
equivalent to the original query predicate by substituting the maximum and
minimum allowed values of the column type as appropriate.

I have not looked at the source code in detail yet, but I believe the basic
idea is sound.
- Mark Butler


Note:  Oracle avoids this whole problem for numeric types by using a common
variable precision format for *all* numbers.  The nice thing is that you can
increase the precision / scale of any numeric column without touching the data
in each row.

Thomas Lockhart wrote:
> 
> Hmm. The problem is as you describe, but the requirements for a solution
> are more severe than you (or I) would hope.
> 
> We would like to have an extensible mechanism for type promotion and
> demotion, but it is not (yet) clear how to implement it. In this case,
> we must demote a constant assigned as "int4" by the parser into an
> "int2" to be directly comparable to the indexed column. We could
> probably do this with some hack code as a brute-force exercise, but no
> one has yet bothered (patches welcome ;) But in general, we must handle
> the case that the specified constraint is *not* directly convertible to
> the indexed type (e.g. is out of range) even though this would seem to
> reduce to a choice between a trivial noop or a sequential scan of the
> entire table. If we can do this without cluttering up the code too much,
> we should go ahead and do it, but it has apparently been a low priority.
> 
>                          - Thomas