Ruby 一道 PG buffer full 问题

leijun · 2022年11月29日 · 最后由 EvanYa 回复于 2022年12月09日 · 357 次阅读

我现在碰到了一道难题 现在有一个很大的 query 表,里面大概几千万 record 这个表里面有一个 column response 里存的是 xml,

    SELECT count(*),
             CASE WHEN (xpath('/search-results', response::xml))[1]::text::int  = 0 THEN '0'
                  WHEN (xpath('/search-results', response::xml))[1]::text::int BETWEEN 1 AND 10 THEN '1-10' 
                  WHEN (xpath('/search-results', response::xml))[1]::text::int BETWEEN 11 AND 20 THEN '11-20' 
                  WHEN (xpath('/search-results', response::xml))[1]::text::int BETWEEN 21 AND 30 THEN '21-30' 
                  WHEN (xpath('/search-results', response::xml))[1]::text::int BETWEEN 31 AND 40 THEN '31-40' 
                  WHEN (xpath('/search-results', response::xml))[1]::text::int BETWEEN 41 AND 50 THEN '41-50' 
                  WHEN (xpath('/search-results', response::xml))[1]::text::int BETWEEN 51 AND 60 THEN '51-60' 
                  WHEN (xpath('/search-results', response::xml))[1]::text::int BETWEEN 61 AND 70 THEN '61-70' 
                  WHEN (xpath('/search-results', response::xml))[1]::text::int > 70 THEN '+70' 
             END AS RANGE
      FROM queries
      WHERE (xpath('/search-results', response::xml))[1] IS NOT NULL
      GROUP BY RANGE 

result = ActiveRecord::Base.connection.execute(sql)

然后允许这个 rake 的时候,发生问题

rails aborted!
ActiveRecord::StatementInvalid: PG::InvalidXmlContent: ERROR:  invalid XML content
DETAIL:  buffer full
CONTEXT:  parallel worker

这是怎么情况啊? 请教各位大神

你这个问题我没遇见过,但 pg query 问题可以考虑先看一下本身查询的优化,利用 rails-pg-extras 看一下

需要 登录 后方可回复, 如果你还没有账号请 注册新账号