Scala+Slick删除PostgreSQL记录时传递变量至触发器的问题排查

问题 #

我用Scala-Slick实现了PostgreSQL的记录删除方法，原代码运行正常：

def deleteRecord(username: String, id: String, mappingRequest: MappingRequest): DBIO[Option[String]] = {
val tbl = MySchema.fullTblName(config)
val action =
  sql"""
     DELETE FROM #$tbl WHERE (#${MySchema.columns.id}) = $id
     OR (SELECT false WHERE 'xusername' = $username)
     RETURNING #${MySchema.columns.id}, $username;
   """.as[(String, String)].headOption

  action.flatMap {
    case Some((result, returnedUserId)) =>
      DBIO.successful(Some(result))
    case None =>
      DBIO.failed(new Exception("Delete failed, probably due to id not found in table"))
  }
}

现在需要把username传递给PostgreSQL触发器函数（用于写入历史表，username不属于删除操作的字段），尝试添加会话变量传递时遇到以下问题：

尝试代码 #

def deleteRecord(username: String, id: String, mappingRequest: MappingRequest): DBIO[Option[String]] = {
    val tbl = MySchema.fullTblName(config)
    val setSessionVariable = sqlu"SET my_variable = $username"
    val deleteAction =
      sql"""
         DELETE FROM #$tbl WHERE (#${MySchema.columns.id}) = $id
         OR (SELECT false WHERE 'xusername' = $username)
         RETURNING #${MySchema.columns.id}, $username;
       """.as[(String, String)].headOption

    for {
      _ <- setSessionVariable
      actionResult <- deleteAction
    } yield actionResult match {
      case Some((result, returnedUserId)) =>
        Some(result)
      case None =>
        throw new Exception("Delete failed, probably due to id not found in table")
    }
  }

遇到的错误 #

1. 使用参数化SET语句时，抛出语法错误：

ERROR: syntax error at or near "$1"
  Position: 19  Error Code: 42601

2. 将SET语句硬编码为sqlu"SET my_variable = 'myUser'"后，抛出参数未识别错误：

ERROR: unrecognized configuration parameter "my_variable"

我想知道为什么PostgreSQL识别不了会话变量，是否需要预先定义？以及如何在Scala-Slick中把username传递给触发器？

解决方案 #

问题根源解析 #

1. 参数化SET语句失效：PostgreSQL的SET属于元命令，不支持参数化占位符（$1），仅DML语句（SELECT/INSERT/UPDATE/DELETE）支持参数化，因此直接用sqlu"SET my_variable = $username"会触发语法错误。
2. 自定义会话变量未正确定义：PostgreSQL不允许直接设置未注册的全局配置参数，自定义会话变量需要使用SET LOCAL（仅当前事务有效）或者预先注册临时参数，且建议添加前缀避免和系统参数冲突。

可行方案：用SET LOCAL+current_setting传递变量 #

这是最简洁安全的方式，变量仅在当前事务内有效，无需预先注册。

1. 修正Scala-Slick中的变量设置逻辑

使用Slick的LiteralColumn安全拼接用户输入（避免SQL注入），设置事务级别的会话变量：

def deleteRecord(username: String, id: String, mappingRequest: MappingRequest): DBIO[Option[String]] = {
  val tbl = MySchema.fullTblName(config)
  // 安全设置事务级会话变量，前缀app_避免和系统参数冲突
  val setSessionVar = sqlu"SET LOCAL app.username = ${LiteralColumn(username)}"
  val deleteAction = sql"""
    DELETE FROM #$tbl WHERE #${MySchema.columns.id} = $id
    RETURNING #${MySchema.columns.id}
  """.as[String].headOption

  // 确保SET和DELETE在同一个事务内执行
  for {
    _ <- setSessionVar
    result <- deleteAction
  } yield result match {
    case Some(recordId) => Some(recordId)
    case None => throw new Exception("Delete failed: record not found")
  }
}

2. 在PostgreSQL触发器函数中读取变量

触发器里通过current_setting获取会话变量：

CREATE OR REPLACE FUNCTION delete_history_trigger()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO history_table (record_id, username, deleted_at)
  VALUES (OLD.id, current_setting('app.username'), NOW());
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到目标表
CREATE TRIGGER after_delete_trigger
AFTER DELETE ON your_target_table
FOR EACH ROW EXECUTE FUNCTION delete_history_trigger();

替代方案：临时表传递变量 #

如果不想用会话变量，可以在事务内创建临时表存储username，触发器从临时表读取：

// Scala-Slick代码
val createTempTable = sqlu"""
  CREATE TEMP TABLE IF NOT EXISTS temp_delete_user (username TEXT)
  ON COMMIT DROP
"""
val insertUser = sqlu"INSERT INTO temp_delete_user VALUES ($username)"

// 触发器函数中读取
// SELECT username FROM temp_delete_user LIMIT 1

关键注意事项 #

• SET LOCAL仅在当前事务内有效，事务结束后变量自动失效，适合单次操作传参
• 自定义变量必须加前缀（如app_），避免与PostgreSQL系统参数冲突
• 必须保证SET和DELETE在同一事务中执行，否则触发器无法读取变量
• 处理用户输入时必须做SQL注入防护，优先使用Slick的LiteralColumn而非手动拼接字符串

内容的提问来源于stack exchange，提问作者Timothy Clotworthy