Re: tinyint and type problems

Hi Tom,

Tom Lane wrote:

>>I need this new type because ...
>>    
>>
>
>Um, the reason we have an extensible type system is so that people can
>make their own datatypes.  You don't have to get a type accepted into
>the base system in order to use it yourself.
>  
>
...

>The criterion for adding new types to the base system is really "is this
>of sufficient general usefulness to justify taking on a permanent
>maintenance load?"
>
Discussions raising excellent points on why the burden is higher than 
the rewards snipped.

Ok, I'll explain why I didn't go the external datatype to begin with, 
and then suggest a compromise for discussion.

There is one difference between builtin datatypes and external datatypes 
that is usually unimportant, but is making my case extremely difficult. 
Builtin types have guarenteed OID number. This makes knowing what you 
got through switch...case over the return type of |PQftype that much 
easier. I can probably work around it by doing a select over pg_types, 
but I'm afraid of the performance penalties. If left with no choice, 
that's what I'll do.|

Since this is not some wierd type that noone has ever heard of, but a 
type that is, under one variation or another, available in any database 
system, I though it would be generally useful and thus wrote a patch for 
it to the main database.

The way I see it, there are three options. I'll list them in the order I 
prefer them to happen, and I ask that you let me know which one the 
group thinks is best.
Choice 1 - I implement the missing features my previous patch did not 
take care of. These include arithmetic operators, index support, and 
casts to all other numeric types (something which MS SQL, by the way, 
makes do without. There is no cast from tinyint to int8 there, but so be 
it). I actually suggested this on the patches list before, but I asked 
that an indication be given that these are the only restraints from 
putting the type in, so that my time is not spent in vain. Since I got 
no reply there, I started this thread here.

Choice 2 - We put in just the macros for manipulating 8 bit parameters 
from my patch, and do one more thing. I suggest that PostgreSQL should 
have, in one form or another, a one byte numerical value, and so all I 
ask is that we decide now what OID it should have. I can then go on with 
implementing everything else as an external type, and everybody is 
happy. When our type system improves, we can then resolve the other 
questions (signed unsigned etc.), and I can pull my external type.

Choice 3 - We do nothing of the above. I put everything into an external 
type lib, and work around my OLE DB driver using additional queries. I 
don't like this option because it means that PostgreSQL's OLE DB driver 
will have reduced performance.

I'll stress again - I don't mind doing all the work associated with any 
once of the above choices. All I'm asking is that we agree on which one 
will be best for this project. As far as I'm concerned, Choice 2 
involves the least amount of work, but I think Choice 1 will serve 
everyone better.

As for the signness/unsigness dillema - I found a table at 
http://www.theopensourcery.com/sqldatatypes.htm. It compares MS SQL, 
Oracle, Postgres and MySQL. Of the four, MS SQL and MySql have one byte 
int. In MS SQL, it is unsigned, while the entire rest of the type system 
is signed. In MySQL, all numbers are signed unless prepended with an 
"unsigned" modifier.

Personally, I don't think the MySQL system is worth the trouble. This is 
a database system, not a C programming language. People who need the 
extra range can use the NUMBER type. I also don't see the use for a 
signed 1 byte type - its range is too small in both direction.
            Shachar

-- 
Shachar Shemesh
Lingnu Open Systems Consulting
http://www.lingnu.com/