DdlUtils
  1. DdlUtils
  2. DDLUTILS-161

PostgreSQL Blob type mapping should be OID in v8

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: 1.3
    • Component/s: Core - PostgreSql
    • Labels:
      None
    • Environment:
      PostgreSQL 8.1.8 on Fedora core 6 (i386).

      Description

      For PostgreSQL v8 JDBC drivers the blob type maps to OID, not BYTEA:
      from here: http://doc.postgresintl.com/jdbc/ch08.html
      "....To use the Large Object functionality you can use either the LargeObject class provided by the PostgreSQL™ JDBC driver, or by using the getBLOB() and setBLOB() methods...."

      When using PostgreSQL (v7 or v8) with the v7 JDBC drivers BYTEA works fine, but with the v8 drivers you get an error message:
      ERROR: column "binvalue" is of type bytea but expression is of type oid

        Activity

        Thomas Dudziak made changes -
        Fix Version/s 1.2 [ 12312767 ]
        Fix Version/s 1.3 [ 12313497 ]
        Thomas Dudziak made changes -
        Fix Version/s 1.2 [ 12312767 ]
        Richard Bounds made changes -
        Field Original Value New Value
        Attachment TestPostgresBlob.java [ 12352920 ]
        Hide
        Richard Bounds added a comment -

        Unit test to reproduce the problem.

        Show
        Richard Bounds added a comment - Unit test to reproduce the problem.
        Hide
        Richard Bounds added a comment -

        I'm actually using Hibernate to access the database, but I think the attached unit test reproduces the problem more simply with plain JDBC - it creates a table, inserts a row with a blob, copies the blob to a new row, then deletes the table. When the table has a column of type OID, everything seems fine. When it is BYTEA I get the following exception:
        org.postgresql.util.PSQLException: ERROR: column "photo" is of type bytea but expression is of type integer
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:308)
        at TestPostgresBlob.testBlob(TestPostgresBlob.java:69)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at junit.framework.TestCase.runTest(TestCase.java:164)
        at junit.framework.TestCase.runBare(TestCase.java:130)
        at junit.framework.TestResult$1.protect(TestResult.java:106)
        at junit.framework.TestResult.runProtected(TestResult.java:124)
        at junit.framework.TestResult.run(TestResult.java:109)
        at junit.framework.TestCase.run(TestCase.java:120)
        at junit.framework.TestSuite.runTest(TestSuite.java:230)
        at junit.framework.TestSuite.run(TestSuite.java:225)

        For some reason I can reproduce this with either set of Drivers - I guess perhaps Hibernate is doing something to detect the V7 drivers.

        Show
        Richard Bounds added a comment - I'm actually using Hibernate to access the database, but I think the attached unit test reproduces the problem more simply with plain JDBC - it creates a table, inserts a row with a blob, copies the blob to a new row, then deletes the table. When the table has a column of type OID, everything seems fine. When it is BYTEA I get the following exception: org.postgresql.util.PSQLException: ERROR: column "photo" is of type bytea but expression is of type integer at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:308) at TestPostgresBlob.testBlob(TestPostgresBlob.java:69) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at junit.framework.TestCase.runTest(TestCase.java:164) at junit.framework.TestCase.runBare(TestCase.java:130) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:120) at junit.framework.TestSuite.runTest(TestSuite.java:230) at junit.framework.TestSuite.run(TestSuite.java:225) For some reason I can reproduce this with either set of Drivers - I guess perhaps Hibernate is doing something to detect the V7 drivers.
        Hide
        Thomas Dudziak added a comment -

        Mhmm, AFAICT bytea is the correct type (see e.g. http://www.postgresql.org/docs/8.1/static/datatype-binary.html vs. http://www.postgresql.org/docs/8.1/static/datatype-oid.html). Also, blobs work fine (there are unit tests for that and they pass in PostgreSql 8.1 using the 8.1 driver).
        Could you give more info as to what you did to produce this error ? E.g. the model XML, the connection info, Ant/code snippet etc.

        Show
        Thomas Dudziak added a comment - Mhmm, AFAICT bytea is the correct type (see e.g. http://www.postgresql.org/docs/8.1/static/datatype-binary.html vs. http://www.postgresql.org/docs/8.1/static/datatype-oid.html ). Also, blobs work fine (there are unit tests for that and they pass in PostgreSql 8.1 using the 8.1 driver). Could you give more info as to what you did to produce this error ? E.g. the model XML, the connection info, Ant/code snippet etc.
        Richard Bounds created issue -

          People

          • Assignee:
            Thomas Dudziak
            Reporter:
            Richard Bounds
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development