Alembic glitches

[corviday]

Two issues with alembic that shouldn't apply to the main database, but have affected test databases:

---

The initial create migration does not run correctly

• The initialization script installs the postGIS extension. It MUST install the postGIS extension; if the extension is already installed, it quits without doing anything else.
• Only the postgres superuser can install the postGIS extension, so the initialization script must be run with the superuser's credentials
• The superuser does not - unlike the database-associated accounts like ce_meta, ce_meta_ro, or ce_meta_rw - default to the ce_meta schema in its search path
• Therefore, upon running the rest of the creation script, the superuser creates the modelmeta tables in the public schema instead, which is not where we want them

This problem only exists with the first alembic "migration", the one that has to install postGIS; it won't affect upgrading the live database, but it does mean we cannot format a completely empty database easily.

---

The discrete sampling geometry upgrade cannot be downgraded if data has already been added.

The following error message results:

alembic -x db=docker_postgres downgrade -1
### Creating modelmeta ORM
/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 12f290b63791 -> 614911daf883, handle variant sampling geometries
Traceback (most recent call last):
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
psycopg2.IntegrityError: insert or update on table "data_file_variables" violates foreign key constraint "data_file_variables_grid_id_fkey"
DETAIL:  Key (grid_id)=(0) is not present in table "grids".

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/bin/alembic", line 11, in <module>
    sys.exit(main())
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/alembic/config.py", line 486, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/alembic/config.py", line 480, in main
    self.run_cmd(cfg, options)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/alembic/config.py", line 463, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/alembic/command.py", line 294, in downgrade
    script.run_env()
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/alembic/script/base.py", line 427, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/alembic/util/pyfiles.py", line 81, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/alembic/util/compat.py", line 82, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 665, in exec_module
  File "<frozen importlib._bootstrap>", line 222, in _call_with_frames_removed
  File "alembic/env.py", line 118, in <module>
    run_migrations_online()
  File "alembic/env.py", line 112, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/alembic/runtime/environment.py", line 836, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/alembic/runtime/migration.py", line 330, in run_migrations
    step.migration_fn(**kw)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/alembic/versions/12f290b63791_handle_variant_sampling_geometries.py", line 197, in downgrade
    ['grid_id'], ['grid_id'])
  File "/usr/lib/python3.5/contextlib.py", line 66, in __exit__
    next(self.gen)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/alembic/operations/base.py", line 300, in batch_alter_table
    impl.flush()
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/alembic/operations/batch.py", line 53, in flush
    fn(*arg, **kw)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/alembic/ddl/impl.py", line 177, in add_constraint
    self._exec(schema.AddConstraint(const))
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/alembic/ddl/impl.py", line 115, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1009, in _execute_ddl
    compiled
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/lzeman/Code/modelmeta-newdb/modelmeta/venv/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) insert or update on table "data_file_variables" violates foreign key constraint "data_file_variables_grid_id_fkey"
DETAIL:  Key (grid_id)=(0) is not present in table "grids".
 [SQL: 'ALTER TABLE data_file_variables ADD CONSTRAINT data_file_variables_grid_id_fkey FOREIGN KEY(grid_id) REFERENCES grids (grid_id)'] (Background on this error at: http://sqlalche.me/e/gkpj)

There is no grid_id 0 in this database; the grid_ids start at 1. No entries in any other table reference grid_id 0, either, I'm not sure why it's trying to access grid_id 0 at all.

Since we don't expect to downgrade the live database, this is unlikely to be a problem, but it does come up in testing.

[rod-glover]

• The initialization script installs the postGIS extension. It MUST install the postGIS extension; if the extension is already installed, it quits without doing anything else.

• This can probably be fixed by changing the create command to CREATE EXTENSION IF NOT EXISTS ...

• See also #68

• Only the postgres superuser can install the postGIS extension, so the initialization script must be run with the superuser's credentials

• Add to notes somewhere.

• The superuser does not - unlike the database-associated accounts like ce_meta, ce_meta_ro, or ce_meta_rw - default to the ce_meta schema in its search path

• Therefore, upon running the rest of the creation script, the superuser creates the modelmeta tables in the public schema instead, which is not where we want them

• Make superuser (role postgres) default to the appropriate schema. E.g., in my dockerized Postgres setup, I have:

  ALTER ROLE postgres IN DATABASE mm_test SET search_path = mm_test, public;

• We can hand update the ce_meta and pcic_meta databases similarly.

[jameshiebert]

Since these are two separate issues. Let's fix the former now, and defer the latter to another issue.