The latest minor version of Django (4.2) support psycopg3 (installed as just psycopg). There is currently a hard-coded import of v2 of this library which is prevent us from upgrading.

I will submit a PR.

I'm not sure if this is expected behaviour - but I'm looking to use this code in dockerised dev environment, and a heroku production environment.

I've noticed that calling ./manage sync_pgviews. upon deployment result in a recreation of all views.. This is going to be problematic...

I did some digging and it looks like the code for checking if a materialized view exists no longer works
This query works...

cursor.execute( "SELECT COUNT(*) FROM pg_class WHERE relkind = %s and relname = %s;", [relkind, vname], )
However, the default behviour of sync_pgviews is then exposed as being "update" by default... So I didn't pursue it any further...

Not sure if this information is useful to you, but I thought I'd pass it on....

(I'm probably thinking I'll go make a manual migration to call the create view code as my solution)

Projects using multiple databases cannot use django-pgviews outside of the default database.

I'm using django-pgviews on a project connected to two databases. In our case, the default database is quite small, while the 2nd database is large and could make valuable use of materialized views.

To support multiple databases, I think the following modifications need to be made:

1. Modify how connection cursors are obtained to make use of the connections dictionary:

   https://github.com/mikicz/django-pgviews/blob/269c17f3e08c76a7cb971afcf2241ee76858589c/django_pgviews/view.py#L176

   https://github.com/mikicz/django-pgviews/blob/269c17f3e08c76a7cb971afcf2241ee76858589c/django_pgviews/view.py#L441

2. Update view creation to refer to the database router's allow_migrate so views are only created within the appropriate database.

3. Update management commands to support running against specific databases, as migrate works. For example:

   python manage.py sync_pgviews  # default db
   python manage.py sync_pgviews --database=customers

In the end, the majority of users using a single database shouldn't notice any differences and everything will continue to function as normal 🤞

I have a proof of concept currently working on a fork. Would there be any interest in possibly incorporating these changes if I can put together a PR?

Thanks for maintaining this fork! I was searching for Django wrappers for Views and only found this maintained repo after an exhaustive search; I had found the base fork a long while before.

As that base is no longer maintained, I suspect if you make this repo its own new repo it will more easily show up in search results.

Hello.

When the schema name is defined using the OPTIONS keyword in the database settings, the sql that verifies if the view exists for a specific schema returns always 0 (sql here) because the function _schema_and_name returns public instead of the right schema name.

Environment

• Python 3.10;
• Django 4.1;
• Django-pgviews 0.8.4.

How to reproduce

• Configure the database with the OPTIONS keyword as following:

  DATABASES = {
      "default": {
          "ENGINE": "django.db.backends.postgresql",
          # ...
          "OPTIONS": {
              "options": "-c search_path=custom_schema_name"
          },
      }
  }

• Make sure that the specified schema exists and the db user has permission:

  CREATE SCHEMA custom_schema_name;
  ALTER SCHEMA custom_schema_name OWNER TO db_username;

• Execute python manage.py sync_pgviews or python manage.py migrate. Is expected an traceback like this:

  ...
  
    File "./venv/lib/python3.10/site-packages/django_pgviews/view.py", line 232, in create_materialized_view
      _create_mat_view(cursor, view_name, query, view_query.params, with_data=view_cls.with_data)
    File "./venv/lib/python3.10/site-packages/django_pgviews/view.py", line 84, in _create_mat_view
      cursor.execute(
  psycopg2.errors.DuplicateTable: relation "app_view" already exists
  

Fix suggestion

This is only a suggestion and I can create a pull request if you like.

django_pgviews/view.py

# ...
RE_DB_SEARCH_PATH = re.compile(r'search_path=(?P<s>[\'"]?)(?P<sp>\w+)(?P=s)')
# ...

def _schema_and_name(connection, view_name):
    if "." in view_name:
        return view_name.split(".", 1)

    try:
        schema_name = connection.schema_name
    except AttributeError:
        search_path = RE_DB_SEARCH_PATH.search(connection.settings_dict.get('OPTIONS', {}).get('options', ''))
        if search_path:
            schema_name = search_path.group('sp')
        else:
            with connection.cursor() as cursor:
                cursor.execute("SELECT current_schema;")
                schema_name = cursor.fetchone()[0]
    return schema_name, view_name

If you have materialized views that depend on one another, they are created in the correct order, but when you call .refresh() the caller needs to take care to refresh all dependent views or risk working with stale data.

Only logs this, should raise an error

WARNING  django_pgviews.view:view.py:474 Failed to find connection to refresh <class 'XXX'>

Are there any plans for supporting creating materialized views using the pg_ivm extension?

Thanks for maintaining this extension!

Hi! I have a model that my views depend on, and when I try to change that model and migrate it, I get

django.db.utils.NotSupportedError: cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on materialized view some_view depends on column "some_column"

The problem could be avoided if the views will be dropped before the migration, and recreated after.

When defining a materialized view like this:

class MyMatView(pg.MaterializedView):
    sql = lazy(get_view_sql, str)()

    record_id = models.IntegerField()
    mod_date = models.DateTimeField()
    # <snip>

    class Meta:
        db_table = "reporting.my_table"
        managed = False
        indexes = [
            models.Index(fields=["record_id"]),
        ]

I receive the following exception when running migrate:

Traceback (most recent call last):
  File "<snip>/manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "<snip>/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "<snip>/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "<snip>/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "<snip>/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "<snip>/django/core/management/base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "<snip>/django/core/management/commands/migrate.py", line 268, in handle
    emit_post_migrate_signal(
  File "<snip>/django/core/management/sql.py", line 42, in emit_post_migrate_signal
    models.signals.post_migrate.send(
  File "<snip>/django/dispatch/dispatcher.py", line 180, in send
    return [
  File "<snip>/django/dispatch/dispatcher.py", line 181, in <listcomp>
    (receiver, receiver(signal=self, sender=sender, **named))
  File "<snip>/django_pgviews/apps.py", line 34, in sync_pgviews
    vs.run(
  File "<snip>/django_pgviews/models.py", line 41, in run
    if super().run(
  File "<snip>/django_pgviews/models.py", line 27, in run
    backlog = self.run_backlog(backlog, **kwargs)
  File "<snip>/django_pgviews/models.py", line 77, in run_backlog
    status = create_materialized_view(
  File "/usr/lib/python3.9/contextlib.py", line 79, in inner
    return func(*args, **kwds)
  File "<snip>/django_pgviews/view.py", line 224, in create_materialized_view
    schema_editor.add_index(view_cls, index)
  File "<snip>/django/db/backends/postgresql/schema.py", line 218, in add_index
    self.execute(index.create_sql(model, self, concurrently=concurrently), params=None)
  File "<snip>/django/db/backends/base/schema.py", line 145, in execute
    cursor.execute(sql, params)
  File "<snip>/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "<snip>/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "<snip>/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "<snip>/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "<snip>/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "<snip>/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: relation "reporting.my_table" does not exist

Removing the schema from db_table allows migrate to complete successfully.

Would it be possible to use this lib with MySQL?

Thank you

When a view or a materialized view is deleted from a project, the view/materialized view will continue existing in the project database, as this project doesn't automatically drop the view. There should be a migration added automatically when a view/materialized view is removed which would drop the view (if exists).

It would be much easier to manage these views if the sql field on a pg.View model could be set to a QuerySet. We manage a lot of calculations and filters in QuerySet filter and annotate functions. Recreating these all in SQL is hard to maintain.

Is there any way to utilize QuerySet functions in a pg.View model to set the sql?

Add an option to refresh materialized views with an atomic swap. When the refresh starts, it would create a new materialized view (with some temp name), and when it is created, it would run an atomic deletion of the old materialized view, and renaming of the new view to the correct name.

This could reduce the number of vacuums PostgreSQL is doing. It may also allow the view to be created without using the concurrent flag, which should be faster.