2

I have such a query, which deletes about 16 millions records from table bills and also deletes records from other four tables in cascade with bill_id foreign key. It takes more than hour on my database.

public int deleteExpiredBills(final Integer someDate, final Set<Integer> itemIdsCanNotBeDeleted) {
    return namedParameterJdbcTemplate.update(
            "DELETE FROM bills WHERE expired_date::date < CURRENT_DATE - :someDate " +
                    "AND item_id NOT IN (:itemIdsCanNotBeDeleted)",
            new MapSqlParameterSource(Map.of(
                    "someDate", someDate,
                    "itemIdsCanNotBeDeleted", itemIdsCanNotBeDeleted)
            )
    );
}

Moreover, when i call this query (code from above) from my spring boot app I get this exception (but postgres still performs the DELETE after some time):

com.zaxxer.hikari.pool.ProxyConnection - HikariPool-1 - Connection org.postgresql.jdbc.PgConnection@1de77ce0 marked as broken because of SQLSTATE(08006), ErrorCode(0)
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.

Is this exception being thrown because my transaction is taking too long?

What is optimal solution in such situations? Should i split this transaction in multiple smaller by using LIMIT n and executing DELETE in a loop until there are no more records?

SorryForAsking
  • 323
  • 2
  • 18
  • 1
    If you need a 16 million-changes transaction, so you do. Now, you shouldn't be calling this process in an interactive manner. Submit it and then have a polling or notification strategy to find out when it ends and its result. Maybe, you can write a stored proc for this to run it in a deferred way. – The Impaler May 02 '23 at 11:25
  • You should try `TRUNCATE` instead of `DELETE`. – Zorglube May 02 '23 at 13:10
  • Does this answer your question? [HikariPool-1 - Connection marked as broken because of SQLSTATE(08006), ErrorCode(0)](https://stackoverflow.com/questions/60925289/hikaripool-1-connection-marked-as-broken-because-of-sqlstate08006-errorcode) – Panagiotis Bougioukos May 02 '23 at 13:34
  • no, I'm not able to change jdk version and my transaction is too long to change timeout that much – SorryForAsking May 02 '23 at 13:41
  • @TheImpaler thanks, but is there any ways to avoid waiting of query finishing in java spring without using stored procedure? – SorryForAsking May 02 '23 at 13:44
  • @TheImpaler and isn't it bad to have that long transcations? Is there risk of blocking other transactions? – SorryForAsking May 02 '23 at 14:02
  • 1
    @SorryForAsking Of course such a long transaction will have side effects. It all depends on the requirements; if the requirements need it, then you need to do it. Alternatively, you can divide it the way microservices do, but that becomes very expensive. – The Impaler May 02 '23 at 14:50

1 Answers1

1

Is this exception being thrown because my transaction is taking too long?

Yes. This is caused by time-out.

What is optimal solution in such situations?

I would use a native query for deleting. Additionally increase the time-out definition on the database to avoid an error on the connection. Additionally - as stated in the comment by @The Impaler - don't wait for the end of execution of the delete query.

To run your query without waiting you can execute it on a different thread, hence run it asynchronously. Like this:

First enable the asynchronous calls by adding @EnableAsync annotation:

@EnableAsync
@SpringBootApplication
public class MyApplication {
// ...
}

Then add the @Async annotation to you long running method:

@Async
void myLongRunningTask() {
// ...
}
Mar-Z
  • 2,660
  • 2
  • 4
  • 16