12

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.)

Thomas
  • 871
  • 2
  • 8
  • 21

2 Answers2

15
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

jkdba
  • 2,378
  • 3
  • 23
  • 33
  • 1
    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. – Dave Feb 28 '16 at 17:41
  • @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... – jkdba Feb 28 '16 at 17:56
3

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.

Martin Prebio
  • 103
  • 7
  • Regarding your idea of counting to track the current status: Depending on your [isolation level](http://www.postgresql.org/docs/current/static/transaction-iso.html) it may not even possible to see the inserted data before you commit it to the database. – Martin Prebio Feb 28 '16 at 17:42
  • 1
    It will **never** be possible to see the inserted data (from a different transaction) before it is committed. Postgres does not allowed dirty reads. –  Feb 28 '16 at 17:56