From: Laurenz Albe <laurenz.albe@cybertec.at>
>
>It is not entirely clear what you are doing, but it seems like you are holding
>a database transaction open, and yes, then it is expected behavior that
>VACUUM cannot clean up dead rows in the table.
>
>Make sure that your database transactions are short.
>Don't use table or row locks to synchronize application threads.
>What you could use to synchronize your application threads are advisory locks,
>they are not tied to a database transaction.
>
Hi
The details are here at https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/issues/67#note_1779300212 and
here is also a ref. to this test script that shows problem https://gitlab.com/nibioopensource/resolve-overlap-and-gap/uploads/9a0988b50f05386ec9d91d6600bb03ec/test_issue_67.sql
I am not doing any locks I just do plain CRUD operations .
The key is that the master code is not creating any table or insert rows that is done by many short operations as you suggested.
But even if the master code is not doing any operations against the test table it's blocking removal of dead rows.
If this expected behavior, it's means that any long running transactions will block for removal of any dead rows for all visible tables in the database and that seems like problem or weakness of Postgresql.
While writing this I now was thinking maybe I can get around problem by not making the table not visible by the master code but that makes it very complicated for mee.
Thanks.
Lars