persistent-sqlite regression between 2.6.0.1 and 2.6.2

[osa1]

Here's program that works with persistent-2.5 and persistent-sqlite-2.6 but doesn't work with persistent-2.7.1 and persistent-sqlite-2.6.3:

{-# LANGUAGE ConstraintKinds            #-}
{-# LANGUAGE DeriveGeneric              #-}
{-# LANGUAGE EmptyDataDecls             #-}
{-# LANGUAGE FlexibleContexts           #-}
{-# LANGUAGE GADTs                      #-}
{-# LANGUAGE GeneralizedNewtypeDeriving #-}
{-# LANGUAGE LambdaCase                 #-}
{-# LANGUAGE MultiParamTypeClasses      #-}
{-# LANGUAGE OverloadedStrings          #-}
{-# LANGUAGE QuasiQuotes                #-}
{-# LANGUAGE ScopedTypeVariables        #-}
{-# LANGUAGE TemplateHaskell            #-}
{-# LANGUAGE TupleSections              #-}
{-# LANGUAGE TypeFamilies               #-}

module Main where

import Control.Monad
import Control.Monad.Logger
import Control.Monad.Reader
import Control.Monad.Trans.Resource
import Data.Pool
import qualified Data.Text as T
import Database.Persist
import Database.Persist.Sqlite hiding (SqlBackend)
import qualified Database.Persist.Sqlite as Sqlite
import Database.Persist.TH
import Database.Sqlite (Error (..), SqliteException (..))

share [mkPersist sqlSettings, mkMigrate "migrateDb"] [persistLowerCase|

User
  username T.Text

Message
  user UserId

|]

-- On the second run move this line to `Message` table above:
-- new_field T.Text Maybe default="null"

main :: IO ()
main = runStdoutLoggingT $ do
    conn_pool <- createSqlitePool "db" 15
    withResource conn_pool $ runReaderT $ do
      void (runMigrationSilent migrateDb)

This is a migration program so we run it twice. On the second run we move the commented-out line to Message table as mentioned in the code.

On older persistent migration works as expected:

[Debug#SQL] CREATE TEMP TABLE "message_backup"("id" INTEGER PRIMARY KEY,"user" INTEGER NOT NULL REFERENCES "user","new_field" VARCHAR NULL DEFAULT null); []
[Debug#SQL] INSERT INTO "message_backup"("id","user") SELECT "id","user" FROM "message"; []
[Debug#SQL] DROP TABLE "message"; []
[Debug#SQL] CREATE TABLE "message"("id" INTEGER PRIMARY KEY,"user" INTEGER NOT NULL REFERENCES "user","new_field" VARCHAR NULL
DEFAULT null); []
[Debug#SQL] INSERT INTO "message" SELECT "id","user","new_field" FROM "message_backup"; []
[Debug#SQL] DROP TABLE "message_backup"; []

On newer persistent it doesn't:

[Debug#SQL] CREATE TEMP TABLE "message_backup"("id" INTEGER PRIMARY KEY,"user" INTEGER NOT NULL REFERENCES "user","new_field" VARCHAR NULL DEFAULT null); []
[Debug#SQL] INSERT INTO "message_backup"("id","user") SELECT "id","user" FROM "message"; []
bug: SQLite3 returned ErrorError while attempting to perform prepare "INSERT INTO \"message_backup\"(\"id\",\"user\") SELECT \"id\",\"user\" FROM \"message\"": no such table: temp.user

cabal and stack files:

name:                persistent-bug
version:             0.1.0.0
-- synopsis:
-- description:
homepage:            https://github.com/githubuser/persistent-bug#readme
license:             BSD3
license-file:        LICENSE
author:              Author name here
maintainer:          example@example.com
copyright:           2017 Author name here
category:            Web
build-type:          Simple
extra-source-files:  README.md
cabal-version:       >=1.10

executable bug
  hs-source-dirs:      app
  main-is:             Main.hs
  ghc-options:         -threaded -rtsopts -with-rtsopts=-N

  build-depends:
    base,
    monad-logger,
    mtl,
    persistent,
    persistent-sqlite,
    persistent-template,
    resourcet,
    resource-pool,
    text

  default-language:    Haskell2010

# buggy
# resolver: lts-9.14
# resolver: nightly-2017-11-24
# works
resolver: lts-7.14
packages:
- .

[osa1]

Just confirmed that it also fails the same way with lts-8.14 (which has persistent-2.6.1 and persistent-sqlite-2.6.2).

[osa1]

Confirmed that the problem is in persistent-sqlite, if I use latest nightly resolver with persistent-sqlite-2.6 it works.

resolver: nightly-2017-11-24
packages:
- .
extra-deps:
- persistent-sqlite-2.6

[osa1]

persistent-sqlite-2.6.0.1 also works

[osa1]

It turns out this is because of #646

Using this line fixes it

    conn_pool <- createSqlitePoolFromInfo (set fkEnabled False (mkSqliteConnectionInfo "db")) 15

[paul-rouse]

Thanks for reporting and diagnosing this.

I think there is still a bug: migration creates a temporary table with a foreign-key constraint which cannot be satisfied, so migration needs to be updated in view of #646. You shouldn't be forced to switch off foreign-key checking to make it work!

I'll reopen this issue, and take a look at the code over the weekend.

[paul-rouse]

@osa1 there is now a new release on hackage which fixes this issue (version 2.6.3.1). Thanks again for spotting it.

[osa1]

Thanks!