Question 246956 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Jooq custom delete method throw ERROR: cannot execute DELETE in a read-only transaction

I use Springboot3 and Jooq to create my project and define a custom delete method deleteByUsername follow jooq delete guide


@Repository
public class UserRepository extends UserDao {

  @Autowired
  public UserRepository(Configuration configuration) {
    super(configuration);
  }

  /* --- some query method can run sucessful in run and test---
   ........
  */

  public void deleteByUsername(String username) {
    ctx().delete(USER).where(USER.USERNAME.eq(username)).execute();
  }
}

The UserRepository is extend UserDao so ctx() is return a DSLContext instance.

@Repository
public class UserDao extends AbstractSpringDAOImpl<UserRecord, jooq.tables.pojos.User, Long> {

    /**
     * Create a new UserDao without any configuration
     */
    public UserDao() {
        super(User.USER, jooq.tables.pojos.User.class);
    }

    /**
     * Fetch records that have <code>id IN (values)</code>
     */
    public List<jooq.tables.pojos.User> fetchById(Long... values) {
        return fetch(User.USER.ID, values);
    }

    /**
     * Fetch a unique record that has <code>id = value</code>
     */
    public jooq.tables.pojos.User fetchOneById(Long value) {
        return fetchOne(User.USER.ID, value);
    }

    .....more
}

UserDao is generate by jooq code generate plugin when if you set up with


generate.apply {
   isDeprecated = false
   isRecords = true
   isImmutablePojos = false
   isFluentSetters = true
   isDaos = true // will create dao layer by jooq
   isSpringDao = true
   isSpringAnnotations = true
}

All *Dao is extend DAOImpl, and ctx() is the way to get DSLContext in dao layer

public abstract class DAOImpl<R extends UpdatableRecord<R>, P, T> implements DAO<R, P, T> {

    private final Table<R>     table;
    private final Class<P>     type;
    private RecordMapper<R, P> mapper;
    private Configuration      configuration;


    /**
     * Inject a configuration.
     * <p>
     * This method is maintained to be able to configure a <code>DAO</code>
     * using Spring. It is not exposed in the public API.
     */
    public /* non-final */ void setConfiguration(Configuration configuration) {
        this.mapper = ((FieldsImpl<R>) table.recordType()).mapper(this.configuration = Tools.configuration(configuration), type);
    }

    public /* non-final */ DSLContext ctx() {
        return configuration().dsl();
    }

}

Problem

When i run deleteByUsername in springboot test it was failed, and throw exception below.

But jooqDeleteRunSuccess run suceess. The userRepository.delete method which defined in jooqDeleteRunSuccess is jooq generated dao layer’s method, it’s not custom method.

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@AutoConfigureWebTestClient
public class SignE2ETest {

  @Autowired private WebTestClient webClient;

  @Autowired private UserRepository userRepository;

  @Test
  void jooqDeleteRunSuccess() {
    User stubUser = new User();
    stubUser.setUsername("test_5fab32c22a3e");
    stubUser.setPassword("test_eab28b939ba1");
    userRepository.insert(stubUser);
    userRepository.delete(userRepository.fetchOneByUsername("test_5fab32c22a3e"));
  }

  @Test
  void myDeleteRunFailed() {
    User stubUser = new User();
    stubUser.setUsername("test_5fab32c22a3e");
    stubUser.setPassword("test_eab28b939ba1");
    userRepository.insert(stubUser);
    userRepository.deleteByUsername("test_5fab32c22a3e");
  }
}

org.jooq.exception.DataAccessException: SQL [delete from "mjga"."user" where "mjga"."user"."username" = ?]; ERROR: cannot execute DELETE in a read-only transaction
 at org.jooq_3.18.6.POSTGRES.debug(Unknown Source)
 at org.jooq.impl.Tools.translate(Tools.java:3470)
 at org.jooq.impl.Tools.translate(Tools.java:3458)
 at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:801)
 at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:360)
 at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:115)
 at com.mjga.repository.UserRepository.deleteByUsername(UserRepository.java:61)
 at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
 at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.base/java.lang.reflect.Method.invoke(Method.java:568)
.....

Question

As you see there is no explicit config to set postgresql to READ-ONLY mode, I don’t know why this READ-ONLY exception happend?

Why myDeleteRunFailed throw READ-ONLY exception but jooqDeleteRunSuccess not?

How to fix it?

thanks!

Enviroment

  • springboot-3.1.2
  • jooq-3.18.6
  • postgresql-15.4
dependencies {
    implementation("org.springframework.boot:spring-boot-starter-actuator")
    implementation("org.springframework.boot:spring-boot-starter-jooq")
    implementation("org.springframework.boot:spring-boot-starter-mail")
    implementation("org.springframework.boot:spring-boot-starter-quartz")
    implementation("org.springframework.boot:spring-boot-starter-security")
    implementation("org.springframework.boot:spring-boot-starter-validation")
    implementation("org.springframework.boot:spring-boot-starter-web")
    implementation("org.springframework.boot:spring-boot-starter-websocket")
    implementation("org.springframework.boot:spring-boot-starter-aop")
    implementation("org.apache.commons:commons-lang3:3.13.0")
    implementation("org.apache.commons:commons-collections4:4.4")
    implementation("org.springdoc:springdoc-openapi-starter-webmvc-ui:2.2.0")
    implementation("org.jooq:jooq-codegen:3.18.6")
    implementation("org.jooq:jooq-meta:3.18.6")
    implementation("com.auth0:java-jwt:4.4.0")
    implementation("org.testcontainers:junit-jupiter:1.19.0")
    implementation("org.testcontainers:postgresql:1.19.0")
    implementation("org.testcontainers:testcontainers-bom:1.19.0")
    runtimeOnly("org.postgresql:postgresql")
    compileOnly("org.projectlombok:lombok")
    developmentOnly("org.springframework.boot:spring-boot-devtools")
    developmentOnly("org.springframework.boot:spring-boot-docker-compose")
    testImplementation("org.springframework.boot:spring-boot-testcontainers:3.1.2")
    testImplementation("org.springframework.boot:spring-boot-docker-compose")
    testImplementation("org.springframework.boot:spring-boot-starter-webflux")
    testImplementation("org.springframework.boot:spring-boot-starter-test")
    testImplementation("org.springframework.security:spring-security-test")
    jooqGenerator("org.postgresql:postgresql")
    annotationProcessor("org.springframework.boot:spring-boot-configuration-processor")
    annotationProcessor("org.projectlombok:lombok")
}


services:
  postgres:
    container_name: postgres
    image: 'postgres:15.4'
    environment:
      POSTGRES_DB: postgres
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - ${DATABASE_PORT}:5432
    volumes:
      - ./db.d/init:/docker-entrypoint-initdb.d
      - ${DATABASE_STORE}:/var/lib/postgresql/data
    restart: on-failure

I have checked my DB read_only status

SHOW default_transaction_read_only;
SHOW transaction_read_only;

off
off
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    @Frisk @Guss @LukasEder

    Thank you, guys! I found the solution.

    The problem, as Frisk and Lukas Eder mentioned, was due to some Spring Boot setup making the transactional behavior default to "read-only."

    When using JOOQ with Spring, the DAO layer extends AbstractSpringDAOImpl, which has the @Transactional(readOnly = true) annotation at the class level. This caused UserRepository to be treated as read-only for all its methods.

    To solve the problem, I added the @Transactional annotation at the method level to override the class-level annotation, like this:

    @Transactional
public void deleteByUsername(String username) {
    ctx().delete(USER).where(USER.USERNAME.eq(username)).execute();
}

  2. 0

    this’s the first time that i answer question on stackoverflow.

    my english is not good, so i hope you can understand what i say

    The error "cannot execute DELETE in a read-only transaction" indicates that you are attempting to perform a DELETE operation in a read-only transaction, which does not allow modifying the database.

    so i guess the jooq method "jooqDeleteRunSuccess" initiated a writable transaction before executing the DELETE operation

    but your personal method "myDeleteRunFailed" only get a DSLContext instance, but not initiate a writable transaction

    you can use this codes before your method and try again

    DSLContext dslContext = DSL.using(connection, SQLDialect.MYSQL);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search