From: Lars Aksel Opsahl <lars.ops...@nibio.no> >From: Laurenz Albe <laurenz.a...@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
Hi I now tested running the master (Orchestration) code as user joe. In the master code I connect back as user lop and creates the test table test_null and inserts data in many tiny operations. User joe who has the long running operation does not know anything about table test_null and does not have any grants to that table. The table test_null is not granted to public either. The problem is the same, the long running transaction to joe will kill the performance on a table which user joe does not have any access to or know anything about . If this is expected behavior it means that any user on the database that writes a long running sql that does not even insert any data can kill performance for any other user in the database. So applications like QGIS who seems to keep open connections for a while can then also kill the performance for any other user in the data. Having postgresql working like this also makes it very difficult to debug performance issues because a problem may just have been a side effect of a not related sql. So I hope this is not the case and that I have done something wrong or that there are some parameters that can be adjusted on get around this problem. Thanks Lars
