Serious bug: Primary-key column configured as readonly are auto-incremented upon UPDATE

[saurabhnanda]

Given this core function - please notice ctKey=()

    updateExisting :: HasDb m => LocalTime -> ClientTagRead -> m [ClientTagRead]
    updateExisting now existingCt = updateRow clientTagTable
      (
        \ctR -> ClientTag
                {
                  ctKey = ()
                , ctCreatedAt = ()
                , ctUpdatedAt = (pgLocalTime now)
                , ctClientId = ctR ^. clientId
                , ctTags = constant $ nub $ (existingCt ^. tags) ++ [newTag ^. tag]
                }
      )
      (\ctR -> (ctR ^. clientId) .== (pgInt8 $ newTag ^. clientId))

... and the supporting types:

type ClientTagRead = ClientTag
  Int64 -- key
  LocalTime --createdAt
  LocalTime --updatedAt
  Int64 -- client Id
  [Tag] -- tags

type ClientTagWrite = ClientTag
  () -- key
  () --createdAt
  LocalTime --updatedAt
  Int64 -- client Id
  [Tag] -- tags

Here's the behaviour I'm seeing -- if the id of the row being updated is 5, after the update it changes to 6 in the DB! I presume this is because the generated SQL has id=DEFAULT or suchlike. And the DEFAULT for the id column is nextval('client_tags_id_seq'::regclass)

[xldenis]

Have you tried changing your definition to make your column optional (so that instead of () you use Nothing) ?

[bergmark]

This is a consequence of #92

[saurabhnanda]

@xldenis @bergmark I tried using (Maybe (Column x)) and using Nothing instead of () as the value. Same buggy behaviour. Only when I specify Just $ row ^. key does it preserve the value.

Very, very bad bug.

[tomjaguarpaw]

Please calm down. readOnly isn't even supported in Opaleye. It was a suggestion I made to @sras on another issue. He claimed it worked and closed the issue. If it doesn't work then feel free to reopen the issue, or even better, fix it.

[saurabhnanda]

So, due to #92 we are forced to provide values for every single field in UPDATE calls. If in the PGWrite version of the record a field is mapped to Maybe (Column x) and the value for this field is a Nothing, the queryRunner maps it to DEFAULT, right? Which forces us to have Just $ pgR ^. existingKey as values for fields that we don't want to change.

What was the situation before #92 @bergmark ?

PS: Does this also mean that it's not really a readonly column issue? Nothing mapping to DEFAULT is the problem here?

[tomjaguarpaw]

Which forces us to have Just $ pgR ^. existingKey as values for fields that we don't want to change.

Correct, with runUpdate you have to wrap all optional fields that you don't want to change in Just. Here is an approach that makes life easier: bf625fb

Does this also mean that it's not really a readonly column issue?

It is a readonly column issue. readonly doesn't do what I expected it to do, sorry. I suggest not to use readonly.

[tomjaguarpaw]

I'm closing this because readonly is not a supported part of Opaleye. All related discussion can continue on #92.