# PostgreSQL 的 deferred constraint

## 情境

1. 同一個 list 中相同 order 的 item 只能有一個 ([軟刪除](https://github.com/jhawthorn/discard))
2. api 一次會傳送多筆資料同時更新所有資料的 order

### migration

class CreateLists < ActiveRecord::Migration[7.1]
  def change
    create_table :lists do |t|
      t.string :name
      t.datetime :discarded_at
      t.timestamps
    end
  end
end

class CreateItems < ActiveRecord::Migration[7.1]
  def change
    create_table :items do |t|
      t.references :list, null: false
      t.string :content
      t.integer :order, null: false
      t.datetime :discarded_at
      t.timestamps
    end

    add_index :items, %i[list_id order], unique: true, where: 'discarded_at is null'
  end
end

### model

class List < ApplicationRecord
  include Discard::Model

  default_scope { kept }

  has_many :items
  accepts_nested_attributes_for :items
end

class Item < ApplicationRecord
  include Discard::Model

  default_scope { kept }

  belongs_to :list

  validates :order, uniqueness: { scope: :list_id, conditions: -> { where(discarded_at: nil) } }
end

### Controller

class ListsController < ApplicationController
  before_action :set_list

  def update
    @list.update!(lists_params)
    render json: @list.as_json(include: :items)
  end

  private

  def lists_params
    params.require(:list).permit(:name, items_attributes: [:id, :content, :order])
  end

  def set_list
    @list = List.find(params[:id])
  end
end

### API

PATCH `/list/1/items`

body

{
  "list": {
    "name": "xxx",
    "items_attributes": [
      {
        "id": 1,
        "content": "xxx",
        "order": 0
      },
      {
        "id": 2,
        "content": "xxx",
        "order": 1
      }
    ]
  }
}

## 問題

當重新排序的時候會驗證出錯，假如 DB 資料 item id 1 的 order 是 0，item id 2 的 order 是 1，而 API 傳送交換順序的資料，更新資料會因為一筆一筆驗證而出錯 (改用 insert_all 也會出錯)



## DB 規則

| 名稱 | deferred | conditional (where) |
| -------- | -------- | -------- |
| unique ==index== | 不行 | 可以 (稱 [partial index](https://hackmd.io/@cindyliu923/BkF-pw8sT)) |
| unique constraint | 可以 | 不行 |
| exclusive constraint | 可以 | 可以 |

SET CONSTRAINTS：
Currently, only `UNIQUE`, `PRIMARY KEY`, `REFERENCES (foreign key)`, and `EXCLUDE` constraints are affected by this setting. NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). Uniqueness and exclusion constraints that have not been declared DEFERRABLE are also checked immediately.

- 文件
- [Constraints (英)](https://www.postgresql.org/docs/current/ddl-constraints.html)
- [Constraints (中)](https://docs.postgresql.tw/the-sql-language/ddl/constraints)
- [SET CONSTRAINTS](https://www.postgresql.org/docs/current/sql-set-constraints.html)
- [Deferrable SQL Constraints in Depth](https://begriffs.com/posts/2017-08-27-deferrable-sql-constraints.html)
- 討論
- https://dba.stackexchange.com/questions/166082/deferrable-unique-index-in-postgres

## 解法

### 新增 migration 並移除 model 上的驗證

class UpdateItemsConstraint < ActiveRecord::Migration[7.1]
  def change
    remove_index :items, %i[list_id order], unique: true, where: 'discarded_at is null'
    add_exclusion_constraint :items, 'list_id WITH =, "order" WITH =', where: 'discarded_at is null', deferrable: :deferred
  end
end


- 其他相關資料
- https://edgeapi.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/PostgreSQL/SchemaStatements.html
- https://www.shakacode.com/blog/defer-unique-constraints-in-postgresql-with-rails/

### 測試注意

測試時 [DatabaseCleaner](https://github.com/DatabaseCleaner/database_cleaner) 必須使用 `truncation` 的策略才能測試到因為 DB ==deferred== constraint 而 raise 的 error (ex: `PG::ExclusionViolation`)

# rails_helper.rb
RSpec.configure do |config|
  config.before(:suite) do
    DatabaseCleaner.clean_with(:truncation)
  end

  config.before(:each) do
    DatabaseCleaner.strategy = :truncation
    DatabaseCleaner.start
  end

  config.after(:each) do
    DatabaseCleaner.clean
  end
end


`truncation` 和 `transaction` 是 DatabaseCleaner 的兩種清理策略：

- `truncation`
原理：刪除表中的所有數據（用 TRUNCATE 命令）。
優點：徹底清空數據，確保測試環境乾淨。
缺點：速度較慢，特別是對於大數據庫。
- `transaction`
原理：在每個測試開始時啟動一個事務，測試結束後回滾事務。
優點：速度非常快，因為事務的開始和回滾比刪除數據快得多。
缺點：某些數據庫約束（如外鍵約束）在事務內可能不完全起作用。

總結
`truncation`：適合複雜關係和需要徹底清理的情況，但速度較慢。
`transaction`：適合大多數情況，速度快，但對某些約束支持有限。
可以在 example 加上參數調整要用哪個方式清理