xelixdev / django-pgviews-redux Goto Github PK
View Code? Open in Web Editor NEWFork of django-postgres that focuses on maintaining and improving support for Postgres SQL Views.
License: The Unlicense
Fork of django-postgres that focuses on maintaining and improving support for Postgres SQL Views.
License: The Unlicense
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:
Modify how connection cursors are obtained to make use of the connections dictionary:
Update view creation to refer to the database router's allow_migrate so views are only created within the appropriate database.
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.
OPTIONS
keyword as following:
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
# ...
"OPTIONS": {
"options": "-c search_path=custom_schema_name"
},
}
}
CREATE SCHEMA custom_schema_name;
ALTER SCHEMA custom_schema_name OWNER TO db_username;
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
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.