m1ha-shvn/django-pg-returning

A small library implementing PostgreSQL ability to return rows in DML statements for Django.

django-pg-returning's Issues

manager.py should not use hardcoded 'id'

line 42 and 172 should use 'pk' instead of 'id' I think. I am trying to verify this. I'll post updates when I do.

Add bulk_create_returning

When you, for example, using F() or passing datetime TZ aware string to DB - you don't know resulting values and have to make an additional SELECT call without RETURNING feature

update_returning падает если в фильтр передаётся пустой набор

Пример вызова:

Model.objects.filter(id__in=set(), active=False).update_returning(active=True)

Часть трейсбека, непосредственно с момента вызова update_returning:

 update_returning(active=True)
 File "/home/vagrant/venv/lib/python3.6/site-packages/django_pg_returning/manager.py", line 86, in update_returning
   return self._get_returning_qs(sql.UpdateQuery, **updates)
 File "/home/vagrant/venv/lib/python3.6/site-packages/django_pg_returning/manager.py", line 73, in _get_returning_qs
   query_sql, query_params = query.get_compiler(self.db).as_sql()
 File "/home/vagrant/venv/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1367, in as_sql
   where, params = self.compile(self.query.where)
 File "/home/vagrant/venv/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 390, in compile
   sql, params = node.as_sql(self, self.connection)
 File "/home/vagrant/venv/lib/python3.6/site-packages/django/db/models/sql/where.py", line 99, in as_sql
   raise EmptyResultSet

values_list optional parameters have wrong types

values_list optional parameters:

result.values_list(pk_name, flat=True)

PyCharm writes expected type dict bool instead on flat

`_get_returning_qs` does not fetch foreign keys in the RETURNING statement.

Hi,

Thanks for the amazing library!

I spend some time looking into a strange N+1 issue on our production system. We have a model defining like this:

class Subscription(TimeStampedModel, SoftDeletableModel, UpdateReturningModel):
    member = ForeignKey(
        settings.AUTH_USER_MODEL,
        related_name="subscriptions",
        on_delete=CASCADE,
    )
    unread_count = IntegerField()

We apply this code to make bulk update counters of many users:

def save_unread_counters(users, counter=1):
    return dict(
        models.Subscription.objects.filter(member__in=users)
        .update_returning(unread_count=F("unread_count") + counter)
        .values_list("member_id", "unread_count")
    )

It will generate following SQL queries:

UPDATE "subscription"
SET "unread_count" = ("subscription"."unread_count" + 1)
WHERE ("subscription"."is_removed" = FALSE
       AND "subscription"."member_id" IN (1518, 1519))
RETURNING "id", "created", "modified", "is_removed", "unread_count"

SELECT "subscription"."id",
       "subscription"."member_id"
FROM "subscription"
WHERE "subscription"."id" = 1515

SELECT "subscription"."id",
       "subscription"."member_id"
FROM "subscription"
WHERE "subscription"."id" = 1516

Looks like _get_returning_qs manager method does not take foreign keys into account when it build field argument.

In the values_list method of the ReturningQuerySet have this line https://github.com/M1hacka/django-pg-returning/blob/d615ee52311fa573d528740a5f1136ddd92585ff/src/django_pg_returning/queryset.py#L116
Where getattr of the member_id load it from the database each time.

Regards, Artem.

Updating query_set with ManyToOneRel fails

If model has ManyToOneRel and only() is not used, error is raiesed:

App.objects.filter(pk=100500500500).update_returning(active=False)

Traceback:

Traceback (most recent call last):
  File "/usr/lib/python3.6/code.py", line 91, in runcode
    exec(code, self.locals)
  File "<input>", line 1, in <module>
  File "/home/sergei/venv36/lib/python3.6/site-packages/django_pg_returning/manager.py", line 83, in update_returning
    return self._get_returning_qs(sql.UpdateQuery, **updates)
  File "/home/sergei/venv36/lib/python3.6/site-packages/django_pg_returning/manager.py", line 51, in _get_returning_qs
    field_str = ', '.join('"%s"' % str(f.column) for f in fields[self.model])
  File "/home/sergei/venv36/lib/python3.6/site-packages/django_pg_returning/manager.py", line 51, in <genexpr>
    field_str = ', '.join('"%s"' % str(f.column) for f in fields[self.model])
AttributeError: 'ManyToOneRel' object has no attribute 'column'

Broken import for EmptyResultSet on Django 3.1

Installing the package and importing the UpdateReturningManager is broken on Django 3.1

It raises

cannot import name 'EmptyResultSet' from 'django.db.models.query' (/Users/rvinzent/invitae/lab-entity-service/env/lib/python3.8/site-packages/django/db/models/query.py)

EmptyResultSet appears to have moved to django.core.exceptions in Django 3.1

Support model instance save?

Hi! Thanks for an awesome Django extension!

Let's consider we have code like this:

community = Community.objects.get(pk=1)
community.post_number = F("post_number") + 1
community.save(update_fields=["post_number"])

community.refresh_from_db() # we need this to fetch result of the F + 1 expression.

I propose to include a ModelMixin with returning_save method.
It will reset model instance attributes from the database result. It should use fields mentioned in the update_fields or all of them.

The code above will be rewritten like this without extra DB query:

community = Community.objects.get(pk=1)
community.post_number = F("post_number") + 1
community.returning_save(update_fields=["post_number"])

Best regards,
Artem.

save_returning() should also work when creating records

Feature Request

Calling save_returning() only seems to work when the save performs an update. When the save performs an insert the database generated value is not returned.

works:

    instance = MyModel.objects.create(
        name="test",
        description="a test with a database generated barcode",
        barcode="something fake",
    )
    instance.barcode = Concat(Value("TE"), Value("123"))
    instance.save_returning()

does not work:

    instance = MyModel(
        name="test",
        description="a test with a database generated barcode",
        barcode=Concat(Value("TE"), Value("123")),
    )
    instance.save_returning()

A useful extension of this might be to add create_returning() method analogous to update_returning() except performing an INSERT.

Compatibility with django 4.0+

From django-clickhouse issue.

Incompatible with django 4.0+

Error
Traceback (most recent call last):
  File "/opt/project/tests/test_models.py", line 235, in test_bulk_create_returning
    items = self.django_model.objects.bulk_create_returning(items)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django_pg_returning/manager.py", line 204, in bulk_create_returning
    result = self.bulk_create(objs, batch_size=batch_size)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/project/src/django_clickhouse/models.py", line 101, in bulk_create
    objs = super().bulk_create(objs, batch_size=batch_size)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 816, in bulk_create
    returned_columns = self._batched_insert(
                       ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/models/query.py", line 1817, in _batched_insert
    self._insert(
  File "/usr/local/lib/python3.11/site-packages/django_pg_returning/manager.py", line 37, in _insert
    return_fields = self._get_fields(ignore_deferred=(django.VERSION < (1, 10)))
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django_pg_returning/manager.py", line 82, in _get_fields
    self.query.deferred_to_data(fields, self._get_loaded_field_cb)
TypeError: Query.deferred_to_data() takes 2 positional arguments but 3 were given

delete_returning doesn't delete the foreign/one-to-one keys

Environment:

Python=3.8
django=3.1
djangorestframework=3.11.1
django-pg-returning=1.3.0

How to reproduce this error:

Create any model. Add One-to-one field for this model in another model.
Try to delete any record from the parent model.
It throws an Integrity error.

My Django View(which causes the error):

@api_view(['POST', ])
@permission_classes([IsAuthenticated, ])
@authentication_classes([SafeJWTAuthentication, ])
def delete_one_or_more_jobs(request):
    """Deletes one or more jobs"""

    if not is_list_of_strings(request.data):
        raise exceptions.ParseError("This API requires a list of one or more Job IDs in the request body.")

    for id in request.data:
        uuid_validation(id)

    tenant = tenant_getter_v2(request, qp=True)

    affected_jobs = Job.objects.filter(tenant=tenant, id__in=request.data).delete_returning()
    affected_jobs = affected_jobs.values_list('id', flat=True)
    return Response(affected_jobs)

Traceback:

Internal Server Error: /api/v1/job/delete/
app_1            | Traceback (most recent call last):
app_1            |   File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 242, in _commitapp_1            |     return self.connection.commit()
app_1            | psycopg2.errors.ForeignKeyViolation: update or delete on table "job" violates foreign key constraint "job_pipeline_job_id_d60ec84d_fk_job_id" on table "job_pipeline"
app_1            | DETAIL:  Key (id)=(f39a30d3-05b4-4597-a7ab-e2ca92b4ac4a) is still referenced from table "job_pipeline".
app_1            |
app_1            |
app_1            | The above exception was the direct cause of the following exception:
app_1            |
app_1            | Traceback (most recent call last):
app_1            |   File "/usr/local/lib/python3.8/site-packages/django/core/handlers/exception.py", line 47, in inner
app_1            |     response = get_response(request)
app_1            |   File "/usr/local/lib/python3.8/site-packages/django/core/handlers/base.py", line 179, in _get_response
app_1            |     response = wrapped_callback(request, *callback_args, **callback_kwargs)
app_1            |   File "/usr/local/lib/python3.8/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
app_1            |     return view_func(*args, **kwargs)
app_1            |   File "/usr/local/lib/python3.8/site-packages/django/views/generic/base.py", line 70, in view
app_1            |     return self.dispatch(request, *args, **kwargs)
app_1            |   File "/usr/local/lib/python3.8/site-packages/rest_framework/views.py", line 505, in dispatch
app_1            |     response = self.handle_exception(exc)
app_1            |   File "/usr/local/lib/python3.8/site-packages/rest_framework/views.py", line 465, in handle_exception
app_1            |     self.raise_uncaught_exception(exc)
app_1            |   File "/usr/local/lib/python3.8/site-packages/rest_framework/views.py", line 476, in raise_uncaught_exception
app_1            |     raise exc
app_1            |   File "/usr/local/lib/python3.8/site-packages/rest_framework/views.py", line 502, in dispatch
app_1            |     response = handler(request, *args, **kwargs)
app_1            |   File "/usr/local/lib/python3.8/site-packages/rest_framework/decorators.py", line 50, in handler
app_1            |     return func(*args, **kwargs)
app_1            |   File "/home/hirecinch-backend/jobs/views.py", line 392, in delete_one_or_more_jobs
app_1            |     affected_jobs = Job.objects.filter(tenant=tenant, id__in=request.data).delete_returning()
app_1            |   File "/usr/local/lib/python3.8/site-packages/django_pg_returning/manager.py", line 183, in delete_returning
app_1            |     return self._get_returning_qs(sql.DeleteQuery)
app_1            |   File "/usr/local/lib/python3.8/site-packages/django_pg_returning/manager.py", line 145, in _get_returning_qs
app_1            |     return self._execute_sql(query, fields)
app_1            |   File "/usr/local/lib/python3.8/site-packages/django_pg_returning/manager.py", line 109, in _execute_sql
app_1            |     return ReturningQuerySet(query_sql, model=self.model, params=query_params, using=using,
app_1            |   File "/usr/local/lib/python3.8/site-packages/django/db/transaction.py", line 232, in __exit__
app_1            |     connection.commit()
app_1            |   File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner
app_1            |     return func(*args, **kwargs)
app_1            |   File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 266, in commit
app_1            |     self._commit()
app_1            |   File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 242, in _commitapp_1            |     return self.connection.commit()
app_1            |   File "/usr/local/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
app_1            |     raise dj_exc_value.with_traceback(traceback) from exc_value
app_1            |   File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 242, in _commitapp_1            |     return self.connection.commit()
app_1            | django.db.utils.IntegrityError: update or delete on table "job" violates foreign key constraint "job_pipeline_job_id_d60ec84d_fk_job_id" on table "job_pipeline"
app_1            | DETAIL:  Key (id)=(f39a30d3-05b4-4597-a7ab-e2ca92b4ac4a) is still referenced from table "job_pipeline".
app_1            |
app_1            | [01/Jan/2021 05:59:15] "POST /api/v1/job/delete/?subdomain_prefix=test-1 HTTP/1.1" 500 145088