Thread: 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

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/