(materialised) views are created before the tables

[amirtvkli1]

(This issue has been reported in #39. More details have been reported here)

When the database has (materialised) views, setting up the database throws an error because the underlying tables do not exist.

This issue is happening for alembic_utils versions >= 0.2.16 and it gives this error message:

File ".../lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "transaction" does not exist
LINE 26:         FROM transaction
                      ^
....

(Background on this error at: http://sqlalche.me/e/13/f405)

I tested the same schema with versions <= 0.2.15 and it works fine.

More details:

• Does this issue still exist in the latest version alembic_utils==0.5.3?
  Yes, actually for any version >= 0.2.16 this issue exists.

• Does this error when the migration is being generated, or when you attempt to apply the migrations to the database?
  It happens when I run alembic to generate the migrations.

• PostgreSQL version?
  psql (PostgreSQL) 13.2 (Ubuntu 13.2-1.pgdg20.04+1)

• Python version?
  Python 3.8.8

An example to reproduce the error:

Table definition:

class Transaction(Base, Table):
    """Table definition for transaction."""

    whse_code = Column(Integer, nullable=False, primary_key=True)
    tran_date = Column(Date, nullable=False, primary_key=True)
    dist_code = Column(String, nullable=False, primary_key=True)
    requisition_code = Column(String, nullable=False, primary_key=True)

    @classmethod
    def raw_record_lookup(cls, record):
        return {
            "whse_code": record["WHSE_CODE"],
            "tran_date": record["TRAN_DATE"],
            "dist_code": record["DIST_CODE"],
            "requisition_code": record["REQUISITION_CODE"],
        }

Then, in env.py (in alembic folder), I have put:

from pathlib import Path
from alembic_utils.pg_materialized_view import PGMaterializedView

replaceable_entities = [PGMaterializedView.from_path(Path('path/to/my_mat_view.sql'))]
register_entities(replaceable_entities)

where my_mat_view.sql has the SQL definition of the materialized view:

CREATE MATERIALIZED VIEW public.transactions_recent
AS
    SELECT
        transaction.whse_code,
        transaction.tran_date,
        transaction.dist_code,
        transaction.requisition_code
    FROM transaction
    WHERE
        transaction.tran_date >= date_trunc('month'::text, now() - '2 mons'::interval)
        AND transaction.tran_date <= now()
    ORDER BY transaction.tran_date
WITH DATA;

[olirice]

Dupe of #41

please see open PR #43

And provide any feedback you have wrt your usecase