Postgresql Check if query is still running

Question

At my work, I needed to build a new join table in a postgresql database that involved doing a lot of computations on two existing tables. The process was supposed to take a long time so I set it up to run over the weekend before I left on Friday. Now, I want to check to see if the query finished or not.

How can I check if an INSERT command has finished yet while not being at the computer I ran it on? (No, I don't know how many rows it was suppose to add.)

Vesta

Check pg_stat_activity – Veloz

Here is an example postgresql.org/message-id/… – Honebein

Answer 1

Select * from pg_stat_activity where state not ilike 'idle%' and query ilike 'insert%'

This will return all non-idle sessions where the query begins with insert, if your query does not show in this list then it is no longer running.

pg_stat_activity doc

Greyhen

Better to use ILIKE (especially since SQL convention is to use uppercase keywords). Also note that not all users will have permission to see the query column associated with other users (it will appear to be NULL), but the OP may still be able to recognise their query by its remote_addr or similar. – Veloz

@Dave, thanks for the syntax correction on the ilike. Very true on all accounts however, if this is the case I am sure the DBA could be asked to check... – Greyhen

Answer 2

You can have a look at the table pg_stat_activity which contains all database connections including active query, owner etc.

At https://gist.github.com/rgreenjr/3637525 there is a copy-able example how such a query could look like.

Assignation

Regarding your idea of counting to track the current status: Depending on your isolation level it may not even possible to see the inserted data before you commit it to the database. – Assignation

It will never be possible to see the inserted data (from a different transaction) before it is committed. Postgres does not allowed dirty reads. – Roos