Django PGViews Redux

1.2.0 · active · verified Fri Apr 17

django-pgviews-redux is a Django library designed to manage PostgreSQL views, including materialized views, as part of your Django models and migrations. It provides tools to define SQL views in your Django applications, track their schema changes, and integrate them seamlessly into your database lifecycle. The current version is 1.2.0, with an active but irregular release cadence.

Common errors

```
django.db.utils.ProgrammingError: relation "myapp_myview" does not exist
```
cause The PostgreSQL view has not been created in the database, or its definition is incorrect, or migrations have not been applied.

fix Ensure `pgviews` is in `INSTALLED_APPS`, and then run `python manage.py makemigrations myapp` followed by `python manage.py migrate`.
```
django.core.exceptions.ImproperlyConfigured: 'pgviews' must be in your INSTALLED_APPS
```
cause The `pgviews` app has not been added to your Django project's `INSTALLED_APPS` setting.

fix Add `'pgviews'` to the `INSTALLED_APPS` list in your Django `settings.py` file.
```
django.db.utils.ProgrammingError: materialized view "my_app_mymaterializedview" cannot be refreshed while it has open queries
```
cause An attempt was made to refresh a materialized view (`REFRESH MATERIALIZED VIEW`) while there were active queries or locks on it, preventing a non-concurrent refresh.

fix Use the `refresh(concurrently=True)` method for materialized views, which allows refreshing without blocking reads (though it may take longer). Alternatively, schedule refreshes during low-traffic periods or ensure no active queries are running against the view.
```
AttributeError: 'MyPostgresView' object has no attribute 'refresh'
```
cause You are trying to call the `.refresh()` method on a standard `PostgresView` model. This method is only available for models that inherit from `MaterializedView`.

fix Ensure the model you intend to refresh inherits from `MaterializedView` (e.g., `class MyMV(MaterializedView, models.Model):`). Standard `PostgresView` models represent live views and do not need to be refreshed.

Warnings

breaking Version 1.0.0 introduced significant breaking changes, including extensive codebase refactoring, removal of unused code, and conversion of some parameters to keyword-only. Review the v1.0.0 changelog thoroughly before upgrading from pre-1.0 versions.
Fix: Consult the changelog and the latest documentation. Update calls to any affected functions and adjust code that relies on previous internal structures.
breaking Starting with v1.0.0, the default behavior for `MATERIALIZED_VIEWS_CHECK_SQL_CHANGED` was changed from `False` to `True`. This means materialized views are now recreated from scratch when their definition changes, which can lead to data loss or performance issues if not explicitly handled or if you expected incremental updates.
Fix: If you require the old behavior (attempting to detect and apply minimal changes) or have specific data retention requirements, you may need to explicitly set `MATERIALIZED_VIEWS_CHECK_SQL_CHANGED = False` in your Django settings or implement custom migration logic to handle data migration during view recreation.
gotcha When defining views as Django models, you must include a primary key (e.g., `id = models.IntegerField(primary_key=True)`) and set `managed = False` in the model's `Meta` class. Failing to do so will cause Django ORM issues or attempt to create a table instead of a view.
Fix: Ensure your view model includes `id = models.IntegerField(primary_key=True)` (or another suitable primary key) and `class Meta: managed = False`.
gotcha Materialized views are static snapshots of data at the time of their last refresh. They do not automatically update when underlying tables change. You must explicitly call the `refresh()` method or use the `pgviews.functions.refresh_materialized_view` utility.
Fix: Implement a strategy for refreshing materialized views, such as periodic tasks (e.g., using Celery), database triggers, or manual calls via Django management commands, depending on your data freshness requirements.

Install

pip install django-pgviews-redux Install with pip

Imports

PostgresView
```
from pgviews.view import PostgresView
```

MaterializedView

from pgviews.materialized_view import MaterializedView

functions
```
from pgviews import functions
```
Contains utility functions like refresh_materialized_view.

Quickstart

This quickstart demonstrates how to define both a regular and a materialized PostgreSQL view in a Django application. Views are defined as Django models that inherit from `PostgresView` or `MaterializedView` mixins. Remember to set `managed = False` in the `Meta` class and include an `id` field as a primary key. Materialized views require explicit refreshing to update their data.

import os
from django.db import models
from pgviews.view import PostgresView
from pgviews.materialized_view import MaterializedView

# Assuming 'myapp' is an installed Django app
# Add 'pgviews' to INSTALLED_APPS in settings.py:
# INSTALLED_APPS = [
#    ...,
#    'myapp',
#    'pgviews',
# ]

# Define a base model for demonstration
class Author(models.Model):
    name = models.CharField(max_length=100)

    def __str__(self):
        return self.name

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

    def __str__(self):
        return self.title

# Define a regular PostgreSQL View
class AuthorBookCountView(PostgresView, models.Model):
    # The SQL query must define an 'id' field, as Django models require a primary key.
    view_definition = """
        SELECT
            a.id AS id,
            a.name AS author_name,
            COUNT(b.id) AS book_count
        FROM myapp_author a
        LEFT JOIN myapp_book b ON a.id = b.author_id
        GROUP BY a.id, a.name
    """

    id = models.IntegerField(primary_key=True)
    author_name = models.CharField(max_length=100)
    book_count = models.IntegerField()

    class Meta:
        # Important: Django should not manage the creation/deletion of the view itself.
        managed = False
        db_table = 'author_book_count_view' # Explicitly set db_table for clarity

# Define a Materialized PostgreSQL View
class MaterializedAuthorBookCountView(MaterializedView, models.Model):
    view_definition = """
        SELECT
            a.id AS id,
            a.name AS author_name,
            COUNT(b.id) AS book_count
        FROM myapp_author a
        LEFT JOIN myapp_book b ON a.id = b.author_id
        GROUP BY a.id, a.name
    """

    id = models.IntegerField(primary_key=True)
    author_name = models.CharField(max_length=100)
    book_count = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'materialized_author_book_count_view'

    def refresh_data(self):
        self.refresh() # Method provided by MaterializedView mixin

# To use:
# 1. Add 'pgviews' and 'myapp' to INSTALLED_APPS in settings.py.
# 2. python manage.py makemigrations myapp
# 3. python manage.py migrate
# 4. To query: AuthorBookCountView.objects.all()
# 5. To refresh materialized view: MaterializedAuthorBookCountView.objects.first().refresh_data() or MaterializedAuthorBookCountView.objects.refresh()

view raw JSON →