Purge Executions Error: Cannot forward queries while a cursor is open Detail: Please ensure there are no open cursors and try again

[jgoguette]

Describe the issue

Running into an issue with the purge task:

It only occurs on the: io.kestra.plugin.core.execution.PurgeExecutions but works on io.kestra.plugin.core.log.PurgeLogs
2024-11-23 01:27:44.912SQL [delete from executions where "key" = ?];

ERROR: Cannot forward queries while a cursor is open Detail: Please ensure there are no open cursors and try again
2024-11-23 01:27:44.912ERROR: Cannot forward queries while a cursor is open Detail: Please ensure there are no open cursors and try again

Task:

id: purge
namespace: company.name
description: |
  This flow will remove all executions and logs older than 1 month.
  We recommend running this flow daily to avoid running out of disk space.

tasks:
  - id: purge_executions
    type: io.kestra.plugin.core.execution.PurgeExecutions
    endDate: "{{ now() | dateAdd(-2, 'DAYS') }}"
    purgeLog: false
    states: 
    - KILLED
    - WARNING
    - SUCCESS

  - id: purge_logs
    type: io.kestra.plugin.core.log.PurgeLogs
    endDate: "{{ now() | dateAdd(-1, 'MONTHS') }}"

triggers:
  - id: daily
    type: io.kestra.plugin.core.trigger.Schedule
    cron: "@daily"

Environment

Kestra Version: latest
I'm using AWS:

EC2: m6i.4xlarge
RDS: db.r6g.4xlarge

I'm using postgres 16.4 Aurora RDS With Local read forwarding enabled.

Docker Swarm:

volumes:
  kestra-data:
    driver: local

services:
  kestra-webserver:
    image: kestra/kestra:latest
    deploy:
      replicas: 1
      restart_policy:
        condition: on-failure
        max_attempts: 30
    env_file: &env
      - .env_encoded
    user: "root"
    command: server webserver
    volumes: &volumes
      - kestra-data:/app/storage
      - /var/run/docker.sock:/var/run/docker.sock
      - /tmp/kestra-wd:/tmp/kestra-wd
    environment: &configuration
      KESTRA_CONFIGURATION: |
        datasources:
          postgres:
            url: jdbc:postgresql://endpoint:5432/kestra
            driverClassName: org.postgresql.Driver
            username: username
            password: password
        kestra:
          jdbc:
            cleaner:
              initialDelay: 1h
              fixedDelay: 1h
              retention: 3d
          tutorialFlows:
            enabled: false
          server:
            basic-auth:
              enabled: false
              username: "[email protected]" # it must be a valid email address
              password: kestra
          repository:
            type: postgres
          storage:
            type: s3
            s3:
              accessKey: "accessKey"
              secretKey: "secretKey"
              region: "region"
              bucket: "bucket"
          queue:
            type: postgres
          tasks:
            tmp-dir:
              path: /tmp/kestra-wd/tmp
          url: https://url.com
          variables:
            globals:
              env: staging
          environment:
            name: Staging
            color: "#FCB37C"
    ports:
      - "80:8080"
      - "8081:8081"
    networks:
      - shared-overlay-network

  kestra-worker-1:
    image: kestra/kestra:latest
    deploy:
      replicas: 2
      restart_policy:
        condition: on-failure
        max_attempts: 30
    env_file: *env
    user: "root"
    command: server worker --thread=256
    volumes: *volumes
    environment: *configuration
    depends_on:
    - kestra-webserver
    networks:
      - shared-overlay-network

  kestra-executor-1:
    image: kestra/kestra:latest
    deploy:
      replicas: 2
      restart_policy:
        condition: on-failure
        max_attempts: 30
    env_file: *env
    user: "root"
    command: server executor
    volumes: *volumes
    environment: *configuration
    depends_on:
    - kestra-webserver
    networks:
      - shared-overlay-network

  kestra-worker-2:
    image: kestra/kestra:latest
    deploy:
      replicas: 2
      restart_policy:
        condition: on-failure
        max_attempts: 30
    env_file: *env
    user: "root"
    command: server worker --thread=256
    volumes: *volumes
    environment: *configuration
    depends_on:
    - kestra-webserver
    networks:
      - shared-overlay-network

  kestra-executor-2:
    image: kestra/kestra:latest
    deploy:
      replicas: 2
      restart_policy:
        condition: on-failure
        max_attempts: 30
    env_file: *env
    user: "root"
    command: server executor
    volumes: *volumes
    environment: *configuration
    depends_on:
    - kestra-webserver
    networks:
      - shared-overlay-network

  kestra-worker-3:
    image: kestra/kestra:latest
    deploy:
      replicas: 2
      restart_policy:
        condition: on-failure
        max_attempts: 30
    env_file: *env
    user: "root"
    command: server worker --thread=256
    volumes: *volumes
    environment: *configuration
    depends_on:
    - kestra-webserver
    networks:
      - shared-overlay-network

  kestra-executor-3:
    image: kestra/kestra:latest
    deploy:
      replicas: 2
      restart_policy:
        condition: on-failure
        max_attempts: 30
    env_file: *env
    user: "root"
    command: server executor
    volumes: *volumes
    environment: *configuration
    depends_on:
    - kestra-webserver
    networks:
      - shared-overlay-network

  kestra-scheduler-1:
    image: kestra/kestra:latest
    deploy:
      replicas: 2
      restart_policy:
        condition: on-failure
        max_attempts: 30
    env_file: *env
    user: "root"
    command: server scheduler
    volumes: *volumes
    environment: *configuration
    depends_on:
    - kestra-webserver
    networks:
      - shared-overlay-network

networks:
  shared-overlay-network:
    external: true
    name: shared-overlay-network