Missing pg_locks and pg_stat_activity tables while trying to diagnose ActiveRecord::ConcurrentMigrationError

Question

I had a stalled Rails migration, so I ctrl-C'ed out of it and tried to re-run it. I got the following error:

ActiveRecord::ConcurrentMigrationError: 

Cannot run migrations because another migration process is currently running.

I tried the solution in this post already. I ran the following command, but it produced no output:

richiethomas=# \c re_app_development
psql (9.6.11, server 11.2)
WARNING: psql major version 9.6, server major version 11.
         Some psql features might not work.
You are now connected to database "re_app_development" as user "richiethomas".

re_app_development=# SELECT DISTINCT age(now(), query_start) AS age, pg_stat_activity.pid,pg_locks.granted,pg_stat_activity.application_name,pg_stat_activity.backend_start, pg_stat_activity.xact_start, pg_stat_activity.state_change, pg_stat_activity.waiting, pg_stat_activity.state, pg_stat_activity.query_start, left(pg_stat_activity.query, 60)
re_app_development-#     FROM pg_stat_activity, pg_locks
re_app_development-#     WHERE pg_locks.pid = pg_stat_activity.pid
re_app_development-# 

I was confused why there was no output, so I started by looking for the pg_locks or pg_stat_activity tables. Below are all the tables in my database (note that I re-arranged the order of the tables slightly, to group tables that I created into the bottom group, and tables generated by Rails or my gems into the first group):

re_app_development-# \dt
                   List of relations
 Schema |          Name          | Type  |    Owner     
--------+------------------------+-------+--------------
 public | ar_internal_metadata   | table | richiethomas
 public | flipper_features       | table | richiethomas
 public | flipper_gates          | table | richiethomas
 public | schema_migrations      | table | richiethomas

 public | reports                | table | richiethomas
 public | users                  | table | richiethomas
 public | categories             | table | richiethomas
 public | responses              | table | richiethomas
 public | zip_code_hpis          | table | richiethomas
 public | zip_codes              | table | richiethomas
(13 rows)

As you can see, the two tables in the above query are missing.

The only thing I can figure is that the warning when choosing the database is relevant here:

WARNING: psql major version 9.6, server major version 11.
         Some psql features might not work.

My question is- where can I find these tables, so that I can remove the lock and re-run my migration?

Answer 1

pg_activity and pg_locks are "views". Therefore \dt will not include them.

to get all your view names you could run

select table_name from INFORMATION_SCHEMA.views;

Which would return something like

 // more views
 pg_locks
 pg_available_extensions
 pg_available_extension_versions
 pg_prepared_xacts
 pg_prepared_statements
 pg_seclabels
 pg_statio_sys_tables
 pg_timezone_abbrevs
 pg_timezone_names
 pg_statio_user_tables
 pg_stat_all_tables
 pg_stat_xact_all_tables
 pg_stat_sys_tables
 pg_stat_xact_sys_tables
 pg_stat_user_tables
 pg_stat_xact_user_tables
 pg_statio_all_tables
 pg_stat_all_indexes
 pg_stat_sys_indexes
 pg_stat_user_indexes
 pg_statio_all_indexes
 pg_statio_sys_indexes
 pg_statio_user_indexes
 pg_statio_all_sequences
 pg_statio_sys_sequences
 pg_statio_user_sequences
 pg_stat_activity
 // more views

Of course, this list may look different depending on your psql version.