Thread: Problems with "CREATE CAST"

Postgresql Community:

We have an Java/JDBC application that runs against a range of versions
of Postgresql from 7.4 though 8.3 and are now moving to 8.4.

Because our databases will never approach 4GB in size we still use OIDs
... that is, in newer versions of Postgresql we create OIDs on all
tables with:

SET default_with_oids = true;

In Postgresql 8.4, however, we are seeing the following error:

SQL error executing statement.
update status set some_column = 'some_value' where oid = 'some_string'
org.postgresql.util.PSQLException: ERROR: operator does not exist: oid =
character varying
  Hint: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
  Position: 132

Because these SQL commands are generated on the fly by a Java
application that opens a JDBC connection to our database, we would
rather not use explicit casting and would prefer to add appropriate
casts in our Postgresql 8.4 databases to match the behavior that we get
in Postgresql 7.4/8.0/8.1/8.2/8.3 databases.

When I issue the command "\dC" and look for the casts that would convert
an OID to character varying, in Postgresql 8.3 and earlier I find the cast:

Source type   Target type             Function   Implicit?
oid                 character_varying   text           in assignment

This cast is missing in Postgresql 8.4.

So, I thought I could resolve this by creating the appropriate cast.
After checking the documentation and running as the database user
postgres, I thought that the following  CREATE CAST command would create
the "missing" oid to character varying cast:

CREATE CAST (oid AS character varying) WITH FUNCTION text(oid) AS
ASSIGNMENT;

However, this results in "ERROR: function text(oid) does not exist."

Clearly, I must have misread or misunderstood the CREATE CAST syntax in
my attempts to create a pre-Postgresql-8.4 cast from oid to character
varying.  Does anyone with more experience using the CREATE CAST command
see my problem?

Thanks for your consideration,

John

John Shott <shott@stanford.edu> writes:
> update status set some_column = 'some_value' where oid = 'some_string'
> org.postgresql.util.PSQLException: ERROR: operator does not exist: oid =
> character varying

> Because these SQL commands are generated on the fly by a Java
> application that opens a JDBC connection to our database, we would
> rather not use explicit casting and would prefer to add appropriate
> casts in our Postgresql 8.4 databases to match the behavior that we get
> in Postgresql 7.4/8.0/8.1/8.2/8.3 databases.

I can assure you that that didn't work in 8.3 either.

You really need to fix your app to not be forcing these strings to
varchar.  Even in old versions, that code worked for only very small
values of "work": it would have used textual rather than numeric
comparison, which isn't really the semantics you want and would have
had a huge performance cost (notably, the inability to use an index
on the oid column).

What you probably need to be doing is not using setString() to set the
parameter values, but you could get better advice about that on the
pgsql-jdbc list.

            regards, tom lane

Tom Lane et al:

Thank you for your comments and observations.  In particular, you make
me realize that I likely don't know how the JDBC connection is handling
things. I find that I often tend to assume that what I see and use on
the interactive command like is exactly what is coming across the wire
on a JDBC connection.  In fact, you are exactly correct about the
inability to use an index on the oid column.  We do have an index and
yet have seen performance problems for a table that rarely exceeds a few
hundred rows.

I'll take your advice and see what the JDBC folks suggest and see if we
can handle things in a better fashion.

Thanks again,

John