Quick question: could django set the default to to_date('2014-31-01', 'yyyy-mm-dd')? ________________________________________ From: django-developers@googlegroups.com [django-developers@googlegroups.com] on behalf of Shai Berger [s...@platonix.com] Sent: Friday, October 31, 2014 17:34 To: django-developers@googlegroups.com Subject: Django's problem with db-level defaults on Oracle
Hi Everyone, I just mentioned in another thread that db-level defaults are particularly troublesome on Oracle. I didn't want to burden that discussion with the detais, but having been asked about it on IRC (thanks Josh), here they are. The problem is caused by a combination of factors: 1) Oracle stores database-level defaults as strings, evaluated when needed. This is not, in itself, completely insensible -- the processing and space overheads (compared to some more "binary" representation) are negligible, and it means defaults "4" and "sysdate()" are treated by the system uniformly. 2) Django's Oracle backend sets the date-time format to a constant (close to ISO format), which is usually not the default. This has been used to perform some database date-time operations by manipulating strings -- because that way was easier to the developer implementing them, or there wasn't proper support for the feature otherwise; as a classic example, before 1.7, date-times used to be inserted into the database as strings, because some special manipulation was required to make cx_Oracle (the database driver library) support sub-second precision (thanks jtiai). I'm not completely sure how much date-string-manipulation remains in the Oracle backend today, but it is certainly still used for database defaults: Oracle doesn't take parameters in DDL statements. As a result of these two factors, when datetimes were set as default column values (which happened a lot with South<0.7.3), the value actually stored in the schema was a string specifying the date-time in a non-default format. Whenever Django connected to the DB, it set the session's date-time format to the "right" one, and so no problems were seen. But when backing up using the oracle "exp" utility -- which, as far as I'm aware, is pretty standard, at least as a developer backing up schemas on their own instance -- it was still these strings that were saved; and when trying to restore with the converse "imp", whose connection is (of course) not controlled by Django, the utility tried to set the date-time defaults by a format that was inappropriate for the values. This usually failed, resulting in partial restores, which lead to a lot of pain. If you're still here, you probably want to know how we solved the problem: Our DBA showed us how to install a database-level trigger to change the format whenever the relevant users logged on. This allowed us to get Oracle's "imp" to use the right date-time formats. However, this is highly non-obvious: I, for one, didn't even know such triggers existed. Thanks for your attention, Shai. -- 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 http://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/201410311734.08971.shai%40platonix.com. For more options, visit https://groups.google.com/d/optout. -- 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 http://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/7CDBD1EFB267CD41949C704C14E92DBF1A714566%40HELW040.stakes.fi. For more options, visit https://groups.google.com/d/optout.