mypebble / django-pgviews Goto Github PK

Hi, I'd like to propose a switch to travis-ci.
I think thee is an advantage when it comes to the support of multiple python and Django versions.
Travis-CI has a more advanced build matrix.

I'd be willing to set it up. As a plus I could even do a full 'django-cc' integration.

Let me know if you're interested and I'll open up a PR.

Add a switch to allow creating the table itself without data.

This is supported via https://www.postgresql.org/docs/12/sql-creatematerializedview.html

I.e. CREATE MATERIALIZED VIEW table_name AS query WITH NO DATA

You cannot define a view which uses another view and reliably get the sync_pgviews command to work correctly.

Proposed solution:

class MyView(View):
    class Meta:
         dependencies = ('my_app.View', 'my_app.Otherview',)

I am using Postgresql as database in my django project. I am using materialised views also using django-pgviews which is django module for using materialised views in django.

To refreshing materialised views I use command below

python manage.py sync_pgviews

But now I am using multidb in my django project. Which is easily manageable like

python manage.py migrate # This will apply sql to default database
python manage.py migrate --database=tableau # This will apply sql to tableau database

I was expecting --database=tableau option to work with python manage.py sync_pgviews also. But this does not work.

python manage.py sync_pgviews # refresh default db views
python manage.py sync_pgviews --database=tableau # This does not work

How can I refresh materialised view of other database using django using manage.py?

Receive the following message when I run pip install django-pgviews

Downloading django-pgviews-0.5.1.tar.gz
Complete output from command python setup.py egg_info:
Traceback (most recent call last):
File "", line 1, in
File "/tmp/pip-build-FEpTji/django-pgviews/setup.py", line 9, in
LONG_DESCRIPTION = open('README.md').read()
IOError: [Errno 2] No such file or directory: u'README.md'

Running pip install "django-pgviews<0.5.1" installs the library correctly.

I have created some materialized views using view.MaterializedView as a base class. I find that every time I run migration, the views are recreated -- dropped and created again.

Is there some way to disable this behaviour?

Hi ,
Can any one provide me how to get the data from the views after creation of the views in python?

When I use a string Projection and try to filter on a field my view should have, I get this error FieldError: cannot resolve keyword 'x' into field. Choices are: id.

After a quick investigation in the ViewMeta, it seems the fields from my real model are not being copied immediately and are put in a deferred situation because they are strings. The thing is, as they are deferred, the super new is called without them being attributed and that apparently causes django to miss them. I then only get one field on my view in the end, which is id.

As the issue was with the projection being a string, I tried putting projection = MyModel._meta.fields but got an error with reverse name clashes on the foreign keys.

Do you have any solution for this problem ?
I don't want to specify every field on my view especially since they are the same.

We would really like to support Django 3 in out app but use django-pgviews quite alot and it uses on six which is now removed. From what I can tell the only usages are in views.py and I tried working on a PR but I do not quite understand how the six.with_metaclass function works and how to replace it.

If anyone is able to advise me I would be happy to submit a PR.

You are using projection = ['myapp.Customer.*',] in the example, but I can't find any reference to what it does or how it is to be used.

Currently sync_pgviews will lock all transactions using a given materialized view while it's being recreated.

I think it would be better to first create the materialized view under a different name, and only then drop the old one and rename the new one. Or even better, have an option to use migration scripts for managing the views.

In the README, we should clarify how ForeignKey relationships work in the context of SQL Views.

When use django-pgviews==0.5.7 with Django 4 raise thise error:

  File ".../lib/python3.8/site-packages/django_pgviews/signals.py", line 4, in <module>
    view_synced = Signal(
TypeError: __init__() got an unexpected keyword argument 'providing_args'

The scenario is out of the box. When model is get created it cannot release projections and raises AppRegistryNotReady.

(see django-pgviews/view.py line 214)

Maybe I missed something on how to defer loading until registry is get populated...

For this particular project I use Django ver. 2.0
Based on documentation it is not supported, but I'd be glad to contribute myself. Just need some input on direction to go.

Thank you

Is there any way to have custom indices on materialized views, except for the one used for concurrent update?

If not, would it be possible to at least get a signal after the view is created (but still in the same transaction)? Then we'd be able to build additional indices in the same transaction.

Hey guys,

I have a issue when test materialized views with pytest.

Basically, when I refresh the materialized views by endpoint works, but when I refresh with tests, don't works.

The refresh method works, but don't update datas on materialized view.

Thank you!

it should be :

from django_pgviews import view as pg

and not " import django_postgres.view as pg"

People somehow maintain packages working both with python 2 and python 3. In this case I think it will be extremely easy – looks like the only inconsistent thing is except Exception, e notation. Can I help you with this?

We need to use Django 1.7's post_migrate hooks because call_command('sync_pgviews') in tests makes them far too slow.

I have a materialized view that is a cached set of results from a few database tables.

I see that you have an option for a model = ForeignKey() field in the read only MV, but I can't quite use that...unless you're just calling it "model" to make it simple to look at, instead of being a hook the app knows about.

Basically, when I call MyMV.objects.count(), I get an error that the relation doesn't exist. I have managed=True, abstract=False, and I have the db_name set correctly. Calling refresh on the class in ipython does seem to work (takes awhile to finish anyway).

Any ideas?

Hi,
we saw that there is an issue with syncing views, in case you change views that are dependencies for other views:

How to reproduce the error

1. create 2 Views like this

class Test1View(django_pgviews.View):
    sql = "SELECT last_name, email from auth_user;"

    class Meta:
        db_table = 'myapp_test1view'
        managed = False


class Test2View(django_pgviews.View):
    dependencies = [
        'myapp.Test1View',
    ]
    sql = "SELECT email from common_test1view;"

    class Meta:
        db_table = 'common_test2view'
        managed = False

2. run manage.py sync_pgviews --force

3. change the sql of Test1View to be SELECT last_name, first_name, email from auth_user;

4. run manage.py sync_pgviews --force
   --> You will encounter the following error during sync:
   psycopg2.InternalError: cannot drop view myapp_test1view because other objects depend on it

I will try to come up with a fix for this, unless you know another way how to get around this error.

I'm trying to update an object in Django Admin but I'm getting an error

DETAIL:  View columns that are not columns of their base relation are not updatable.

wanted to enquire if the package allows update.

It's not clear on the README that views behave like querysets. Also, it's not specified which queryset methods they support.

ProgrammingError: relation "misuper_stockitemmaterializedview" does not exist
LINE 1: ...ct"."unit_stock", "misuper_product"."colors" FROM "misuper_s...

The error is random, because from 10.000 request, only 3-4 fails with that error in the endpoint. Any reason for that?

Hi,

Maybe this is a dumb question, but is it possible to define a pg.View with a ForeignKey in its projection? The reason I ask, is its natural given an model with an IP address in a model (and postgres) to say something like:

select v.*,o.id AS organisation_id from data_visitor v LEFT OUTER JOIN v.source_ip <<= data_network n LEFT OUTER JOIN data_organisation o ON n.organisation_id = o.id

and then say:
class VisitorOrganisation(pg.View):
projection = ['Visitor.*', models.ForeignKey(...)]
sql = ...

but this fails for me under django v1.8 - models.IntegerField in place of the foreign key is an ugly hack, but it works. Perhaps there is a better way to achieve the ability to identify an organisation for a given visitor's IP address using the postgres specific SQL?

I am trying to use a MaterializedView to hold some aggregated queries from one of my model tables. Everything seems fine, however when I try to use the result like a queryset calling MyView.objects.all() I get the error column per_machine.id does not exist. It seems that django expects an id column to always be present but that unlike a normal model, and AutoField isn't assigned by default.
Is this just the expected behaviour and if so is the intention that one should specify an explicit id column or am I simply misusing the resulting queryset by calling objects.all()?

Django pgviews current fails to work with multiple databases. The issue is the connection get called with the default database but not the alternative one. To fix that I added a meta attribute 'using' then check that in the view sync runbacklog function
https://github.com/atkawa7/django-pgviews/commit/f73ca72cfa4ff3d3a038f5cbff28cce75478bd47
https://github.com/atkawa7/django-pgviews/commit/b6b45b65202ad0f7feeb02edf72f5e388ae8fa46

Please make it possible to create materialized view without data by using WITH NO DATA parameter (optional).
https://www.postgresql.org/docs/9.5/sql-creatematerializedview.html

WITH [ NO ] DATA
This clause specifies whether or not the materialized view should be populated at creation time. If not, the materialized view will be flagged as unscannable and cannot be queried until REFRESH MATERIALIZED VIEW is used.

Another extremely simple to create feature: am I right that it will take only to add MATERIALIZED into View creation syntax?

We need to add some docs so long-running applications can be upgraded cleanly.

The fix is exactly the same as the one outlined by Django MQTT: https://django-mptt.github.io/django-mptt/upgrade.html#id1

django-pgviews always assumes that there will be only one schema public and that all the tables/views will belong to it. When using multiple schemas, see: https://github.com/bernardopires/django-tenant-schemas -- views that belong to a tenant fail on App.ready().

Traceback (most recent call last):
  File "/var/www/student-platform/student-platform/manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.6/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.6/site-packages/tenant_schemas/management/commands/migrate_schemas.py", line 42, in handle
    executor.run_migrations(tenants=[self.schema_name])
  File "/usr/local/lib/python3.6/site-packages/tenant_schemas/migration_executors/base.py", line 58, in run_migrations
    run_migrations(self.args, self.options, self.codename, public_schema_name)
  File "/usr/local/lib/python3.6/site-packages/tenant_schemas/migration_executors/base.py", line 31, in run_migrations
    MigrateCommand(stdout=stdout, stderr=stderr).execute(*args, **options)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 257, in handle
    self.verbosity, self.interactive, connection.alias, apps=post_migrate_apps, plan=plan,
  File "/usr/local/lib/python3.6/site-packages/django/core/management/sql.py", line 51, in emit_post_migrate_signal
    **kwargs
  File "/usr/local/lib/python3.6/site-packages/django/dispatch/dispatcher.py", line 175, in send
    for receiver in self._live_receivers(sender)
  File "/usr/local/lib/python3.6/site-packages/django/dispatch/dispatcher.py", line 175, in <listcomp>
    for receiver in self._live_receivers(sender)
  File "/var/www/student-platform/student-platform/django_pgviews/apps.py", line 29, in sync_pgviews
    vs.run(force=True, update=True)
  File "/var/www/student-platform/student-platform/django_pgviews/models.py", line 25, in run
    backlog = self.run_backlog(backlog, force, update)
  File "/var/www/student-platform/student-platform/django_pgviews/models.py", line 56, in run_backlog
    index=view_cls._concurrent_index)
  File "/usr/local/lib/python3.6/contextlib.py", line 52, in inner
    return func(*args, **kwds)
  File "/var/www/student-platform/student-platform/django_pgviews/view.py", line 121, in create_view
    cursor.execute('CREATE OR REPLACE VIEW {0} AS {1};'.format(view_name, view_query))
psycopg2.ProgrammingError: relation "xxxxxxxxx" does not exist
LINE 11:           FROM xxxxxxxxx

I have the library installed, and I'm running django 1.9 and python3.5

Here is the error I get on startup:
ImportError: No module named 'django_pgviews'
It is listed in my installed_apps.
I'm running version 0.1.0.
Ideas?