Issue: Error with bulk_update, UUID fields and Postgresql
Author: spl0k
State: closed
Created: 2020-12-01 10:49:00

Hello

Postgres raises an error when using an `UUID` field in a `bulk_update`. The following code
```python
import logging
import uuid

from peewee import Model, UUIDField, IntegerField, PostgresqlDatabase

database = PostgresqlDatabase(
    "test", user="test", password="test", host="192.168.1.84", port=5432
)

class TestModel(Model):
    pk = UUIDField(primary_key=True, default=uuid.uuid4)
    uuid = UUIDField(default=uuid.uuid4)

    class Meta:
        database = database


database.create_tables((TestModel,))

m1, m2 = [TestModel.create() for _ in (1, 2)]
m1.uuid = uuid.uuid4()
m2.uuid = uuid.uuid4()

logger = logging.getLogger("peewee")
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)

TestModel.bulk_update((m1, m2), (TestModel.uuid,))

database.drop_tables((TestModel,))
```
gives this
```
('UPDATE "testmodel" SET "uuid" = CASE "testmodel"."pk" WHEN %s THEN %s WHEN %s THEN %s END WHERE ("testmodel"."pk" IN (%s, %s))', ['a2fdc19c233b44caa909eff4f878b13f', '3d0a5bfe46ef42d4ab9cc644a15407e5', 'ff76972d6d7d4b9f94ca3c3052d23269', 'ee7ba41da3d741e192512ef0a2071301', 'a2fdc19c233b44caa909eff4f878b13f', 'ff76972d6d7d4b9f94ca3c3052d23269'])
Traceback (most recent call last):
  File "E:\projects\bug\env\lib\site-packages\peewee.py", line 3129, in execute_sql
    cursor.execute(sql, params or ())
psycopg2.errors.DatatypeMismatch: column "uuid" is of type uuid but expression is of type text
LINE 1: UPDATE "testmodel" SET "uuid" = CASE "testmodel"."pk" WHEN '...
                                        ^
HINT:  You will need to rewrite or cast the expression.


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "peewee_bug.py", line 32, in <module>
    TestModel.bulk_update((m1, m2), (TestModel.uuid,))
  File "E:\projects\bug\env\lib\site-packages\peewee.py", line 6411, in bulk_update
    .where(cls._meta.primary_key.in_(id_list))
  File "E:\projects\bug\env\lib\site-packages\peewee.py", line 1898, in inner
    return method(self, database, *args, **kwargs)
  File "E:\projects\bug\env\lib\site-packages\peewee.py", line 1969, in execute
    return self._execute(database)
  File "E:\projects\bug\env\lib\site-packages\peewee.py", line 2465, in _execute
    cursor = database.execute(self)
  File "E:\projects\bug\env\lib\site-packages\peewee.py", line 3142, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "E:\projects\bug\env\lib\site-packages\peewee.py", line 3136, in execute_sql
    self.commit()
  File "E:\projects\bug\env\lib\site-packages\peewee.py", line 2902, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "E:\projects\bug\env\lib\site-packages\peewee.py", line 185, in reraise
    raise value.with_traceback(tb)
  File "E:\projects\bug\env\lib\site-packages\peewee.py", line 3129, in execute_sql
    cursor.execute(sql, params or ())
peewee.ProgrammingError: column "uuid" is of type uuid but expression is of type text
LINE 1: UPDATE "testmodel" SET "uuid" = CASE "testmodel"."pk" WHEN '...
                                        ^
HINT:  You will need to rewrite or cast the expression.
```

With both PostgreSQL 10 and 11. There is no problem with SQLite, and I haven't tested MySQL/MariaDB.

Note that the UUID primary key isn't the problem here, as using another type for the field passed to `bulk_update` yields no error.

This is using peewee 3.14.0.

This came up recently related to array fields: #2292 - I believe this is either the same (or very similar) issue. I can't offer a fix for this at present and suggest the following workarounds:

* write your Case() explicitly with the appropriate cast
* begin a transaction and issue your updates one-at-a-time - profile this! it very likely may be just as performant

Ok, I also considered doing single updates, I guess I'll go this way (especially since the app might also be used in SQLite-backed environments) and try to remember to not use such types in `bulk_update`.
Thank you for your quick answer and the link to some explanation.

Yeah, I'm sorry to not be able to offer a fix right now. I think as long as you're using a transaction then updating individually should be fine, however.

@coleifer Would you add this to the docs please, I have used a lot time to find this issue.
I considered following methods:
1) do not use non-integer primary key
2) change name "id"(non-integer primary key) to "idcard"
It's difficult for noobs to know this issue.

If a noob saw a note in docs:
Note:
When using bulk_update, if there is a DatatypeMismatch error, and you are using postgresql.
You should use single update.
because ...

@yunlongYoung - those methods you outlined are not quite accurate and are not recommended. Bulk update works perfectly fine with a non-integer primary key (including UUID primary key), as well as any arbitrary pk column name, including "id".

The bulk-update works well generally across databases and field-types. The issue has to do with updating the UUID value in UUID fields (which seems like an odd thing to do anyways, but that's beside the point...). Postgres doesn't like it when we do something like:

```sql
SET "uuid_col" = (
  CASE "id" 
    WHEN 1 THEN <some uuid>
    WHEN 2 THEN <some other uuid> 
  END)
```

Details can be read here: https://dba.stackexchange.com/questions/228046/why-do-i-need-to-cast-null-to-column-type/228058#228058

The accepted workaround is to simply make individual `UPDATE` queries.