Cast with AutoField results in a 'type does not exist' error in PostgreSQL 10.1

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Cast with AutoField results in a 'type does not exist' error in PostgreSQL 10.1

Andrew Standley
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 [hidden email].
To post to this group, send email to [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

Re: Cast with AutoField results in a 'type does not exist' error in PostgreSQL 10.1

Carlton Gibson-3
Hi Andrew, 

Reading the description, it seems like you may have hit a bug. 

Could you possibly put this into an actual test case and open a PR
(plus Trac ticket) with that  assuming the test fails? 

With code in hand it's much easier to assess (and fix). 

Thanks. 

Kind Regards,

Carlton

--
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 [hidden email].
To post to this group, send email to [hidden email].
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/6b51e80d-d237-4e6d-814e-2d70d0229139%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.