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/