DELETE statement causes the database to multiply in size and stuck.

[Vojtěch Jaroš]

Hello,

we are using a H2 database that is normally ~20 GB in size. It was created from scratch with the current version of H2 (2.1.214). Every time when it reaches some size which was not exactly determined, but something over 20 GB, the cleanup DELETE query that normally takes just a few minutes gets stuck for hours. During these hours, CPU is low, but the DB is growing in size, reaching the size of the partition (currently 200 GB). If I interrupt the process (^C), I get the attached error (table names and app packages redacted, but otherwise no change).

The query is:
delete  from T1 where T1_ID in (select T1_ID from T1 where not exists (select null from T2 where T2_T1_FID=T1_ID union select null from T3 where T3_T1_FID=T1_ID))

where:

T1_ID is the primary key of T1
T2_T1_FID is a foreign key in T2 pointing to T1_ID
T3_T1_FID is a foreign key in T3 pointing to T1_ID

the JDBC string is:
jdbc:h2:file;DB_CLOSE_ON_EXIT=FALSE;CACHE_SIZE=2097152;MAX_COMPACT_TIME=67890

The goal is to delete orphan entries in T1 that are not referenced in neither T2 nor T3.

When the DB is smaller, this doesn't happen, now it happened for the third time when building the DB from scratch.

When the union statement was not present (there was just T2 and no T3), it worked too.

Is this a known bug? Or any other ideas? (Maybe this kind of query is not supported in H2? I'm not a DB specialist).

Vojtěch Jaroš

[Noel Grandin]

What does

   EXPLAIN PLAN <your query>

show?

[Vojtěch Jaroš]

I found an issue with the SQL command, the second table has a where clause which is always true:

where T3_T1_FID =  T3_T1_FID

The redacted file with the exception has it wrong, I made a mistake when renaming the tables for sending here. Sorry for that. I will run my application again with the correct statement and see if it's OK now.

Anyway, even the wrong SQL shouldn't cause this behavior so in case you want to have a look into it, the explain plan (for the buggy query that was used) says:

DELETE FROM "PUBLIC"."T1"
    /* PUBLIC.PRIMARY_KEY_C: T1_ID IN(SELECT DISTINCT
        T1_ID
    FROM PUBLIC.T1
        /* PUBLIC.T1_INDEX */
    WHERE NOT EXISTS(    (SELECT
            NULL
        FROM PUBLIC.T2
            /* PUBLIC.CONSTRAINT_INDEX_9A0: T2_T1_FID = T1_ID */
        WHERE T2_T1_FID = T1_ID)
        UNION
        (SELECT
            NULL
        FROM PUBLIC.T3
            /* PUBLIC.CONSTRAINT_INDEX_1ED */
        WHERE T3_T1_FID = T3_T1_FID)))
     */
WHERE "T1_ID" IN(    SELECT DISTINCT
        "T1_ID"
    FROM "PUBLIC"."T1"
        /* PUBLIC.T1_INDEX */
    WHERE NOT EXISTS(    (SELECT
            NULL
        FROM "PUBLIC"."T2"
            /* PUBLIC.CONSTRAINT_INDEX_9A0: T2_T1_FID = T1_ID */
        WHERE "T2_T1_FID" = "T1_ID")
        UNION
        (SELECT
            NULL
        FROM "PUBLIC"."T3"
            /* PUBLIC.CONSTRAINT_INDEX_1ED */
        WHERE "T3_T1_FID" = "T3_T1_FID")))

By the way, when stopping the process of deleting, the DB had 148GB. After reopening the database and shutting it down with SHUTDOWN COMPACT, it has 1.4GB.

so 19. 11. 2022 v 11:25 odesílatel Noel Grandin <noelg...@gmail.com> napsal:

What does

   EXPLAIN PLAN <your query>

show?

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/DckQhsI73i0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/CAFYHVnVEfV9czquzb-nXy4j8kWeytZN4AjXg7TpCoz0yA8wO-w%40mail.gmail.com.

[Noel Grandin]

HI

There probably is something wrong with our execution of that query, but I'm afraid it's too complex to be debuggable
just from the overview.

Unless you can prepare us a self-contained test-case, that won't be much we can do, sorry.

Regards, Noel.