Redundant migrations for fields' default values

[tlaitinen]

I added a Boolean field "authorized" in the User table defined in "config/models" using a clean scaffolded site.

-- snip --
User
ident Text
password Text Maybe
authorized Bool default=True
UniqueUser ident
-- snip --

As expected, upon next startup of the development server, yesod migrates the table correctly:

Starting devel application
Devel application launched: http://localhost:3000
Migrating: ALTER TABLE "user" ADD COLUMN "authorized" BOOLEAN NOT NULL DEFAULT True

However, when the development server is launched for the second time, it sets the default value again even if it has not been changed:

Starting devel application
Devel application launched: http://localhost:3000
Migrating: ALTER TABLE "user" ALTER COLUMN "authorized" SET DEFAULT True

Is this intended behavior? Is it not possible to retrieve the default value from the database, so yesod takes a conservative approach and rewrites the default value every time?

[snoyberg]

I think this is a capitalization issue: you've specified True, but I think PostgreSQL returns it as true. Can you try changing to default=true and see what happens?

[tlaitinen]

Changing the value True to true did the trick. Yesod does not try to migrate the default value anymore.

However, I experimented with a Text field. I added a new field, "language", to User-table:
-- snip --
User
ident Text
password Text Maybe
authorized Bool default=true
language Text default='fi'
UniqueUser ident
-- snip --

The initial migration works as expected.

Starting devel application
Devel application launched: http://localhost:3000
Migrating: ALTER TABLE "user" ADD COLUMN "language" VARCHAR NOT NULL DEFAULT 'fi'

However, on subsequent executions, the default value is migrated again:

Starting devel application
Devel application launched: http://localhost:3000
Migrating: ALTER TABLE "user" ALTER COLUMN "language" SET DEFAULT 'fi'

The PostgreSQL schema is following:

id | integer | not null default nextval('user_id_seq'::regclass)
ident | character varying | not null
password | character varying |
authorized | boolean | not null default true
language | character varying | not null default 'fi'::character varying

It does not seem to be possible to specify 'fi''::character varying as the default value due to the white space. Any ideas on how I could specify a default value for a Text field without repeated database migrations upon startup?

[snoyberg]

Try:

"default='fi'::character varying"

[tlaitinen]

Okay, now the User table is defined as follows:

User
ident Text
password Text Maybe
authorized Bool default=true
language Text default="'fi'::character varying"
UniqueUser ident

I get the following error:

Starting devel application
Devel application launched: http://localhost:3000
Migrating: ALTER TABLE "user" ALTER COLUMN "language" SET DEFAULT "'fi'::character
devel.hs: SqlError {sqlState = "42601", sqlNativeError = 7, sqlErrorMsg = "execute: PGRES_FATAL_ERROR: ERROR: unterminated quoted identifier at or near ""'fi'::character"\nLINE 1: ... TABLE "user" ALTER COLUMN "language" SET DEFAULT "'fi'::cha...\n ^\n"}
Exit code: ExitFailure 1

[snoyberg]

That doesn't look like the same code I provided, try putting the double quotes before default.

[meteficha]

FWIW, persistent-mysql usually is broken in this regard (i.e. it will try to reset the default). This has been a low priority bug for me since I don't run persistent migrations automatically on server startup so I never tried to fix it for good. However, I don't remember right now exactly why persistent-mysql doesn't understand enough of MySQLs default in order to check that it really is the same value.

[tlaitinen]

Oops. I tried now with

User
ident Text
password Text Maybe
authorized Bool default=true
language Text "default='fi'::character varying"
UniqueUser ident

and it seems to work. The migration is run only once. Thanks for the help.

While the ability to supply backend-specific values as the default value is very convenient (for example, now() for datetime-fields), in most cases I'm supplying concrete values for bools, enums, and strings. It would thus be nice to supply a backend-independent Haskell value as the default and backend-specific default value only when explicitly specified.

[snoyberg]

The reason the default has to be backend-specific is because it's only relevant for the backend, it never affects live Haskell code. Persistent itself never actually uses the default; instead, migrations will use it when create new columns or changing a column from NULL to NOT NULL, and raw SQL code could leverage the presence of a default.

In other words, I agree with you that it would be nice, but I don't think it's possible.