django queryset returns wrong values from postgresql view

Question

I have a crazy bug somewhere in this setup.

The database is Postgres 9.1 and is pre-existing (not managed by Django). In it there exists 1 table and then a number of fairly simple views, one of which is called valid_logins_dow_popularity as defined:

 =>\d+ valid_logins_dow_popularity
             View "public.valid_logins_dow_popularity"
   Column   |       Type       | Modifiers | Storage | Description
------------+------------------+-----------+---------+-------------
 logins_avg | double precision |           | plain   |
 dow        | double precision |           | plain   |
View definition:
 WITH by_dow AS (
         SELECT valid_logins_over_time.count, date_part('dow'::text, valid_logins_over_time.date) AS dow
           FROM valid_logins_over_time
        )
 SELECT avg(by_dow.count)::double precision AS logins_avg, by_dow.dow
   FROM by_dow
  GROUP BY by_dow.dow
  ORDER BY by_dow.dow;

In Django 1.4 I've defined a simple model that uses that view as it's datasource:

class ValidLoginsDowPopularity(models.Model):
    class Meta:
        db_table = 'valid_logins_dow_popularity'
        managed = False

    logins_avg = models.FloatField(
                            db_column='logins_avg')
    # Day of Week (dow)
    dow = models.IntegerField(db_column='dow',
                              primary_key=True)

    def __unicode__(self):
        return u"%d : " % (self.dow, self.logins_avg )

When I grab the data directly from the DB I get one set of numbers:

SELECT "valid_logins_dow_popularity"."logins_avg", "valid_logins_dow_popularity"."dow" 
  FROM "valid_logins_dow_popularity";

    logins_avg    | dow
------------------+-----
 28.8571428571429 |   0
 95.1428571428571 |   1
 91.4285714285714 |   2
           89.625 |   3
 82.6666666666667 |   4
 61.4285714285714 |   5
 28.4285714285714 |   6
(7 rows)

When I get the data through the Django model I get a somewhat vaguely related, but different set of numbers:

In [1]: from core.models import *

In [2]: v = ValidLoginsDowPopularity.objects.all()

In [3]: for i in v:
    print "logins_avg : %f | dow : %d" % (i.logins_avg, i.dow)
   ...:
logins_avg : 25.857143 | dow : 0
logins_avg : 85.571429 | dow : 1
logins_avg : 89.571429 | dow : 2
logins_avg : 86.375000 | dow : 3
logins_avg : 83.000000 | dow : 4
logins_avg : 67.000000 | dow : 5
logins_avg : 28.000000 | dow : 6

To date, I've verified the sql that Django generates, when run directly from psql returns the expected output. I've likewise tried with the Django model using a IntegerField, FloatField and DecimalField for the logins_avg attribute -- all have the same, but incorrect values. I've also written a simple test program to bypass the Django code and make sure it isn't a psycopg2 issue:

import psycopg2

def main():
    conn_string = "dbname='********' user='*********'"

    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()

    sql = "select * from valid_logins_dow_popularity"
    cursor.execute(sql)

    for rec in cursor.fetchall():
        print rec

if __name__ == '__main__':
    main()

Which, when run give the correct fault, so psycopg2 seems to be doing the right thing:

$ python test_psycopg2.py
(28.8571428571429, 0.0)
(95.1428571428571, 1.0)
(91.4285714285714, 2.0)
(89.625, 3.0)
(82.6666666666667, 4.0)
(61.4285714285714, 5.0)
(28.4285714285714, 6.0)

How is this possible? Any clues would be appreciated. Where could I dig into the Django code and see where things go wrong? Should I report this issue with the Django Project?

What is the database type of the logins_avg field in your view/table? I suspect the conversion from that to the FloatField type is not working correctly. — Jeff Shelman
– Jeff Shelman, Commented Aug 2, 2012 at 17:08
Good idea. I've edited the question to have those details, but I've also tried to cast the login_avg column to be an int, updated the model to IntegerField, and still there were odd value differences. — portman
– portman, Commented Aug 2, 2012 at 17:27
FloatField should map correctly to "double precision"...what python db engine are you using? postgresql_psycopg2? you could also maybe try a DecimalField instead of FloatField and see if that changes anything — Jeff Shelman
– Jeff Shelman, Commented Aug 2, 2012 at 18:49
psycopg2==2.4.5 I just converted the model over DecimalField and get the exact same (incorrect) values. — portman
– portman, Commented Aug 2, 2012 at 19:16

djvg · Accepted Answer · 2021-07-27 13:13:41Z

1

Redefine the view and cast the value to a numeric instead of a double. In the Django model you need a DecimalField that matches the Postgres numeric (like numeric(15,10) -> DecimalField(max_digits=15, decimal_places=10)).

I've never had any luck at all with floating point values between Django and the db and have had similar float weirdness problems with other software talking to databases before as well. Doing numeric <-> DecimalField is the only way I've found to guarantee floating point values don't get weird -- by changing them into fixed-point values.

edited Jul 27, 2021 at 13:13

djvg

14.8k7 gold badges84 silver badges120 bronze badges

answered Aug 16, 2012 at 12:57

zxq9

13.2k1 gold badge48 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

django queryset returns wrong values from postgresql view

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related