issue with the new database Optimisations on postgreSQL db

[Robonau]

Steps to reproduce

@renjfk @Syer10
have a server setup in docker with postgres quite large (~1000 manga in library) in version v2.1.1933-preview
(the version before #1660 got merged) all works no issues
move to v2.1.1937-preview or later and i get issues with graphql queries not responding with any data

same query ran seconds apart from each other

reverting back to v2.1.1933-preview and it all works again so its not a database corruption issue

log from a startup and making a few gql requests
application.log

Expected behavior

it should work

Actual behavior

it doesn't work

Suwayomi-Server version

v2.1.1937

Server operating system

arch linux docker

Server Desktop Environment

kde

Server JVM version

came with docker

Used client name

both VUI and webui

Client version

n/a

Used web browser

n/a

Client operating system

arch

Other details

No response

Acknowledgements

• I have searched the existing issues and this is a new ticket, NOT a duplicate or related to another open or closed issue.
• I have written a short but informative title (ideally less than ~100 characters).
• I have tried the troubleshooting guide described in README.md
• I have updated to the latest version.
• I have filled out all of the requested information in this form, including specific version numbers.
• I understand that Suwayomi does not have or fix any extensions, and I will not receive help for any issues related to sources or extensions.

[renjfk]

I wonder if it has to do with batching 🤔 Could you provide a small reproducer or clear steps to reproduce?

[Robonau]

i can try, no idea if i can (syer couldn't)

[Robonau]

---
name: Suwa Stack test
services:
  suwayomi_test:
    container_name: suwayomi_test
    image: ghcr.io/suwayomi/suwayomi-server:v2.1.1937-preview
    depends_on:
      postgresql_test:
        condition: service_healthy
        restart: true
    volumes:
      - ./test:/home/suwayomi/.local/share/Tachidesk
    environment:
      TZ: Europe/London
      DATABASE_TYPE: POSTGRESQL
      DATABASE_URL: postgresql://postgresql_test:5432/test
      DATABASE_USERNAME: test
      DATABASE_PASSWORD: test
    user: "1000:568"
    ports:
      - "4567:4567"
  postgresql_test:
    container_name: postgresql_test
    image: postgres:17
    restart: unless-stopped
    volumes:
      - ./postgres:/data/postgres
    environment:
      TZ: Europe/London
      PGTZ: Europe/London
      POSTGRES_USER: test
      POSTGRES_PASSWORD: test
      PGDATA: /data/postgres
      POSTGRES_DB: test
    healthcheck:
      test:
        - CMD-SHELL
        - pg_isready -d test -U test
      interval: 10s
      timeout: 5s
      retries: 3

does it on the same server as my main setup

make the ./test folder
chown ./test to 1000:568
docker compose up -d
most queries are intermittent

im pulling locally now to test it on my desktop

[Robonau]

reproducible on my desktop as well as my server so isn't just an old cpu thing

[renjfk]

Thanks! I will have a look at it and prepare patch.

[Robonau]

@Syer10 can u reopen this issue.
#1681 helped significantly but didn't fix the issue entirely.
Im still getting the occasional error

"Error loading mangas": {
    "name": "CombinedError",
    "graphQLErrors": [
        {
            "name": "GraphQLError",
            "path": [
                "category"
            ],
            "originalError": {
                "message": "The field at path '/category' was declared as a non null type, but the code involved in retrieving data has wrongly returned a null value.  The graphql specification requires that the parent field be set to null, or if that is non nullable that it bubble up null to its parent and so on. The non-nullable type is 'CategoryType' within parent type 'Query'",
                "path": [
                    "category"
                ]
            },
            "extensions": {},
            "message": "The field at path '/category' was declared as a non null type, but the code involved in retrieving data has wrongly returned a null value.  The graphql specification requires that the parent field be set to null, or if that is non nullable that it bubble up null to its parent and so on. The non-nullable type is 'CategoryType' within parent type 'Query'"
        }
    ],
    "response": {}
}

and in logs

java.util.NoSuchElementException: Collection is empty.
        at kotlin.collections.CollectionsKt___CollectionsKt.first(_Collections.kt:208)
        at suwayomi.tachidesk.manga.impl.Manga.getMangaThumbnail$lambda$0(Manga.kt:412)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction$run(ThreadLocalTransactionManager.kt:355)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction$lambda$11(ThreadLocalTransactionManager.kt:402)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:410)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:401)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$lambda$5(ThreadLocalTransactionManager.kt:310)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:410)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:259)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:237)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:236)
        at suwayomi.tachidesk.manga.impl.Manga.getMangaThumbnail(Manga.kt:412)
        at suwayomi.tachidesk.manga.controller.MangaController$thumbnail$2$1$1.invokeSuspend(MangaController.kt:106)
        at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:34)
        at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:100)
        at kotlinx.coroutines.internal.LimitedDispatcher$Worker.run(LimitedDispatcher.kt:124)
        at kotlinx.coroutines.scheduling.TaskImpl.run(Tasks.kt:89)
        at kotlinx.coroutines.scheduling.CoroutineScheduler.runSafely(CoroutineScheduler.kt:586)
        at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.executeTask(CoroutineScheduler.kt:820)
        at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.runWorker(CoroutineScheduler.kt:717)
        at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.run(CoroutineScheduler.kt:704)
18:20:20.472 [DefaultDispatcher-worker-5] ERROR suwayomi.tachidesk.server.JavalinSetup -- NoSuchElementException while handling the request
java.util.NoSuchElementException: Collection is empty.
        at kotlin.collections.CollectionsKt___CollectionsKt.first(_Collections.kt:208)
        at suwayomi.tachidesk.manga.impl.Manga.fetchMangaThumbnail$lambda$0(Manga.kt:371)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction$run(ThreadLocalTransactionManager.kt:355)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction$lambda$11(ThreadLocalTransactionManager.kt:402)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:410)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:401)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$lambda$5(ThreadLocalTransactionManager.kt:310)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:410)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:259)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:237)
        at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:236)
        at suwayomi.tachidesk.manga.impl.Manga.fetchMangaThumbnail(Manga.kt:371)
        at suwayomi.tachidesk.manga.impl.download.fileProvider.impl.ThumbnailFileProvider.downloadImpl(ThumbnailFileProvider.kt:47)
        at suwayomi.tachidesk.manga.impl.download.fileProvider.impl.ThumbnailFileProvider.access$downloadImpl(ThumbnailFileProvider.kt:19)
        at suwayomi.tachidesk.manga.impl.download.fileProvider.impl.ThumbnailFileProvider$download$1.execute(ThumbnailFileProvider.kt:56)
        at suwayomi.tachidesk.manga.impl.ThumbnailDownloadHelper.download(ThumbnailDownloadHelper.kt:11)
        at suwayomi.tachidesk.manga.impl.Manga.getMangaThumbnail(Manga.kt:418)
        at suwayomi.tachidesk.manga.controller.MangaController$thumbnail$2$1$1.invokeSuspend(MangaController.kt:106)
        at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:34)
        at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:100)
        at kotlinx.coroutines.internal.LimitedDispatcher$Worker.run(LimitedDispatcher.kt:124)
        at kotlinx.coroutines.scheduling.TaskImpl.run(Tasks.kt:89)
        at kotlinx.coroutines.scheduling.CoroutineScheduler.runSafely(CoroutineScheduler.kt:586)
        at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.executeTask(CoroutineScheduler.kt:820)
        at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.runWorker(CoroutineScheduler.kt:717)
        at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.run(CoroutineScheduler.kt:704)

[renjfk]

@Robonau Are the steps you have provided to reproduce still valid?

[Robonau]

yea, just use the latest preview instead

[renjfk]

@Robonau I can't seem to reproduce the issue you are facing by doing the following:

• Started the service stack with current latest preview which is ghcr.io/suwayomi/suwayomi-server:v2.1.1959-preview on a clean database
• Added https://raw.githubusercontent.com/keiyoushi/extensions/repo/index.min.json to extension repository
• Installed Weeb Central extension
• Added couple of mangas to library
• Arbitrarily tried reading random chapters on various mangas

And nothing has failed so far.. Let me know if I'm missing something.

[Robonau]

video of it on my main db
https://streamable.com/1mxf4w