Bigint field object needed

[jmadson@…]

The existing IntegerField is not adequate for generating schemas that require a "bigint" field. Either IntegerField needs to take a size/length parameter or a new Field type is necessary.

[jmadson@…]

Additionally, a signedness boolean option is needed on IntegerField.

[MattCroydon]

See bigint_patch.txt for a BigIntegerField and PositiveBigIntegerField. I took it for a spin on mysql but it needs testing on postgres and sqlite (the backend-specific stuff is there but has not been tested).

jmadson: I think PositiveIntegerField, SmallPositiveIntegerField, or BigPostitiveIntegerField might be what you're looking for unless I misunderstood.

[MattCroydon]

BigIntegerField seems OK on PostgreSQL, but PostitiveBigIntegerField throws ProgrammingError: ERROR: integer out of range for 18446744073709551615. I'll see what I can do.

[MattCroydon]

See ​my post for details, but it looks like a PostitiveBigInteger on PostgreSQL is only going to reach 9 bajillion whereas MySQL does 18 bajillion. Still needs testing on sqlite.

[Gopal Narayanan <gopastro@…>]

Matt Croydon patch is now quite dated for the existing codebase. I have updated ticket #399 with the new patch. It is called bigint_patch_06aug06.txt. Since I am most familiar with MySQL, I modified the backend code for MySQL. The changes for Oracle, postgres, sqlite, etc. should be minimal, and should be done for django/db/backends.
I have done some simple tests. Please take it out for a spin.

[Gaël Chardon <gael.dev@…>]

I have updated Gopal (previously Matt Croydon) patch against revision [4274]
See bigint_patch_03jan07.diff

[Chris Beaven]

Ok, on second thoughts this is a valid ticket.

Current patch needs some documentation and implementation for other database backends.

[Marc Fargas <telenieko@…>]

Last patch with docs added.

[Marc Fargas <telenieko@…>]

needs_docs = 0 and stripping redundant prefix from summary.

[Peter Nixon <listuser@…>]

Add BigIntegerField support to django (SQLite, MySQL, Postgresql, Oracle, MSSQL)

[Peter Nixon <listuser@…>]

Add BigIntegerField? support to django (SQLite, MySQL, Postgresql (and postgresql_psycopg2), Oracle, MSSQL)

[Peter Nixon <listuser@…>]

Oops. I missed introspection support for postgresql_psycopg2. Updated patch attached.

[Thomas Güttler <hv@…>]

The patch django-bigint-20070712.patch is working for me (psycopg2)

[Tomáš Kopeček]

Previous patch with simple tests

[Tomáš Kopeček]

Updated, added constraint checking

[Matt McClanahan]

The latest patch's approach for constraint checking (sys.maxint) isn't going to work for 32bit systems.

[Tomáš Kopeček]

sys.maxint was changed to class constant.

[Jacob]

This isn't yet ready for checkin. The MAX_BIGINT variable in django.db.models.fields is fixed, but in reality is dependant on both architecture and database backend. I'm not sure what the fix is, but hard-coding 64 bits is going to look as silly in ten years as hardocoding 32 bits does today.

[Tomáš Kopeček]

Replying to jacob:

This isn't yet ready for checkin. The MAX_BIGINT variable in django.db.models.fields is fixed, but in reality is dependant on both architecture and database backend. I'm not sure what the fix is, but hard-coding 64 bits is going to look as silly in ten years as hardcoding 32 bits does today.

But we _define_ BIGINT as 64 bits long (as written in doc). So why not to hardcode it that it will be always 64bit? Also in all db backends it is defined as 64bit type. So, there is no same situation like with integer data type which has always denpended on size of CPU register. This will always be 64bits. If there is a variant that db backend cannot save 64bit value, then we cannot us bigint type at all. But all backends supports 64bit values, so there is no problem with this. Yes, I also don't like `magic' value, but I think that it is adequate here because python doesn't define anything similar to sys.maxint.

[Vitaliy <vitaliyf@…>]

Typo in latest patch:

raise ValueError("Value is so small/large to fit to field")

should be

raise ValueError("Value is too small/large to fit this field")

(or something like that. Also, same message is in tests/modeltests/field_types/models.py)

[corporeal]

The SQL specification defines bigint as a 64-bit signed integer, and as such the definition of bigint in the context of SQL will never change, just as the definition of int has not changed despite the need for 64-bit signed integers today. Thus I believe jacob's reason for delaying check-in of this patch to be invalid. Please change check this in as soon as possible.

[James Bennett]

"Ready for checkin" is a determination made by triagers and committers. Please don't set it unless you're one of those people.

[Tomáš Kopeček]

English typos

[Tomáš Kopeček]

Replying to corporeal:
Agreed, changing to 'ready for checkin'

[Ramiro Morales]

Please, please read ​http://code.djangoproject.com/ticket/399#comment:24 and ​http://www.djangoproject.com/documentation/contributing/#ticket-triage

[Tomáš Kopeček]

Replying to ramiro:

Please, please read ​http://code.djangoproject.com/ticket/399#comment:24 and ​http://www.djangoproject.com/documentation/contributing/#ticket-triage

Ticket triagers: community members who keep track of tickets, making sure the tickets are always categorized correctly.

Probably I don't understand this correctly. Who are triagers if not us? Is there any list of them? How to make some progress with tickets, whose are ready for months and no one moves them to next stage?

[Marc Fargas]

Recalling comments, to avoid triagers loosing time reading the full log to take conclusions:

• In ​comment:20 jacob says that why would we hardcode that Bigint field to 64bits. Thus marking as "needs improvement".
• But corporeal says: "The SQL specification defines bigint as a 64-bit signed integer"

So, hardcoding it is not that bad. maybe we could ask the backend about the bigint size, but writting this for every backend knowing that BIGINT must be 64bits long seems a bit cumbersome.

So then, I'm setting "needs improvement" to 0, any triager please move to "Ready for checkin" or "Please ask every backend if they feel right beeing 64bit long" :)

[honeyman]

Sorry for meddling in, but shouldn't django/db/models/fields/init.py have

if value > self.MAX_BIGINT or value < -self.MAX_BIGINT - 1

instead of

if value > self.MAX_BIGINT or value < -self.MAX_BIGINT

?

[Tomáš Kopeček]

Replying to honeyman:

Sorry for meddling in, but shouldn't django/db/models/fields/init.py have

if value > self.MAX_BIGINT or value < -self.MAX_BIGINT - 1

instead of

if value > self.MAX_BIGINT or value < -self.MAX_BIGINT

?

You're right. Appending new patch.

[Tomáš Kopeček]

-1 problem fix

[cpinto]

The patch I just submitted fixes an "integer out of range" in pgsql by specifying the internal type of the BigIntegerField

[mwdiers <martin@…>]

There was a lot of work on this, and then it all just stopped, waiting for Triage team to move to check-in. I would like to repeat permon's question above.

How do we give a "shout-out" to the triage members when a ticket has met check-in requirements, but yet has been sitting for months, particularly when we don't know who they are? (I understand it's a volunteer effort and all, but it's still a valid question).

I really need this patch, and it seems like many others do as well. As I am working on the newforms-admin branch, I would like to not have to apply this patch everytime I update SVN.

[mwdiers <martin@…>]

Restored tests to previously submitted patch

[Chris Beaven]

Yep, this does look ready for checkin to me. The best way to get tickets rolling again is to ask about them in django-dev group. Would you mind doing that now?

I'll promote anyway.

[mwdiers <martin@…>]

Replying to SmileyChris:

Yep, this does look ready for checkin to me. The best way to get tickets rolling again is to ask about them in django-dev group. Would you mind doing that now?

Thank you. I will do so right away.

[wesley]

Hi,
I have an issue with the latest patch (bigint-patch-2008-04-25.diff) and fixtures.
When I load a fixtures file that contains BigInteger fields, I have this error : "Value is too small/large to fit this field"

In file django/db/models/fields/init.py in class BigIntegerField in function get_db_prep_save(self, value).
The type of value parameter is unicode instead of long. (The content of value match correctly with the biginteger field in my fixtures file).

If I cast value parameter to long in the condition statement all works fine, here is my update :

    def get_db_prep_save(self, value):
        value_long =  long(value)
        if value_long > self.MAX_BIGINT or value_long < -self.MAX_BIGINT - 1: 
            raise ValueError("Value is to small/large to fit this field") 
        return super(BigIntegerField, self).get_db_prep_save(value)

[mwdiers <martin@…>]

The value should have already been converted to a Long or an Integer by that point in the code. So the problem lies elsewhere. JSON and YAML serialization work just fine. It's only XML that is producing this error. From what I can tell, a regular IntegerField should also be producing this error, but for some reason it is not, and I cannot determine why not.

In any case, the proper solution for something like this is to override to_python():

    def to_python(self, value):
        return super(BigIntegerField, self).to_python(long(value))

I'm changing this bug to Unreviewed, as it's obviously not ready for checkin.

[mwdiers <martin@…>]

Ok. I got it. The problem is introduced by trying to perform validation, and since IntegerField does no validation at all - by design - it produces no error here. Model is smart enough to parse out the integer by the time it gets to the DB.

But there is a bigger question: Why are we validating this field at all? Why not rely upon the backend to provide its own validation, just like we do with IntegerField?

Seems to me the fix is to skip validation entirely. That would also avoid the whole mess with different bigint sizes on different architectures.

[mwdiers <martin@…>]

For anyone who would like BigInteger support, there is no need to wait for or to apply this patch. If you are using newforms, the following is all you need:

from django.db.models.fields import IntegerField
from django.conf import settings

class BigIntegerField(IntegerField):
	empty_strings_allowed=False
	def get_internal_type(self):
		return "BigIntegerField"
	
	def db_type(self):
		return 'NUMBER(19)' if settings.DATABASE_ENGINE == 'oracle' else 'bigint'

[Tomáš Kopeček]

Replying to mwdiers <martin@diers.us>:

Ok. I got it. The problem is introduced by trying to perform validation, and since IntegerField does no validation at all - by design - it produces no error here. Model is smart enough to parse out the integer by the time it gets to the DB.

But there is a bigger question: Why are we validating this field at all? Why not rely upon the backend to provide its own validation, just like we do with IntegerField?

Seems to me the fix is to skip validation entirely. That would also avoid the whole mess with different bigint sizes on different architectures.

a) You're right, there is a possibility, that value arrives as string. So cast to long is necessary.
b) Why there is validation -> main reason was that each backend behaves differently. If I remember well, SQLite saves clipped value, MySQL raises exception and so on. For me looked like best way to have same behaviour with all backends.

[Tomáš Kopeček]

missing typecast

[Robert Coup]

the current patch doesn't handle None values - get_db_prep_save() should explicitly check for it:

def get_db_prep_save(self, value):
    if value is not None:
        value_long =  long(value)
        if value_long > self.MAX_BIGINT or value_long < -self.MAX_BIGINT - 1: 
            raise ValueError("Value is to small/large to fit this field") 
    return super(BigIntegerField, self).get_db_prep_save(value)

[Martin Diers]

I cannot speak for the developers, but when this patch has come up on the development group, time and again they have said that it is not needed, because it is now so easy to create your own special field types, on the fly, and in the case of a BigInt, it is trivial to do so. See the above example which I gave, dated 5/2/08.

I completely agree with this, especially considering the direction that Python 3 seems to be heading, where there is no longer any distinction at all between an Int and a Long, and the fact that 64-bit architectures are quickly relegating the old Int/Long distinction to an historical observation.

I would not be surprised if they eventually close this ticket as a "wontfix".

[Tomáš Kopeček]

Replying to mwdiers:
There are two possibilities - we want to have BIGINT in all db backends, then this patch should be finished and incorporated into trunk. Second possibility is that bigint is not 'basic' field and we will discard this ticket. But it depends on somone from core developers. Again I think, that situation has changed after 1.0, so I will change status to 'design decision'.

Depending on result of this decision, I will try to finish the patch or discard it. From my point of view are both solutions valid.

[fnl]

I created a fix for this problem which should work with Oracle and definitely works with PSQL and MySQL. It uses a new BigIntegerField, BigAutoField, and BigForeignKey field because they are all hardcoded to use the AUTO_INCREMENT feature of the databases. Would be nice if somebody could check out the code and comment if it works for Oracle, too.
link: ​http://www.djangosnippets.org/snippets/1244/

[CAJoel]

I could not get mwdiers's patch to work or the fnl patch.

It appears to result in a KeyError on the next "manage.py reset SITE".

Traceback (most recent call last):

File "./manage.py", line 11, in <module>

execute_manager(settings)

File "/var/lib/python-support/python2.5/django/core/management.py", line 1672, in execute_manager

execute_from_command_line(action_mapping, argv)

File "/var/lib/python-support/python2.5/django/core/management.py", line 1630, in execute_from_command_line

output = action_mapping[action](mod, options.interactive)

File "/var/lib/python-support/python2.5/django/core/management.py", line 661, in reset

sql_list = get_sql_reset(app)

File "/var/lib/python-support/python2.5/django/core/management.py", line 352, in get_sql_reset

return get_sql_delete(app) + get_sql_all(app)

File "/var/lib/python-support/python2.5/django/core/management.py", line 468, in get_sql_all

return get_sql_create(app) + get_custom_sql(app) + get_sql_indexes(app)

File "/var/lib/python-support/python2.5/django/core/management.py", line 127, in get_sql_create

output, references = _get_sql_model_create(model, known_models)

File "/var/lib/python-support/python2.5/django/core/management.py", line 175, in _get_sql_model_create

col_type = data_types[data_type]

KeyError: 'BigIntegerField'

In the meantime, I've been using CharFields.

I wish this wasn't so hard to fix.

[CAJoel]

The patches work in 1.0.2, but not in a pre 1.0 version which came with my distro.

I was hoping to avoid installing from source, bummer.

--joel

[Martin Diers]

Joel,

I would never rely upon any distro to provide Django, unless you want to be stuck on a very old version of the framework, and ignore the incredible improvements which have come with 1.0 and 1.1. Installing from source is so trivial, it's not worth fretting over.

A massive amount has changed since the pre-1.0 days, including the ability to easily create custom field types, as I note in comment 38 above. 1.0 and later have no need for a patch like those in this ticket, and that is why no work further work is likely to be done to support BigInt in the back ends.

[nix]

That advice is not necessarily true. I help maintain the django packages in the openSUSE devel:languages:python project available at:
​http://download.opensuse.org/repositories/devel:/languages:/python/

We generally have the latest stable release (Currently at 1.0.2) of Django packaged and on the mirrors within 24 hours of release, and have worked together with the Django team in the past to do simultaneous releases of updated packages when security holes were found in Django. Please feel free to use our rpms if you want a tested Django installation on any currently shipping i386 or x86_64 openSUSE version and the most common SUSE Linux Enterprise versions (We don't auto-build IA64, Sparc, PowerPC or S390 packages at present, however people with these platforms can trivially rebuild our source rpms.)

As it turns out I also monitor this bug (and have submitted a patch to it in the past) as this bit me in one of my production deployments of Django.

[Walter Ferreira]

Why this is not included yet in the django install source package?

Postgresql have Bigint field but django models not :|

[anonymous]

@emonk:

Because anybody can implement Bigint by themselves with about 8 lines of code. See my comment above. It makes no sense to support new field types in Django that do not apply to all backends, when it is trivial to implement your own custom field with practically no work.

[fnl]

@anonymous

Not true, see my comments on the possible implementation I submitted on djangosnippets that does not patch the django code but uses custom fields. Not only does it need 70 lines of (clean, readable) code, because to use bigint as PK you need to patch Integer, AutoField AND ForeignKey, otherwise it does not work properly. A very hiddeos and awful solution; but you are right if you want to just argue "it is practically no work" because you can copy my snippet...

[Richard Davies <richard.davies@…>]

Adding to the 1.2 milestone, since it was accepted into the medium priority list (ORM-04).

[Tomáš Kopeček]

updated to trunk

[Tomáš Kopeček]

Patch updated to trunk. Because oldforms library is completely out, some changes are out. Needs testing in admin/newforms environment. Also some better documentation (look for all relevant places in docs) is needed. Patch is against r11655.

[jhovanny]

Patch is not in trunk. I checked all changesets from r11655, and it's not there. Updated from trunk, and it's not there. Anybody knows what happened?

[Martin Diers]

Replying to jhovanny:

Patch is not in trunk. I checked all changesets from r11655, and it's not there. Updated from trunk, and it's not there. Anybody knows what happened?

Nothing here says that the patch is in trunk. Permon's comment means he updated the patch so that it works against the trunk. In fact, if you look at the comments, you will see that the patch needs tests and better documentation. Please let the triage team change the triage stage.

[jhovanny]

I apologize. I misunderstood the comment. I'll be testing this patch as part of my code, and report any problems that might arise.

[Martin Diers]

Replying to jhovanny:

I apologize. I misunderstood the comment. I'll be testing this patch as part of my code, and report any problems that might arise.

"Needs Tests" refers to written Unit Tests. Please see the ​Contributing to Django page for more information about Triage, Unit Tests, and other useful topics.

[bruce_s]

Is there any ETA on this? This bug has been open for four years. A working patch was submitted 08/26/05 -- four years ago. This still isn't in trunk.

Bigint is necessary to interoperate with Facebook (facebook id's are big integers), so there is substantial demand for this feature.

[Tomáš Kopeček]

Patch was little bit tailored to current trunk. Also tests were added. Tests were run on mysql and postgres backends. Please try other backends and let me know about potential problems. Documentation is according to documentation of other DB fields, so I am removing Doc flag.

[Ramiro Morales]

bigint-patch-2009-11-23.diff contains an extraneous modification in django/contrib/comments/templates/comments/preview.html.

[Tomáš Kopeček]

updated patch, modelform, tests are back (removed typo in comments template)

[anonymous]

I'm not sure if django/db/backends/sqlite3/creation.py shouldn't be patched this way:

+        'BigIntegerField':              'integer',

instead of actual:

+        'IntegerField':                 'bigint',

[Tomáš Kopeček]

Replying to anonymous:
You are right. Patch was updated, also tests were slightly modified. Tests were successfully run on these backends: sqlite3, mysql, postgresql, postgresql_psycopg2. I am still looking for someone with running oracle instance who can confirm tests and correct admin behavior.
Also changing stage to accepted due Richard Davies

[Tomáš Kopeček]

updated patch (typo, tests)

[Chris Beaven]

Replying to permon:

I am still looking for someone with running oracle instance who can confirm tests and correct admin behavior.

The best thing you can do is keep on bring it up on the django-dev google group.

[Karen Tracey]

The tests from the previous patch ran on Oracle. I noticed the sqlite problem and some of the tests were failing on MySQL and Postgres, I assume your test tweaks involve no longer testing for the DB returning int, since in some cases the DB is going to be returning a long? (Sorry, trac isn't displaying the diff and I don't have much time right now.)

One thing that is not working, on Oracle, is introspection. Running inspecdb on a table with a field created as a BigInteger generates a model where the field is an Integer. This is something not covered by tests. It would be good if the tests included testing introspection. There are a few introspection tests in regressiontests/introspection but I have not had a chance to look at them at all to see how much guidance they give for testing stuff like this.

[Ian Kelly]

Added Oracle introspection and test

[Tomáš Kopeček]

All tests works for me now, also introspection looks well now. Patch applies correctly to current trunk, so removing 'Patch needs improvement'. I'll consult committer/devel list about inclusion in trunk.

[Jiri Suchan]

Shouldn't be BigIntegerField compatible with IntegerField in all ways? Actually it isn't - if there's created BigIntegerField(blank=True, something), it throws TypeError during save with empty value in this field. Patch still needs improvements, imho.

long() argument must be a string or a number, not 'NoneType'

Exception Location: ../django_common/django/db/models/fields/init.py in get_db_prep_save, line 736

[Tomáš Kopeček]

fixed: correct saving null fields + test

[Jiri Suchan]

I'm happy about it now. Taking back my needs_better_patch.

[Karen Tracey]

I've reviewed the existing patch and uploaded a new one.

Primary difference is that I removed the limit testing done during save. No other fields do this kind of validation during save and it seems highly inconsistent to add one field that has this one specific kind of test (without testing for other bad things, like attempting to save floats, for example). It is true that the behavior if you attempt to save outside the limits is ugly and inconsistent from db to db, but it's consistent with how other existing fields behave. The right way to fix it, consistently for all fields, would be in something like model validation, I think, which is beyond the scope of this ticket.

I did, however, leave in the formfield that sets max and min limits. This too is a bit inconsistent with other fields, but for some reason it does not bother me as much, perhaps because it limits the likelihood of a user encountering the ugliness resulting from not validating during save.

I also added some doc and put the existing doc for the new field in alphabetical order rather than making it a subtype under the doc for IntegerField. That's an implementation detail I don't believe needs to be exposed in the user doc.

[Karen Tracey]

(In [11887]) Fixed #399: Added big integer field. Thanks to Tomáš Kopeček for persistently maintaining a patch for this.

[Jacob]

Milestone 1.2 deleted