Rows reappearing in Tables after Auto-Vacuum Failure in PostgreSQL on Windows

PostgreSQL
1

Version: PostgreSQL 10.21, compiled by Visual C++ build 1800, 64-bit

Platform: Windows

In our application code, we’ve implemented logic to clean up entries in several PostgreSQL tables using a loop. Although some of these tables have foreign key references, we’ve included them to ensure a thorough deletion process. Here’s a simplified code snippet:

String tables[] = {"TableA", "TableB", "TableC", "TableD", "TableE", "TableF", "TableG", "TableH"};

for (String tableName : tables) {
    try {
        stmt = conn.prepareStatement("DELETE FROM " + tableName);
        stmt.execute();
    } catch (Exception ex) {
        // Log the exception
    }
}

Notably, it leaves entries in different tables each time, and we’ve found auto-vacuum failure traces in the pg_log for these tables at the same time. Here’s an example of the error messages from pg_log:

2023-09-28 12:03:35.955 IST,,,11592,,65151e2d.2d48,1,,2023-09-28 12:03:17 IST,6/19,122786262,ERROR,42501,"could not truncate file ""base/16509/6935026"" to 0 blocks: Permission denied",,,,,"automatic vacuum of table ""DB.public.TableB""",,,,"" 2023-09-28 12:03:37.205 IST,,,11592,,65151e2d.2d48,2,,2023-09-28 12:03:17 IST,6/45,122786326,ERROR,42501,"could not truncate file ""base/16509/6935443"" to 0 blocks: Permission denied",,,,,"automatic vacuum of table ""DB.public.TableB""",,,,""

We’ve already attempted to exclude the entire pg folder from antivirus scans, but the problem persists. Any insights or solutions to this issue would be greatly appreciated. Thank you!