Upgrading to 0.8.9 with postgres metadata db hangs at 4fa8

[andrewhn]

OS Ubuntu 14.04 LTS server
PG version 9.1
To reproduce

• pip install caravel --upgrade
• caravel db upgrade

Seems to be a similar issue to #238, but requiring a different fix (I tried the analogous). Upgrading to 0.8.9 with a postgres metadata db hangs at running upgrade 4fa8, printing the following line:

INFO [alembic.runtime.migration] Running upgrade b4456560d4f3 -> 4fa88fe24e94, owners_many_to_many

Looking at postgres activity:

select * from pg_stat_activity where datname='caravel';
 datid | datname | procpid | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          | waiting |                     current_query
-------+---------+---------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+--------------------------------------------------------
 55915 | caravel |   25945 |    53556 | caravel |                  | 192.168.0.100 |                 |       41238 | 2016-05-02 11:47:21.07437+10  | 2016-05-02 11:47:21.107635+10 | 2016-05-02 11:47:22.363563+10 | f       | <IDLE> in transaction
 55915 | caravel |   25946 |    53556 | caravel |                  | 192.168.0.100 |                 |       41240 | 2016-05-02 11:47:22.391575+10 | 2016-05-02 11:47:22.426428+10 | 2016-05-02 11:47:32.578778+10 | t       |                                                       +
       |         |         |          |         |                  |               |                 |             |                               |                               |                               |         | CREATE TABLE slice_user (                             +
       |         |         |          |         |                  |               |                 |             |                               |                               |                               |         |         id SERIAL NOT NULL,                           +
       |         |         |          |         |                  |               |                 |             |                               |                               |                               |         |         user_id INTEGER,                              +
       |         |         |          |         |                  |               |                 |             |                               |                               |                               |         |         slice_id INTEGER,                             +
       |         |         |          |         |                  |               |                 |             |                               |                               |                               |         |         PRIMARY KEY (id),                             +
       |         |         |          |         |                  |               |                 |             |                               |                               |                               |         |         FOREIGN KEY(user_id) REFERENCES ab_user (id), +
       |         |         |          |         |                  |               |                 |             |                               |                               |                               |         |         FOREIGN KEY(slice_id) REFERENCES slices (id)  +
       |         |         |          |         |                  |               |                 |             |                               |                               |                               |         | )                                                     +
       |         |         |          |         |                  |               |                 |             |                               |                               |                               |         |                                                       +
       |         |         |          |         |                  |               |                 |             |                               |                               |                               |         |
(2 rows)

[mistercrunch]

Did you try killing your webserver while upgrading?

[andrewhn]

Hi, yeah, I also tried restarting postgres and then the host vms to no avail.

In the end I'm not sure what the problem was -- I could install caravel into a fresh database no problem, so the code seems fine. In the end I took a data-only dump with pg_dump, dropped the database, ran caravel db upgrade on a fresh database, and restored the data.

pg_dump gave a warning that's possibly related to the issue I was having:

xx@xx:~$ sudo -u postgres pg_dump -Fc --data-only caravel > caravel-do.pgdb
pg_dump: NOTICE: there are circular foreign-key constraints among these table(s):
pg_dump:   ab_user
pg_dump: You may not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.

Restoring with sudo -u postgres pg_restore --disable-triggers caravel-do.pgdb -d caravel worked.