How to track query progress in PostgreSQL?

Question

Is there a plugin or script that can track the progress of a long query in PostgreSQL?

I mean, I need to set the value of the execution line in Java, which is associated with some update request in Postgres. I search through the Internet, but I just found a document that has no official implementation in any RDBMS system.

Answer 1

I found a good answer here: Tracking update instruction execution

The trick is to first create a sequence (name it whatever you want):

CREATE SEQUENCE query_progress START 1; 

Then add the WHERE part to your query:

 AND NEXTVAL('query_progress')!=0 

Now you can request progress:

 SELECT NEXTVAL('query_progress'); 

Finally, be sure to get rid of the sequence:

 DROP SEQUENCE query_progress; 

Note that this is likely to make your request run even slower, and each time you check the progress, it will increment the value further. The link above suggests creating a time sequence, but PostgreSQL does not seem to make them visible in the sessions.

Answer 2

Not. Unable to track live request flow. Theoretically, the system could compare the progress at the highest level compared to the query plan and emit some percentage indication. In practice, I doubt that it would be terribly accurate, and I doubt that the impact of performance would be beneficial.

Answer 3

You can add the update_time column to the table by holding the value of the last update. If you somehow know which records should be affected, you can also set their update_time to the current time, and when you check the progress and know the number of rows affected, you can choose the number of records that were affected by those t20> newer than the time when you started the update. The number of affected lines that have a "new" update_time / number of records to update * 100 gives the percentage of progress.