Is there any problem in persisting an image in postgres and it is of type bytea?

Question

I have an application that was using spring framework version 4 (application that generated a .War full of settings...), it worked plausibly.

However, I decided to upgrade to version 5 (using spring boot, to make the project more streamlined without so many configuration classes...).

So consider:

In this application I have some entities:

• an avatar type object
• a user-type object

So a user has a @Onetoone type relationship with the avatar.

@Entity
@Table(name = "avatares")
public class Avatar extends AbstractPersistable<Long> {

    @Column(nullable = false)
    private String titulo;

    @Column(nullable = false)
    private String tipo;

    @Lob
    @Column(nullable = false)
    private byte[] avatar;

    ...


@Entity
@Table(name = "usuarios")
public class Usuario extends AbstractPersistable<Long> {

    @Override
    public void setId(Long id) {
        super.setId(id);
    }

    @Column(nullable = false, unique = true)
    private String nome;

    @Column(nullable = false, unique = true)
    private String email;

    @OneToOne(cascade = {CascadeType.PERSIST, CascadeType.REMOVE})
    @JoinColumn(name = "avatar_id")    
    private Avatar avatar;
    ...

When using version 4 of spring it persists the Avatar entity with the avatar attribute(array of bytes) as being of the type oid I could search for the user in the database and show your avatar image without any problem.

See in the image:

But when I upgraded to spring boot (Spring Boot v2.1.1.RELEASE = Spring v5.1.3.RELEASE) it persists with the oid type, and even all right , but to the search for the user in the database and show your avatar image gives the following Exception:

org.postgresql.util.PSQLException: Objetos Grandes não podem ser usados no modo de efetivação automática (auto-commit).

This problem is not from today a few months ago that I came across it, and I had been trying somehow alone to solve this little problem. I’ve tried everything that was configuration type in the property file (application.properties) and nothing, visited forums.

And as amazing as it sounds with mysql it works perfectly without any problem (yes that even using spring 5) using the @Lob annotation!

Ai Voce thinks : - why Voce does not use mysql then?

For the simple reason that I’m using postgis extension of postgres for a much larger purpose, and I spent a lot of time , until I was able to perform the querys with the data type Geometry.

I don’t know if I could do the same with mysql that supports Geometry type.

What’s the doubt after all?

In order to show the user’s blessed avatar I simply removed the note @Lob avatar attribute. Ai all perfect it saves in the database the byte array and then I recover without any exception.

And so?

My images cannot exceed 100k according to the business rule of my application. (They are not huge images..., they are very small)

As for the bank I wanted to know if this has any impact on the performance of the application, why did I see somewhere around that guy oid is more "light" than the type bytea. In other words the bytea type will take more space in the database unless empty spaces with memory garbage... already the oid Prfills only the original space of the image.

That’s true?

If so, how could I bring back the avatar using the @Lob annotation that stores the attribute as oid?