Some issues about data type convert

donniehan wrote:

I have a question about pg_cast--- data type convert.
Pg provide more data types than sql spec, such as OID.
Internal OID type is unsigned int32 and INT8 is int64.

Why pg can convert INT8 into OID implicitly while can not
convert OID into INT8 implicitly?

There may be some problems when using coalesce expr. Have a
look at the following case:

postgres=# create table test1(c1 OID, c2 BIGINT);
CREATE TABLE
postgres=# create view v1 as select coalesce(c1,c2) from test1;
CREATE VIEW
postgres=# \d v1
View "public.v1"
Column | Type | Modifiers
----------+------+-----------
coalesce | oid |
View definition:
SELECT COALESCE(test1.c1, test1.c2::oid) AS "coalesce"
FROM test1;

postgres=# insert into test1(c2) values(-1);
INSERT 0 1
postgres=# select * from v1;
ERROR: OID out of range

Although we can define the view v1 successfully, but we can
not get what we want.
If pg can convert INT8 into OID implicitly, it seems there
would not be any problems.

This has nothing to do with implicit or explicit casts.

The maximum possible oid is 4294967295, and you are trying to create a
bigger one.

The problem is that since "oid" is unsigned, negative integers will be
interpreted as large positive values when you cast them.

Compare:

test=> SELECT oid(-1);
oid
------------
4294967295
(1 row)

or

test=> SELECT oid(int4(-1));
oid
------------
4294967295
(1 row)

In both cases there is a conversion from 4-byte integer to oid
(which also has 4 bytes). "-1" becomes the maximum unsigned
4-byte integer value.

If you try the same with bigint = int8, you get

test=> SELECT oid(int8(-1));
ERROR: OID out of range

The corresponding unsigned 8-byte integer values would be
18446744073709551615, and when you try to store that in
an "oid", you get an overflow error.

Why do you want a view where "-1" is converted to an oid?

Yours,
Laurenz Albe

On Mon, Jan 04, 2010 at 03:55:15PM +0100, Albe Laurenz wrote:

donniehan wrote:

postgres=# create table test1(c1 OID, c2 BIGINT);
postgres=# create view v1 as select coalesce(c1,c2) from test1;
postgres=# \d v1
SELECT COALESCE(test1.c1, test1.c2::oid) AS "coalesce"
FROM test1;

Although we can define the view v1 successfully, but we can
not get what we want.
If pg can convert INT8 into OID implicitly, it seems there
would not be any problems.

This has nothing to do with implicit or explicit casts.

Um, I think the OP is right. Notice he does:

create view v1 as select coalesce(c1,c2) from test1;

which PG interprets as:

SELECT COALESCE(test1.c1, test1.c2::oid) AS "coalesce" FROM test1;

showing that PG is auto-magically inserting a cast from BIGINT to OID.
The OP is saying that this isn't right because the cast isn't a total
function, i.e. it fails for -1 and a large number of other inputs.

I think the OP is either expecting the CREATE VIEW to fail saying types
are invalid, or to result in "c1" being cast to BIGINT.

--
Sam http://samason.me.uk/

Sam Mason <sam@samason.me.uk> writes:

Um, I think the OP is right. Notice he does: ...
showing that PG is auto-magically inserting a cast from BIGINT to OID.

Yes, as a quick look into pg_cast will show you, bigint -> oid is an
implicit cast:

regression=# \dC+ oid
List of casts
Source type | Target type | Function | Implicit?
---------------+---------------+--------------------+---------------
bigint | oid | oid | yes
integer | oid | (binary coercible) | yes
oid | bigint | int8 | in assignment
oid | integer | (binary coercible) | in assignment
...

The reason for this is that we want to be able to accept constants like
3000000000 as OIDs, but the lexer will initially mark that as bigint
because it doesn't fit in int4.

The OP is saying that this isn't right because the cast isn't a total
function, i.e. it fails for -1 and a large number of other inputs.

Sometimes usefulness has to trump consistency :-(. It could actually be
argued that this is a consistency issue in itself. Given a function
that takes OID, such as pg_database_size(oid), it would be unpleasant
if pg_database_size(16384) worked but pg_database_size(3000000000)
did not.

regards, tom lane

Um, I think the OP is right. Notice he does: ...
showing that PG is auto-magically inserting a cast from BIGINT to OID.
I think the OP is either expecting the CREATE VIEW to fail saying types
are invalid, or to result in "c1" being cast to BIGINT

Yes, that's what i mean. Is that more reasonable cast OID to BIGINT?

Yes, as a quick look into pg_cast will show you, bigint -> oid is an
implicit cast:

regression=# \dC+ oid
List of casts
Source type | Target type | Function | Implicit?
---------------+---------------+--------------------+---------------
bigint | oid | oid | yes
integer | oid | (binary coercible) | yes
oid | bigint | int8 | in assignment
oid | integer | (binary coercible) | in assignment
...

The reason for this is that we want to be able to accept constants like
3000000000 as OIDs, but the lexer will initially mark that as bigint
because it doesn't fit in int4.

The OP is saying that this isn't right because the cast isn't a total
function, i.e. it fails for -1 and a large number of other inputs.

Sometimes usefulness has to trump consistency :-(. It could actually be
argued that this is a consistency issue in itself. Given a function
that takes OID, such as pg_database_size(oid), it would be unpleasant
if pg_database_size(16384) worked but pg_database_size(3000000000)
did not.

Can i just resolve this consistency issue by update pg_cast set the CASTCONTEXT = 'i'(implicit) where cast OID to BIGINT ?
I'm not sure whether this change would cause other problems?

For coalesce expression, it does not conform with sql specification, and why not?
And also in pg there is no Data Type Precedence concept(I saw there is Preferred Data Type)? Why does not pg support it ?
Data Type Precedence help cast data type to more common type, it will be very useful for like coalesce expressions.
For examples:
Let VARCHAR is higher than CHAR
So when coalesce(CHAR(20), VARCHAR(10)), the return type will be VARCHAR(20) while now is CHAR(8000).

Let INT8 is higher than OID
So when coalesce(INT8, OID), the return type will be INT8, while now is OID. (The precondition is supporting cast OID to INT8 implicitly).

Just my two cents for your information.

Regards
--Dongni

On Mon, Jan 04, 2010 at 12:45:00PM -0500, Tom Lane wrote:

Sam Mason <sam@samason.me.uk> writes:

Um, I think the OP is right. Notice he does: ...
showing that PG is auto-magically inserting a cast from BIGINT to OID.

Yes, as a quick look into pg_cast will show you, bigint -> oid is an
implicit cast:

Oops, I should have pointed this out to the OP!

The reason for this is that we want to be able to accept constants like
3000000000 as OIDs, but the lexer will initially mark that as bigint
because it doesn't fit in int4.

What's wrong with getting users to put quotes around OIDs? Backwards
compatibility seems like an awkward one to maintain when doing this.

The OP is saying that this isn't right because the cast isn't a total
function, i.e. it fails for -1 and a large number of other inputs.

Sometimes usefulness has to trump consistency :-(.

One technical solution I've seen used elsewhere is to introduce the
concept of a "numeric literal". For example, Haskell's parser knows
about both "numbers" and "fractional numbers" and types can be declared
as supporting either numeric constants or both numeric and fractional
constants. This would seem to complicate the simple type inference that
PG does, but not too badly.

It could actually be
argued that this is a consistency issue in itself. Given a function
that takes OID, such as pg_database_size(oid), it would be unpleasant
if pg_database_size(16384) worked but pg_database_size(3000000000)
did not.

Not sure what you're saying here, but I don't think it matters.

--
Sam http://samason.me.uk/