id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34996	Enhance update_or_create() method with upsert SQL.	Jordan Bae	nobody	"== Context

Current QuerySet.update_or_create method work like below 

1. open transaction or savepoint
2. get_or_create with lock
3. exist -> update and not exist - create

I want to suggest how about refactoring this with upsert SQL (ex. INSERT INTO ... ON DUPLICATE KEY UPDATE)
{{{
    def update_or_create(self, defaults=None, create_defaults=None, **kwargs):
        """"""
        Look up an object with the given kwargs, updating one with defaults
        if it exists, otherwise create a new one. Optionally, an object can
        be created with different values than defaults by using
        create_defaults.
        Return a tuple (object, created), where created is a boolean
        specifying whether an object was created.
        """"""
        update_defaults = defaults or {}
        if create_defaults is None:
            create_defaults = update_defaults

        self._for_write = True
        with transaction.atomic(using=self.db):
            # Lock the row so that a concurrent update is blocked until
            # update_or_create() has performed its save.
            obj, created = self.select_for_update().get_or_create(
                create_defaults, **kwargs
            )
            if created:
                return obj, created
            for k, v in resolve_callables(update_defaults):
                setattr(obj, k, v)

            update_fields = set(update_defaults)
            concrete_field_names = self.model._meta._non_pk_concrete_field_names
            # update_fields does not support non-concrete fields.
            if concrete_field_names.issuperset(update_fields):
                # Add fields which are set on pre_save(), e.g. auto_now fields.
                # This is to maintain backward compatibility as these fields
                # are not updated unless explicitly specified in the
                # update_fields list.
                for field in self.model._meta.local_concrete_fields:
                    if not (
                        field.primary_key or field.__class__.pre_save is Field.pre_save
                    ):
                        update_fields.add(field.name)
                        if field.name != field.attname:
                            update_fields.add(field.attname)
                obj.save(using=self.db, update_fields=update_fields)
            else:
                obj.save(using=self.db)
        return obj, False
}}}
=== Strength
- Performance: when updates, there is no need transaction and lock. and it's single query.
- Maintenance: It can be simple for maintenance.

=== Consideration
- database compatibility: need to check support upsert SQL in the all of databases.
 


"	New feature	closed	Database layer (models, ORM)	dev	Normal	wontfix			Unreviewed	0	0	0	0	0	0