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 [email protected].
To post to this group, send email to [email protected].
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.
