persistent insertKey - Haskell

Welcome to the Functional Programming Zulip Chat Archive. You can join the chat here.

Pavan Rikhi

I'm running a database migration from MySQL -> Postgresql. I want to retain the ID values so I used Persistent's insertKey function to set the IDs when inserting records. However, when the application decides to insert a row(during normal operation, not as part of the data migration), it gets an error since apparently the primary key sequence doesn't get updated when you use insertKey. Is there a simple way to fix this w/ persistent or should I just query the max ID and fix the sequence with some rawsql?

Pavan Rikhi

Related: Can I grab all the EntityDefs for a TH-generated schema? That'll let me just fix every ID sequence instead of having to hardcode the sequence names and manually listing the tables that I use insertKey on...

Pavan Rikhi

Ended up with this:

fixIdSequences :: SqlPersistT IO ()
fixIdSequences = void $
    fixIdSequence @Category >>
    fixIdSequence @Product >>
    fixIdSequence @Page >>
    fixIdSequence @Customer >>
    fixIdSequence @Order
  where
    fixIdSequence :: forall e. (PersistEntity e) => SqlPersistT IO [Single Int]
    fixIdSequence =
        let
            table =
                unDBName $ tableDBName @e undefined
            key =
                unDBName $ fieldDBName $ persistIdField @e
        in
        rawSql
            ( "SELECT setval("
            <> "    pg_get_serial_sequence(?,?), "
            <> "    COALESCE(MAX(" <> key <> "), 1), "
            <> "    MAX(" <> key <> ") IS NOT null) "
            <> "FROM \"" <> table <> "\"")
            [PersistText table, PersistText key]

Wasn't sure how to get unquoted strings or double-quoted table names into rawSql :confused:

Jack Henahan

Does persistent not have prepared statements? Woof

Pavan Rikhi

Well preparing the statement with PersistText renders key_field as 'key_field' & I wasn't sure how to make it be just key_field w/o the single quotes

Pavan Rikhi

And didn't care enough to investigate further.

Pavan Rikhi

And table needs to be rendered as "table" instead of 'table'

Pavan Rikhi

It works and it's a script that only gets run once so :shrug:

James King

iirc, in postgresql-simple, quoted object names such as tables use ?? and values use ?... maybe persistent uses the same convention?

Pavan Rikhi

Nope, it sees ?? as two ? that are side by side

Pavan Rikhi

It uses ?? in the field list for SELECT when you want to return an entire entity.