Setting database default values in migrations (postgres)

[Peter Coles]

Hey all,

I'm interested in getting database migrations to support setting database-level default values. I've heard talk of this being a hotly contested issue in the past, but I haven't been able to find the actual conversations, so far this is the best thread I've found and I've already asked about it in django-users.

Instead of just asking for this feature, I've gone ahead and created a new db backend that is almost exactly the `django.db.backends.postgresql_psycopg2` module, but with two key code blocks removed (that appear to be the logic to remove default values from the database).

Here's the repo: https://github.com/ringly/django-postgres-dbdefaults

and here it is in pypi: https://pypi.python.org/pypi/django-postgres-dbdefaults/0.0.1

I'd love to hear feedback/thoughts/concerns.

-Peter

[Andrew Godwin]

Hi Peter,

The main reason this hasn't been done in the past was that Django has never made use of database defaults, mostly for the reason described in that thread - that Django is sufficiently expressive with its defaults (you can pass in any callable) that we can't represent them in the database. The only reason they're ever set at all is because we need them to add columns, and even then I'd considered a mode where django adds all columns as NULL and calls the default once per row rather than doing one call for the whole set of rows.

I'm not totally against the idea, but I'd want to see a convincing method as to how you detect and deal with defaults that are not expressible in the database (for example, what if someone does the very common default=datetime.datetime.now). Obviously all calls through Django will still work as intended, but other database users will have entirely the wrong value in that field whenever they insert rows but no errors to tell them about it (and if you don't have other database users than Django, why do you need the defaults in there in the first place?)

Andrew

--
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-develop...@googlegroups.com.
To post to this group, send email to django-d...@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/5bf3f984-30fb-4adf-a992-616cbdedd864%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Peter Coles]

Thanks for the quick response! I just tried datetimefields with `datetime.datetime.now` and `timezone.now` as defaults (and another one with null=True, just to see). They just evaluated the callables and set those as static default values, e.g., the code generated was this:

BEGIN;

ALTER TABLE "customers" ADD COLUMN "created_datetime_now" timestamp with time zone DEFAULT '2014-10-29 13:31:34.519110+00:00' NOT NULL;

ALTER TABLE "customers" ADD COLUMN "created_timezone_now" timestamp with time zone DEFAULT '2014-10-29 13:31:34.607064+00:00' NOT NULL;
ALTER TABLE "customers" ADD COLUMN "created_null_true" timestamp with time zone NULL;

COMMIT;

This isn't ideal, but doesn't look too far off from what the existing migration does anyways. For example, I tested adding a not null boolean column with a default value of false to a table that had pre-existing rows, and the SQL for it looks like this:

BEGIN;

ALTER TABLE "temp_table" ADD COLUMN "temp" boolean NOT NULL DEFAULT false;

ALTER TABLE "temp_table" ALTER COLUMN "temp" DROP DEFAULT;

COMMIT;

If I then do a select from that table, I see that all existing columns have taken on the default value of false for the "temp" column.

db=# SELECT "temp" FROM "temp_table" LIMIT 5;

 temp

------

 f

 f

 f

 f

 f

(5 rows)

Even if I change the add column line to be nullable (`ALTER TABLE "temp_table" ADD COLUMN "temp" boolean NULL DEFAULT false`), it still introduces the default value to existing rows.

Given how this works in practice, I'd either label that a bug in the current implementation or suggest that it's one more reason that putting defaults in the db isn't that big of a deal.

On the other hand I haven't played around with callables for default values much more than with timestamps, are there some that might actually raise exceptions?

I'll keep playing around with this and come back later when I have more input on using it in practice and probably find some lurking bug somewhere. Worst case, other people who really want this functionality could use this as a 3rd-party dependency. Regardless, I think the docs should be clearer about defaults not getting set in the db.

[Andrew Godwin]

Well, the one big problem with callable defaults is people who use them for UNIQUE fields (e.g. they're using uuid as the value or something). These migrations will apply fine to empty tables - as there's no data to cause non-unique issues with - but your solution would leave an entirely invalid default in there.

If this does happen, I'd probably want some way to declare what defaults to keep. (South actually used to have this with a keep_default option on the add_column method but it was kind of unmaintained)

Andrew

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/92306a2b-11f4-49c0-af55-4203f7d49fb4%40googlegroups.com.

[Peter Coles]

Interesting scenario. However, the same example seems entirely broken in the existing django migrations with `null=False` and a default too, right?

If so, maybe our current lens is a bit too myopic, and the UNIQUE scenario is a separate problem where model updates on existing tables can sometimes create invalid migrations?

Definitely worth adding into my README though. Thank you for the great input so far!

[Andrew Godwin]

Well, it's not a problem for anything done through Django as it'll always do the default correctly; the existing migrations don't do it right either, for sure, but they'll either error on application or work and everything's fine.

Leaving the defaults in there would mean that non-Django code trying to add rows will start getting integrity errors when it tries to add the rows, for a non-obvious reason. I'm just not sure of the use case of database defaults in this kind of scenario.

Andrew

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/84a7b4e9-49e5-46cf-b738-f04bdb14f495%40googlegroups.com.

[Shai Berger]

On Thursday 30 October 2014 23:00:18 Andrew Godwin wrote:
>
> If this does happen, I'd probably want some way to declare what defaults to
> keep. (South actually used to have this with a keep_default option on the
> add_column method but it was kind of unmaintained)
>

IIRC, in the few South releases between 0.7.3 and 0.8.2 we killed the
functionality, and only kept accepting the parameters to avoid breakage of old
migrations.

Defaults-in-the-DB may be OK on Postgres (I'm not fluent enough in it to tell),
but they lead to very odd problems on Oracle, e.g, requiring special, non-
obvious actions in order to make backups possible. I'm really not sure about
the other backends; and I think the behavior in this respect should be uniform
across backends (and welcoming to 3rd-party backends as well).

Peter, you said you're "interested in getting database migrations to support
setting database-level default values", but you haven't said why; unless
there's a convincing use-case, I'm going to be -1 on this (per the new rules,
this is no longer a veto, but still).

I would be much less opposed to a specific default-setting operation in
migrations -- that is, allowing users to explicitly set db-level defaults if
they really want to; my main concern is with the automatic translation of a
model-level default to a database-level one.

Shai.

[Jon Dufresne]

On Fri, Oct 31, 2014 at 6:48 AM, Shai Berger <sh...@platonix.com> wrote:
> Peter, you said you're "interested in getting database migrations to support
> setting database-level default values", but you haven't said why; unless
> there's a convincing use-case, I'm going to be -1 on this (per the new rules,
> this is no longer a veto, but still).

I am not the original poster, but, FWIW, I too would benefit from this feature.

My Django application interacts with an existing Legacy application
and database. These two applications share the existing database.
Parts of the Legacy application have been ported to Django, other
parts haven't. The existing Legacy application and legacy scripts
relied on these database defaults to handle some data. I've made an
effort to shape the database and Legacy application to the "Django
way", but sometimes it is inconvenient given the existing Legacy
compatibility.

For my particular use case, just handling "primitave" values would be
sufficient: True, False, numbers, text. I understand arbitrary Python
functions can't be easily represeted by databases in a cross platform
way, but that is already beyond the common case. Those functions would
need to be implemented by the Legacy application already.

As far as I'm concerned, Django's ORM doesn't even need to rely on
these defaults when creating objects, but if the migration system
_created_ them (and kept them up to date) it would be very helpful to
people in my situation.

Cheers,
Jon

[Andrew Godwin]

So, bear in mind that you can easily set these defaults yourself in a migration with RunSQL if you need them for legacy purposes; that way they'll get applied, we don't need to add more code to Django, and it works fine for simple plain defaults without the need for a system where Django tries to work out if the default is safe or not.

Andrew

--
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-develop...@googlegroups.com.
To post to this group, send email to django-d...@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/CADhq2b7wNsZfoY9Gnq2QejigOCCO08uzQtmo7ZK8AX1whHnSRg%40mail.gmail.com.

[Jon Dufresne]

On Fri, Oct 31, 2014 at 9:46 AM, Andrew Godwin <and...@aeracode.org> wrote:
> So, bear in mind that you can easily set these defaults yourself in a
> migration with RunSQL if you need them for legacy purposes; that way they'll
> get applied

Absolutely. I effectively have such a system in place at the moment.

But, my point is I am also making an effort to match Django's expected
schema while moving away from the legacy schema. I would prefer not to
drift too far from Django's expectations as the goal is move entirely
to Django. This is just one more thing to keep track of and handle
semi-manually.

All I'm saying is that if the described feature existed, it would
benefit me and others that share my use case.

[Shai Berger]

So, we should be weighing the support-transition-from-legacy use-case against
creating a situation where field defaults get a special treatment if they are
primitive enough (callables are out, but I am really not sure about other
complex objects -- GIS would probably gain a new dimension of fun if it were
to deal with defaults, even when not callable).

I think the correct way forward for migrations is to keep as it does today --
requiring users to explicitly ask for db defaults (we could, as I said
earlier, give it a nicer API than RunSQL).

As for "Django's expectations" -- while I don't think we should generate db
defaults unless specifically asked to, I don't see where such defaults could
get in our way. If you ever run into a situation where Django mishandles some
table because it has defaults, that is almost for sure a bug.

My 2 cents,
Shai.

[Sam Baron]

Greetings, I know this is an old thread, but it's an issue I am coming up against and hopefully I can restart the conversation.  I do think it's important that we use as many native database features as possible for application portability, extensibility, and performance.

I have a different solution to the database default problem.  Why not handle it like SQLAlchemy?  There are two field properties for default - one applied at model instance (current Django default behavior) and one sent to the database.  In SQLAlchemy, it's called 'server_default', but for Django, I think 'db_default' would be a more appropriate name. 

So for example:
integer_column = models.IntegerField(default=0)
OR
integer_column = models.IntegerField(db_default="0")

And I agree that callables would not be included in 'db_default'.  This new property would only be for pushing SQL.  With this solution, I think you avoid user errors by keeping the two defaults behavior separate.  Only the advanced database folks will end up using it.

I have made the relatively minor code changes on my end.  I am in the process and starting a new project and will keep testing it out.  I am curious if this would be an acceptable solution.

Thanks,
Sam

[Marcin Nowak]

Simply do not use Django builtin migrations. Consider using Liquibase (maybe with Liquimigrate wrapper for Django) and feel freedom.

BR

Marcin

[Andrew Godwin]

Hi Sam,

That was also my idea for some time during development; I'm open to the idea, though it does mean bringing database defaults into Django as an overall concept, with all that that entails (e.g. documenting which default takes precedence; dealing with database-specific defaults; how to represent SQL functions; etc.).

If you're interested in whipping up a rough proposal, though, I'd be happy to look over it - people have been asking for this since migrations came around (and long before, too), as while Django never needs DB defaults anything else using the database does (plus some concurrency issues with default functions can be solved by moving them to the DB layer).

Andrew

--

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-develop...@googlegroups.com.
To post to this group, send email to django-d...@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/e7d99cf6-c6a5-4d32-994c-7dd4e07ad478%40googlegroups.com.