Hi all,

I have sent a patch to implement tinyint, an unsigned one byte integer, 
for MS SQL compatibility. The replies I got were under two categories. 
One was "our type system is complicated enough", and the other was "it 
should be signed and we should have a general "unsigned" keyword.

Now, I'm not against neither simplifying the type system nor having a 
"unsigned" keyword. The thing is that between these two remarks, my 
patch was not applied and I don't know why.

I need this new type because the OLE DB provider I'm writing (for a 
client) works in binary mode by-definition, which means that the 
client's software, currently designed to work with MS SQL, relies on 
certain table entries to be one byte wide. Tricks such as using "char" 
don't work, as it uses "insert ... from select... ", which fails because 
the numbers are not translateable to "char". It also does explicit 
"insert", which also will not work with "char".

I would really love to know what needs to be done in order to get a one 
byte type, either signed or not (not really important at this stage) 
into postgres.

If this is impossible, would it be at least possible to reserve an OID 
for this type, and decide what it actually is later? If that would be 
possible, I can go on, in the mean time, with my development. The 
problem is that pglib really has no way of identifying the types except 
by OID.

             Shachar

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


Mon, 15 Mar 2004 23:58:54 +0200
Shachar Shemesh <psql@shemesh.biz>
tinyint and type problems
Shachar Shemesh <psql@shemesh.biz> writes:
> I have sent a patch to implement tinyint, an unsigned one byte integer, 
> ...
> Now, I'm not against neither simplifying the type system nor having a 
> "unsigned" keyword. The thing is that between these two remarks, my 
> patch was not applied and I don't know why.

> 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.  There wasn't any part of
your patch that couldn't have been implemented like a contrib module
(i.e., a dynamically loaded library for the C functions plus a SQL
script to install the catalog entries).

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?" ... where the size of the maintenance load depends
quite a lot on how tightly bound the new type is to the rest of the
system.  A new type that is part of the numeric hierarchy is going to be
way up there on the scale of maintenance burden.  For instance, people
would expect it to have at least as much functionality as int2 does,
such as indexing support, and so we'd be buying into (sooner or later)
doing a lot of work that your patch left unfinished.

On top of that there are some concerns about locking ourselves into
supporting a definition we might not like later.  Most obviously there
is the signed/unsigned issue, but that's not the only risk.  We are still
tweaking the numeric hierarchy to try to get the interrelationships of
the types just right.  Until we are really satisfied that that task
is done, I'm not excited about adding more functionality that might look
like a big backwards-compatibility problem later.

So the bottom line for me is that the potential downsides outweigh
the upside.  You haven't made a good enough case for the general
usefulness of this datatype.

			regards, tom lane

Mon, 15 Mar 2004 17:51:47 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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/


Tue, 16 Mar 2004 09:43:48 +0200
Shachar Shemesh <psql@shemesh.biz>
Re: tinyint and type problems
Shachar Shemesh wrote:
> 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.

Sorry for jumping into this thread so late, but if you don't really need 
  arithmetic operators, index support, and casts to all other numeric 
types, why not use "char"?

Joe

Tue, 16 Mar 2004 09:24:40 -0800
Joe Conway <mail@joeconway.com>
Re: tinyint and type problems
Joe Conway wrote:

> Shachar Shemesh wrote:
>
>> 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.
>
>
> Sorry for jumping into this thread so late, but if you don't really 
> need  arithmetic operators, index support, and casts to all other 
> numeric types, why not use "char"?
>
> Joe

On a totally unrelated issue - how are the quotes around "char" 
enforced? I have not seen anything special about it's definition.

Hi Joe,

Thanks for the offer. I actually tried to use "char" before embarking on 
the quest, but it didn't work out. While my specific client (and I 
realize that others do need it) don't need many of the above, they do 
need some of it. As such, the lack of ability to type "insert into foo 
(char_col) values (5)" meant I couldn't use it.

In any case, I now have (already commited to the OLE DB sources) a 
solution to this problem. The provider has a list of special types it 
supports. When first establishing a connection to the database, I query 
the pg_type table for all of those types, and cache their OIDs. When I 
need to perform parsing, I look for all the OIDs I know in advance using 
a "switch", and in the default clause, look for all the OIDs of the non 
preknown types. It's a reasonable solution, I think.

I would still love it if the definitions for PG_GETARG_UINT8, 
PG_RETURN_UINT8 and DatumGetInt8 made it into the main tree, as I don't 
think there is any real reason to define them in my so. That aside, 
however, my specific problem is solved. I also renamed the type 
"utinyint", to leave room for a signed 1 byte value if we decide to go 
that route. 
(http://gborg.postgresql.org/project/oledb/cvs/co.php/oledb/PgSess.cpp?r=1.1).

As far as I'm concerned, the discussion just turned academic. I'll still 
love to add the missing feature to get this type in, as I do think it's 
generally useful. My implementation, however, currently works over a 
vanilla postgres without a problem.

             Shachar

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


Wed, 17 Mar 2004 00:05:37 +0200
Shachar Shemesh <psql@shemesh.biz>
Re: tinyint and type problems