Hey all,
I've run across an issue and I'm not sure if what I have is a bug, or
if I've just done something horribly wrong.
I'm hoping one of you can help me work out what it is.
The issue is that `AutoField.db_type` returns 'serial', which is a
"syntactical sugar" (not a true type) in Postgresql and only valid for
creation. This is fine, but `AutoField.cast_db_type` also returns
'serial', which is invalid. More confusingly `AutoField.rel_db_type`
correctly returns 'integer'.
This means that when using `Cast` with AutoField as part of a join a
"psycopg2.ProgrammingError: type "serial" does not exist" error is
thrown by the connection.
Manually 'correcting' for this and using an IntegerField in the query
produces the correct results.
This seems like a bug to me, but I just want to be sure that I've not
missed something, like considerations for other databases, or other uses
of `cast_db_type` that mean it can't return 'integer'.
Full details of the route to this problem are below.
Cheers,
Andrew
----
I ran into it using django-guardian's object permission implementation,
but have reproduced with the following test case.
Using PostgreSQL 10.1 with the following models:
```
class Item(models.Model):
label = models.CharField(max_length=100)
cost = models.IntegerField()
class Activity(models.Model):
content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)
object_pk = models.CharField(max_length=100)
generic_object = GenericForeignKey(fk_field='object_pk')
```
I needed to get all Items which have an Activity. I can't use
GenericRelation because I have to be able to get records with Activities
for ANY arbitrary model, and I don't have control over some of the
models. So naively I tried
```
item_ct = ContentType.objects.get_for_model(Item)
Item.objects.filter(pk__in=Activity.objects.filter(content_type=item_ct).values('object_pk'))
```
which, of course, resulted in a type error "psycopg2.ProgrammingError:
operator does not exist: integer = character varying"
So after some research I managed to get the ORM to produce the statement
I wanted using `annotate` and `Cast`
```
item_ct = ContentType.objects.get_for_model(Item)
Item.objects.filter(pk__in=Activity.objects.filter(content_type=item_ct).annotate(casted_pk=Cast('object_pk',
IntegerField())).values('casted_pk'))
```
However when I went to extend this to the general case I ran into the
'type' issue. (model = Item)
```
model_ct = ContentType.objects.get_for_model(model)
pk_field_class = model._meta.pk.__class__
model.objects.filter(pk__in=Activity.objects.filter(content_type=model_ct).annotate(casted_pk=Cast('object_pk',pk_field_class
())).values('casted_pk'))
```
I received the "psycopg2.ProgrammingError: type "serial" does not exist"
error.
On investigation 'SERIAL' in just syntactic sugar over the `INT` type,
which seems to work correctly for the database definition creation.
Item does indeed have the 'id' `AutoField` as an 'integer' type that has
the correct 'autoincrement' default.
```
Table
"public.test_item"
Column | Type | Collation | Nullable
| Default | Storage | Stats target |
Description
-----------------+--------------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
id | integer | | not null |
nextval('test_item_id_seq'::regclass) | plain | |
label | character varying(100) | | not null
| | extended | |
cost | integer | | not null
| | plain
```
DefaultConnectionProxy does map AutoField to 'serial', which seems
reasonable given it can only hold one 'type'.
```
>>> connection.data_types
{'BigAutoField': 'bigserial', 'IntegerField': 'integer',
'DurationField': 'interval', 'GenericIPAddressField': 'inet',
'FilePathField': 'varchar(%(max_length)s)', 'TimeField': 'time'mallint',
'OneToOneField': 'integer', 'CharField': 'varchar(%(max_length)s)',
'NullBooleanField': 'boolean', 'TextField': 'text', 'DateTimeField':
'timestamp with time zone', 'FileFith)s)', 'IPAddressField': 'inet',
'AutoField': 'serial', 'PositiveIntegerField': 'integer',
'DecimalField': 'numeric(%(max_digits)s, %(decimal_places)s)',
'UUIDField': 'uuid', 'DateFmallIntegerField': 'smallint', 'BinaryField':
'bytea', 'BooleanField': 'boolean', 'SlugField':
'varchar(%(max_length)s)', 'FloatField': 'double precision',
'BigIntegerField': 'bigint
```
However, it seems pretty clear that when trying to CAST to an AutoField
column you need to CAST to an 'integer' type.
Looking at the implementation of AutoField `rel_db_type` has been
overloaded with:
```
def rel_db_type(self, connection):
return IntegerField().db_type(connection=connection)
```
but `cast_db_type` has not.
So I'm left wondering why `AutoField.cast_db_type` does not return
'integer' as `AutoField.rel_db_type` does?
Is there some consideration I'm not taking into account, or is this just
a bug? (In which case at least the 'fix' seems straightforward enough)
--
This message has been scanned by E.F.A. Project and is believed to be clean.
--
You received this message because you are subscribed to the Google Groups "Django
developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/713a99b5-3314-935b-cc73-f91982c3351a%40linear-systems.com.
For more options, visit https://groups.google.com/d/optout.
