Re: oid as long type

What's the reason of using int rather than long for the large object oid? I am experiencing a problem where the database server is generating oids exceeding the size for a Java int, and got "org.postgresql.util.PSQLException: Bad value for type int". Could this be solve by making org.postgresql.largeobject.LargeObject.oid a long type and make corresponding changes to the source tree?

(for example in org.postgresql.jdbc3.Jdbc3ResultSet.getBlob(int), instead of:

return new Jdbc3Blob(connection, getInt(i));

do:

return new Jdbc3Blob(connection, getLong(i));)

I am not familar with the internals of the driver, so not sure if there are other complications. Any advice on using long oids would be much appreciated.

Thanks in advance!

Ryan

O Ryan Li έγραψε στις Feb 8, 2006 :

> What's the reason of using int rather than long for the large object oid?

Just a side quiestion,
why not use the much friendlier bytea?

--
-Achilleus

On Wed, 8 Feb 2006, Ryan Li wrote:

> What's the reason of using int rather than long for the large object
> oid? I am experiencing a problem where the database server is generating
> oids exceeding the size for a Java int, and got
> "org.postgresql.util.PSQLException: Bad value for type int". Could this
> be solve by making org.postgresql.largeobject.LargeObject.oid a long
> type and make corresponding changes to the source tree?
>

The use of int is a historical artifact, but the problem is that we can't
change the API without requiring clients to make code changes for some
methods. We could add duplicate method signatures that take long for the
oid types, but the problem is methods like LargeObject.getOID() and
LargeObjectManager.create() return int and these cannot be changed to long
without adjustments to the calling code. Additionally changes would be
required to the fastpath infrastructure to send and receive longs which
would be complicated by the fact that oids are really unsigned int4, not
int8.

So this is a known problem, but one we've generally avoided dealing with
because few people actually bump into it. I suppose we could add the new
method signatures and add new methods like getLongOID and only bail out
when code called the old ones with values to be to be represented by an
int. I'll put it on my todo list, but it's not real close to the top.

Kris Jurka

On Wed, 8 Feb 2006, Achilleus Mantzios wrote:

> Just a side quiestion,
> why not use the much friendlier bytea?
>

Because it's impossible to stream bytea data from the server to the client
using bytea is infeasible for large amounts of data.

Kris Jurka

Hi, Kris,

Kris Jurka wrote:

> The use of int is a historical artifact, but the problem is that we
> can't change the API without requiring clients to make code changes for
> some methods. We could add duplicate method signatures that take long
> for the oid types, but the problem is methods like LargeObject.getOID()
> and LargeObjectManager.create() return int and these cannot be changed
> to long without adjustments to the calling code. Additionally changes
> would be required to the fastpath infrastructure to send and receive
> longs which would be complicated by the fact that oids are really
> unsigned int4, not int8.
>
> So this is a known problem, but one we've generally avoided dealing with
> because few people actually bump into it. I suppose we could add the
> new method signatures and add new methods like getLongOID and only bail
> out when code called the old ones with values to be to be represented by
> an int. I'll put it on my todo list, but it's not real close to the top.

Maybe it would be possible to map it bit-wise to a singned int, so we
have negative OIDs on java side?

Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

To maintain compatibility with existing code but still give access to
the entire OID value space, why not map OID values >= 2**31 to negative
numbers-- i.e. just consider them to be signed ints?

This is an awful hack, yes, but it might be better to see a strange-
looking OID but still be able to use it as a valid unique identifier
than to get a SQLException with no easy workaround.

In the long run it would probably be better to convert to longs, though.
Perhaps the migration pain could be mitigated by deprecating
LargeObject.getOID() in favor of LargeObject.getLongOID() or similar, so
at least new clients could work the right way.

-- Mark Lewis

On Wed, 2006-02-08 at 12:17 -0500, Kris Jurka wrote:
>
> On Wed, 8 Feb 2006, Ryan Li wrote:
>
> > What's the reason of using int rather than long for the large object
> > oid? I am experiencing a problem where the database server is generating
> > oids exceeding the size for a Java int, and got
> > "org.postgresql.util.PSQLException: Bad value for type int". Could this
> > be solve by making org.postgresql.largeobject.LargeObject.oid a long
> > type and make corresponding changes to the source tree?
> >
>
> The use of int is a historical artifact, but the problem is that we can't
> change the API without requiring clients to make code changes for some
> methods. We could add duplicate method signatures that take long for the
> oid types, but the problem is methods like LargeObject.getOID() and
> LargeObjectManager.create() return int and these cannot be changed to long
> without adjustments to the calling code. Additionally changes would be
> required to the fastpath infrastructure to send and receive longs which
> would be complicated by the fact that oids are really unsigned int4, not
> int8.
>
> So this is a known problem, but one we've generally avoided dealing with
> because few people actually bump into it. I suppose we could add the new
> method signatures and add new methods like getLongOID and only bail out
> when code called the old ones with values to be to be represented by an
> int. I'll put it on my todo list, but it's not real close to the top.
>
> Kris Jurka
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

On Wed, 8 Feb 2006, Mark Lewis wrote:

> To maintain compatibility with existing code but still give access to
> the entire OID value space, why not map OID values >= 2**31 to negative
> numbers-- i.e. just consider them to be signed ints?

We don't always know when we're working with OIDs. If someone says
PreparedStatement.setInt() we don't know if they're passing us a mangled
OID they got from LargeObjectManager.create or if they're just passing us
an int, so we'd have to send the mangled form to the database. This will
break any triggers that are added to delete large objects on row deletion
and will completely break any non-JDBC clients that access the database.
You'd also have to adjust all of your ResultSet.getInt() calls to
determine if you're dealing with an OID and then check if it's a mangled
OID or not. Doesn't sound like a great idea to me.

Kris Jurka

Kris Jurka <books(at)ejurka(dot)com> writes:
> On Wed, 8 Feb 2006, Mark Lewis wrote:
>> To maintain compatibility with existing code but still give access to
>> the entire OID value space, why not map OID values >= 2**31 to negative
>> numbers-- i.e. just consider them to be signed ints?

> We don't always know when we're working with OIDs. If someone says
> PreparedStatement.setInt() we don't know if they're passing us a mangled
> OID they got from LargeObjectManager.create or if they're just passing us
> an int, so we'd have to send the mangled form to the database.

Does that matter? There's an implicit cast from int4 to oid on the
database side, so I am not clear where the problem really comes in.

regression=# select (-1)::int4::oid;
oid
------------
4294967295
(1 row)

regression=# select 4294967295::oid::int4;
int4
------
-1
(1 row)

regards, tom lane

On Wed, 8 Feb 2006, Tom Lane wrote:

> Does that matter? There's an implicit cast from int4 to oid on the
> database side, so I am not clear where the problem really comes in.
>
> regression=# select (-1)::int4::oid;

I was not aware of that. There is still an issue on the select side, yes
there is a possible conversion from oid::int4, but no one is going to
write their query with that oidcolumn::int4 cast so the driver will have
to selectively remap larger than int values to negative for OIDs, but
not for other types. I'm still not excited about giving the user a
different representation of the value than is in the database, but now it
at least seems feasible.

Kris Jurka

Kris Jurka <books(at)ejurka(dot)com> writes:
> ... There is still an issue on the select side, yes
> there is a possible conversion from oid::int4, but no one is going to
> write their query with that oidcolumn::int4 cast so the driver will have
> to selectively remap larger than int values to negative for OIDs, but
> not for other types.

Right, but at least on the select side you do know the datatype and so
you can do it (in principle anyway, not sure what it would actually take
in the JDBC code). The hard part would be on the data transmission
side ... but AFAICS you can just play dumb and send the negative integer
value as an integer parameter, letting the database coerce it to OID if
needed.

regards, tom lane