JPA, PostgreSQL and bytea vs. oid type

I started to work on a private project using JBoss Seam, Facelets, RichFaces (proven stack from Seam’s Booking demo :-)) with PostgreSQL 8.3 as a backing database – database I prefer for years for whatever reason. Hibernate is used as the JPA provider (packaged with Seam anyway) and the most recent JDBC 4 driver for PostgreSQL is used.

I added column:

picture bytea;

Somehow – after reading 8.4 Binary Data Types – I thought that bytea is the right guy for the job. Of course I had an entity with proper annotation and field:

    private byte[] picture;
    @Lob
    @Basic(fetch = FetchType.LAZY)
    public byte[] getPicture() {
        return picture;
    }

    public void setPicture(byte[] picture) {
        this.picture = picture;
    }

What was my surprise when following exception occurred:

java.sql.SQLException: ERROR: column "picture"
  is of type bytea but expression is of type oid

Now – when I know the solution – this message is pretty clear. It would be in case different types were mentioned in the message. While I understood that “he” somehow dislikes bytea, I couldn’t understand what the hell “oid” (as an “object identifier”) has to do with it?

I started my investigation with Google in one hand and PostgreSQL in the other. While 8.16 Object Identifier Types was not very helpful – as in “what does have ‘unsigned four-byte integer’ to do with my byte array?!” – some googled pages convinced me that oid indeed is that type I should use – mostly one from postgresql.com domain. Truth is that I didn’t understand why and how that oid type manages to store my byte array. But I’ve tried it, it stores it, it reads it, no exception is thrown.

Now I’ve finished second part of my investigation and the results are:

  • There is special Large Object facility in PostgreSQL and it’s based on using oid type that references actual large object stored in different table behind the scene.
  • JDBC documentation also mentions that you can use oid instead of bytea.
  • In that same chapter you can learn that bytea is supported since JDBC 7.2 version – that is not very long ago.
  • I don’t know if JPA/Hibernate can use bytea, but – honestly – I don’t care while this works fine. And it does.

So if you use PostgreSQL along with JPA and bytea type is throwing an exception, try oid. I’m not sure how well JDBC supports bytea, I don’t know why JPA wants to use oid and if other JPA providers (Toplink?) can use bytea. It’s not straightforward to use oid as a type for binary data – because you can learn this only in JDBC documentation and not in the mainstream PostgreSQL manual (I don’t count that chapter 31 because you’ll likely never use it while creating your tables). If you google this with along with “JPA” or “@Lob” your results might not lead you to proper articles quickly enough… so hopefully this post might help it a bit.

If you have some facts to add, feel free to comment.

Edit 8-Feb-2013 – warning: My colleague informed me that after removing oid row from a table, large object itself stays in the DB if you use Hibernate (can’t tell anything about other providers). Reportedly, there is hot ongoing debate between Hibernate and PostgreSQL guys. Both may be right that it’s not their job to do the stuff and they don’t have enough information to delete the object, because large object itself can be shared and referenced from more tables and in fact only you, the programmer can know if the object can go away or not. Check the PostgreSQL documentation to find out how to remove the object, also check if vacuumlo doesn’t solve your problem.

Advertisements

16 thoughts on “JPA, PostgreSQL and bytea vs. oid type

  1. I came across the same issue …I am using OID datatype to store image…

    id | branding_logo
    —+—————
    1 | 206533
    2 | 206534
    3 | 206535
    9 | 206543
    7 | 206537

    How can i identify that is store the image…

    Please help me..
    shashi

  2. I’m not sure what you need. I don’t care what’s in that oid column – I leave it to PostgreSQL/Hibernate and when I ask for byte[] from an entity, I always get what I want.

  3. I am using jboss seam …and richfaces .. for file upload..

    I used bytea for storing image into daatabase … in postgres 8.3
    I got exception

    is of type bytea but expression is of type Integer…
    Then i change to OID datatype same as bolb in oracle…. .

    This is my java class :

    private byte[] brandingLogo;

    @Column(name = “branding_logo”)
    @Lob
    public byte[] getBrandingLogo() {
    return this.brandingLogo;
    }

    public void setBrandingLogo(byte[] brandingLogo) {
    this.brandingLogo = brandingLogo;
    }

    public void fileUploadListener(UploadEvent event) throws Exception{

    upload = event.getUploadItem();
    if (upload.isTempFile()) {
    file = upload.getFile();
    } else {
    ByteArrayOutputStream b = new ByteArrayOutputStream();
    b.write(upload.getData());
    }
    }

    public UploadItem getUpload() {
    return upload;
    }

    public void setUpload(UploadItem upload) {
    this.upload = upload;
    }

    public File getFile() {
    return file;
    }

    public void setFile(File file) {
    this.file = file;
    }

    But when i store it…
    id | branding_logo
    —+—————
    1 | 206533
    2 | 206534
    3 | 206535
    9 | 206543
    7 | 206537

    How can i identify that is store the image… in database..
    I guess it store sequence number…

    Please help me…
    shashi

  4. I guess there would be null in branding_logo if there is no image. Byte array is stored elsewhere – this is PostgreSQL’s magic. Data are stored in some “secret” Postgresql store and oid is only referencing them. Can you obtain the image back from the entity? I can’t understand your problem – you don’t mention if it works or not. It works for me. I can display the picture directly with seams tag referencing the property of the entity.

    I wrote everything I know into the blog post. Sorry.

  5. In then front end this is my xhtml file:
    —————————————–

    …..
    ….. [ Other Text Fields…..]
    ……

    LOGO

    …..
    ……
    …..
    ….

    Java Entity class :
    ——————

    ….
    …..
    …..

    private byte[] brandingLogo;

    …..
    …..

    @Column(name = “branding_logo”)
    @Lob
    public byte[] getBrandingLogo() {
    return this.brandingLogo;
    }
    public void setBrandingLogo(byte[] brandingLogo) {
    this.brandingLogo = brandingLogo;
    }

    …..
    …..
    …..

    My JavaHome Class :
    ——————-

    @In(scope=ScopeType.SESSION,required=false)
    @Out(scope=ScopeType.SESSION, required=false)
    File file;

    @In(scope=ScopeType.SESSION,required=false)
    @Out(scope=ScopeType.SESSION, required=false)
    UploadItem upload;

    /* ****** File upload section ***** */

    // UploadItem upload;
    public void fileUploadListener(UploadEvent event) throws Exception{
    upload = event.getUploadItem();
    if (upload.isTempFile()) {
    file = upload.getFile();
    } else {
    ByteArrayOutputStream b = new ByteArrayOutputStream();
    b.write(upload.getData());
    }
    }
    public UploadItem getUpload() {
    return upload;
    }
    public void setUpload(UploadItem upload) {
    this.upload = upload;
    }
    public File getFile() {
    return file;
    }
    public void setFile(File file) {
    this.file = file;
    }

    ————————————————————————
    public String persist() {
    try {
    SaasTenants saasTenants = getInstance();

    /* ****** File upload section ***** */
    if(upload!=null){
    saasTenants.setBrandingLogo(file.getAbsolutePath().getBytes());
    String fileName = upload.getFileName();
    int postion = fileName.lastIndexOf(‘.’);
    saasTenants.setLogoExtension(fileName.substring(postion, fileName.length()));
    }

    saasTenants.setCreationDate(new Date());
    saasTenants.setUpdateDate(new Date());

    entityManager.persist(saasTenants);
    }

    ——————————————————————————

    Sorry for not mentioning if it works or not….

    I am using
    richfaces-ui-3.2.1.GA.jar
    richfaces-api-3.2.1.GA.jar
    richfaces-impl-3.2.1.GA.jar files

    In richfaces i set immediateUpload=”true” so when i select a image file
    I calls then fileUploadListener method in java home class….

    UploadItem upload; (for getting the extension..)
    File file; ( for getting the upload file…)

    from this object i can able to get bytes of the selected image and extension..setBrandingLogo
    I put this object in session scope…

    Then i entered all the textfield like…
    name
    Address… etc..

    When i submit the form i get the byte from the (File file;) object and set to branding..
    at the time i have byte array… with values…

    Then i call persist() in java Home class to insert image…

    Please can u suggest me is this the right way to do it….
    shashi

  6. my xhtml file..

    rich:fileUpload id=”fileUploadID” immediateUpload=”true”
    fileUploadListener=”#{saasTenantsHome.fileUploadListener}”
    listWidth=”460″ listHeight=”60″
    immediate=”true”

  7. I had the same problem with
    @Lob
    @Basic(fetch = FetchType.LAZY, optional = false)
    public byte[] getContentData() {
    return contentData;
    }

    The solution is to omit @Lob annotation leaving just

    @Basic(fetch = FetchType.LAZY, optional = false)
    public byte[] getContentData() {
    return contentData;
    }

  8. I ran across this same error message. I solved it by setting my object’s byte[] field to a new byte[0] instead of null before saving it into the database.

    cheers

  9. This problem can be solved by changing the blob field datatype in the table from oid to bytea.

    alter table your_table_name drop field blob_field_name;
    alter table your_table_name add field blob_field_name bytea;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s