Artalk 2.8.0 使用PostgreSQL 作为数据存储库时，无法正常工作，Artalk 提示数据库有问题

[aaro-n]

发现

升级2.8.0版本后，Artalk 服务端不能正常显示用户归属地，ArtalkLite的js和css显示为英文，设置中文无效。检查Artalk 服务端后有问题提示。

错误日志

运行平台介绍

服务端：2.8.0
前端：2.8.0（ArtalkLite）
数据库：PostgreSQL 16
服务端运行平台：Docker ，ARM

日志

time="2024-02-02T18:44:23+08:00" level=info msg="File Generated: /data/artalk.yml"
Fri Feb  2 18:44:23 CST 2024 [info] Generate new config file to '/data/artalk.yml'

 ________  ________  _________  ________  ___       ___  __
|\   __  \|\   __  \|\___   ___\\   __  \|\  \     |\  \|\  \
\ \  \|\  \ \  \|\  \|___ \  \_\ \  \|\  \ \  \    \ \  \/  /|_
 \ \   __  \ \   _  _\   \ \  \ \ \   __  \ \  \    \ \   ___  \
  \ \  \ \  \ \  \\  \|   \ \  \ \ \  \ \  \ \  \____\ \  \\ \  \
   \ \__\ \__\ \__\\ _\    \ \__\ \ \__\ \__\ \_______\ \__\\ \__\
    \|__|\|__|\|__|\|__|    \|__|  \|__|\|__|\|_______|\|__| \|__|

Artalk (v2.8.0/46e2a15)

 -> A Self-hosted Comment System.
 -> https://artalk.js.org

-------------------------------


 ┌───────────────────────────────────────────────────┐ 
 │                   Fiber v2.52.0                   │ 
 │              http://127.0.0.1:23366               │ 
 │      (bound on host 0.0.0.0 and port 23366)       │ 
 │                                                   │ 
 │ Handlers ............ 90  Processes ........... 1 │ 
 │ Prefork ....... Disabled  PID ................ 17 │ 
 └───────────────────────────────────────────────────┘ 

ERROR SELECT count(*) FROM "comments" WHERE "comments"."deleted_at" IS NULL ORDER BY created_at DESC [370.4µs] error=ERROR: column "comments.created_at" must appear in the GROUP BY clause or be used in an aggregate function (SQLSTATE 42803)
ERROR SELECT count(*) FROM "comments" WHERE rid = 0 AND "comments"."deleted_at" IS NULL ORDER BY created_at DESC [340.48µs] error=ERROR: column "comments.created_at" must appear in the GROUP BY clause or be used in an aggregate function (SQLSTATE 42803)

问题复现

使用docker compose构建运行环境，按照官方说明创建管理员账户，登录账户，到这时还是正常的，当导入之前备份的数据后，重启服务端，查看评论数据，此时服务端会提示错误日志里的内容。

docker compose

version: '3'
services:
  postgres:
    restart: always
    image: postgres:16
    container_name: postgres
    ports:
      - 5432:5432
    environment:
      - POSTGRES_PASSWORD=vg9QUVgeyjsQq6jwi5PsHZSDKWFReFZzKzNaPZ8X
    volumes:
      - /home/www/postgres/database:/var/lib/postgresql/data
      - /home/www/postgres/data:/home/postgresql

  artalk:
    restart: always
    image: artalk/artalk-go
    container_name: artalk
    ports:
      - "8080:23366"
    environment:
      ATK_HOST: 0.0.0.0
      ATK_APP__KEY: fqAv2uYjvdVN3VWZcHSUSAd7q822JzLo
      ATK_DB_TYPE: pgsql
      ATK_DB_DSN: postgres://postgres:[email protected]:5432/postgres
    volumes:
      - /home/www/postgres/ip2region.xdb:/data/ip2region.xdb
    depends_on:
      - postgres

排查

部署不同版本数据库和 artalk版本，测试结果如下表：

postgres 版本	artalk 版本	问题是否复现
16	2.8.0	问题复现
16	2.7.3	正常
15	2.8.0	问题复现
15	2.7.3	正常

总结

这4次都是使用同一备份文件恢复，2.8.0版本使用postgres15，postgres16，云数据平台supabase和neon都会出现这个问题。

artalk 在使用postgres云数据库supabase和neon时，无法使用连接池功能

5432端口时PG数据库的默认端口，6543一般是PG链接池功能，artalk使用6543端口连接PG数据库时有错误提示。

日志

ERROR SELECT count(*) FROM information_schema.tables WHERE table_schema = CURRENT_SCHEMA() AND table_name = 'sites' AND table_type = 'BASE TABLE' [361.89472ms] error=ERROR: prepared statement "stmtcache_b9d0d734c3c5605ca2f13e779c1c0ae15a5a6e4164e5b9ba" does not exist (SQLSTATE 26000)
ERROR SELECT count(*) FROM information_schema.tables WHERE table_schema = CURRENT_SCHEMA() AND table_name = 'pages' AND table_type = 'BASE TABLE' [242.01356ms] error=ERROR: prepared statement "stmtcache_b9d0d734c3c5605ca2f13e779c1c0ae15a5a6e4164e5b9ba" does not exist (SQLSTATE 26000)
ERROR SELECT count(*) FROM information_schema.tables WHERE table_schema = CURRENT_SCHEMA() AND table_name = 'users' AND table_type = 'BASE TABLE' [244.3866ms] error=ERROR: prepared statement "stmtcache_b9d0d734c3c5605ca2f13e779c1c0ae15a5a6e4164e5b9ba" does not exist (SQLSTATE 26000)
ERROR SELECT count(*) FROM information_schema.tables WHERE table_schema = CURRENT_SCHEMA() AND table_name = 'comments' AND table_type = 'BASE TABLE' [119.88892ms] error=ERROR: prepared statement "stmtcache_b9d0d734c3c5605ca2f13e779c1c0ae15a5a6e4164e5b9ba" already exists (SQLSTATE 42P05)
ERROR SELECT count(*) FROM information_schema.tables WHERE table_schema = CURRENT_SCHEMA() AND table_name = 'notifies' AND table_type = 'BASE TABLE' [241.76096ms] error=ERROR: prepared statement "stmtcache_b9d0d734c3c5605ca2f13e779c1c0ae15a5a6e4164e5b9ba" does not exist (SQLSTATE 26000)
ERROR SELECT * FROM "sites" WHERE name = 'Default Site' AND "sites"."deleted_at" IS NULL ORDER BY "sites"."id" LIMIT 1 [240.98248ms] error=ERROR: prepared statement "stmtcache_48698d1e51016f22cfc8d7cac2d0558112c3fd37f6698acc" does not exist (SQLSTATE 26000)

 ________  ________  _________  ________  ___       ___  __
|\   __  \|\   __  \|\___   ___\\   __  \|\  \     |\  \|\  \
\ \  \|\  \ \  \|\  \|___ \  \_\ \  \|\  \ \  \    \ \  \/  /|_
 \ \   __  \ \   _  _\   \ \  \ \ \   __  \ \  \    \ \   ___  \
  \ \  \ \  \ \  \\  \|   \ \  \ \ \  \ \  \ \  \____\ \  \\ \  \
   \ \__\ \__\ \__\\ _\    \ \__\ \ \__\ \__\ \_______\ \__\\ \__\
    \|__|\|__|\|__|\|__|    \|__|  \|__|\|__|\|_______|\|__| \|__|

Artalk (v2.8.0/46e2a15)

 -> A Self-hosted Comment System.
 -> https://artalk.js.org

-------------------------------


 ┌───────────────────────────────────────────────────┐ 
 │                   Fiber v2.52.0                   │ 
 │              http://127.0.0.1:23366               │ 
 │      (bound on host 0.0.0.0 and port 23366)       │ 
 │                                                   │ 
 │ Handlers ............ 90  Processes ........... 1 │ 
 │ Prefork ....... Disabled  PID ................. 9 │ 
 └───────────────────────────────────────────────────┘ 

time="2024-02-02T20:12:40+08:00" level=info msg="File Generated: /data/artalk.yml"
Fri Feb  2 20:12:40 CST 2024 [info] Generate new config file to '/data/artalk.yml'
ERROR SELECT count(*) FROM information_schema.tables WHERE table_schema = CURRENT_SCHEMA() AND table_name = 'sites' AND table_type = 'BASE TABLE' [236.08128ms] error=ERROR: prepared statement "stmtcache_1" does not exist (SQLSTATE 26000)
ERROR SELECT count(*) FROM information_schema.tables WHERE table_schema = CURRENT_SCHEMA() AND table_name = 'pages' AND table_type = 'BASE TABLE' [115.18668ms] error=ERROR: prepared statement "stmtcache_1" does not exist (SQLSTATE 26000)
ERROR SELECT count(*) FROM information_schema.tables WHERE table_schema = CURRENT_SCHEMA() AND table_name = 'comments' AND table_type = 'BASE TABLE' [120.70604ms] error=ERROR: prepared statement "stmtcache_1" does not exist (SQLSTATE 26000)
ERROR SELECT count(*) FROM information_schema.tables WHERE table_schema = CURRENT_SCHEMA() AND table_name = 'notifies' AND table_type = 'BASE TABLE' [115.6728ms] error=ERROR: prepared statement "stmtcache_1" does not exist (SQLSTATE 26000)
ERROR SELECT count(*) FROM information_schema.tables WHERE table_schema = CURRENT_SCHEMA() AND table_name = 'votes' AND table_type = 'BASE TABLE' [115.11084ms] error=ERROR: prepared statement "stmtcache_1" does not exist (SQLSTATE 26000)
ERROR SELECT * FROM "sites" WHERE name = 'Default Site' AND "sites"."deleted_at" IS NULL ORDER BY "sites"."id" LIMIT 1 [234.84924ms] error=ERROR: prepared statement "stmtcache_2" does not exist (SQLSTATE 26000)

 ________  ________  _________  ________  ___       ___  __
|\   __  \|\   __  \|\___   ___\\   __  \|\  \     |\  \|\  \
\ \  \|\  \ \  \|\  \|___ \  \_\ \  \|\  \ \  \    \ \  \/  /|_
 \ \   __  \ \   _  _\   \ \  \ \ \   __  \ \  \    \ \   ___  \
  \ \  \ \  \ \  \\  \|   \ \  \ \ \  \ \  \ \  \____\ \  \\ \  \
   \ \__\ \__\ \__\\ _\    \ \__\ \ \__\ \__\ \_______\ \__\\ \__\
    \|__|\|__|\|__|\|__|    \|__|  \|__|\|__|\|_______|\|__| \|__|

Artalk (v2.7.3/92ff757)

 -> A Self-hosted Comment System.
 -> https://artalk.js.org

-------------------------------


 ┌───────────────────────────────────────────────────┐ 
 │                   Fiber v2.49.2                   │ 
 │              http://127.0.0.1:23366               │ 
 │      (bound on host 0.0.0.0 and port 23366)       │ 
 │                                                   │ 
 │ Handlers ............ 78  Processes ........... 1 │ 
 │ Prefork ....... Disabled  PID ................ 17 │ 
 └───────────────────────────────────────────────────┘ 

总结

测试2.8.0，2.7.4，2.7.0，2.6.4，2.6.2这几个版本都会出现日志里的内容，使用的端口是6543，平台是supabase，在测试这几个版本时，supabase里的表已经创建，每次都要手动清空表，只是运行，没有创建管理员账户，创建管理员还会有其他错误提示。

Artalk 2.8.0 ArtalkLite的js和css显示为英文

不知道是不是受问题1的影响，评论框显示为英文，设置为中文时无效

[qwqcode]

你好，感谢提供详细的排查日志和说明！

问题 1：PostgreSQL ERROR SELECT count(*) FROM 问题

该问题已在 v2.8.1 修复

问题 2：当 PG 连接池为事务模式 (Transaction mode) 时，预编译语句 (Prepared Statement) 功能不可用

你可以使用 5432 端口连接，以 会话模式 (Session mode) 访问连接池，或者禁用 预编译语句 功能。

如需使用事务模式，为了解决这个问题，v2.8.1 更新增加了 db.prepare_stmt 配置项，可以通过 ATK_DB_PREPARE__STMT=0 环境变量禁用 预编译语句 功能。

附加说明：

Supabase 云数据库底层基于 Postgres，启用连接池功能后，通过 5432 端口连接是 回话模式 的连接池、6543 是 事务模式 的连接池。

根据 Supabase 提供的说明，事务模式的连接池不支持预编译语句

参考：https://supabase.com/docs/guides/database/connecting-to-postgres#connection-pooler

问题 3：语言设置不生效

测试语言切换正常，未能复现这个问题

[aaro-n]

我已测试2.8.1版本，问题已经解决，此问题关闭，另外，前端引用ArtalkLite 2.8.1版本的css和js时默认显示的语言是英文，需添加locale: 'zh-CN',解决。最后祝开发者春节愉快。