主页 / user-270239

Rafael Chielle's questions

Martin Hope
Rafael Chielle
Asked: 2023-03-08 04:12:54 +0800 CST
  • 5

当执行包含 WITH 和超过一千个 UNION ALL 的查询时，PostgreSQL 版本 14 或更高版本使用所有服务器内存，直到它进入恢复模式。在版本 11 的测试中，不会发生此行为。

WITH DADOS as (
SELECT   
             CAST( 37959.0 as NUMERIC(9, 0)) id ,
             CAST( 1.0 as NUMERIC(9, 0)) emp,
             CAST( 12884.0 as NUMERIC(9, 0)) pro ,
             CAST( 7.891097087431E12 as NUMERIC(18, 0)) gtin,
             to_timestamp( '2023-03-07 12:48:00' , 'YYYY-MM-DD HH24:MI:SS') data3,
             CAST( 0.79 as NUMERIC(18, 8)) preco ,
             CAST( 10.0 as NUMERIC(18, 8)) valor1 ,
             CAST( 0.0 as NUMERIC(18, 8)) preco,
             CAST( 0.0 as NUMERIC(18, 8)) valor3,
             CAST( '1' as NUMERIC(1, 0)) flag ,
             0,
             current_timestamp,
             current_timestamp,
             CAST( 'USER' as varchar(255)) usr,
             to_timestamp( '2023-03-07 23:59:00' , 'YYYY-MM-DD HH24:MI:SS') data1,
             CAST( 9.0 as NUMERIC(18, 8)) codigo2,
             CAST( 5.0 as NUMERIC(1)) opt,
             to_timestamp( '2023-03-07 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') data2,
             CAST( '2' as NUMERIC(1)) codigo3
UNION ALL

... over 2k  unions all..
 
SELECT   
             CAST( 37975.0 as NUMERIC(9, 0)) id ,
             CAST( 1.0 as NUMERIC(9, 0)) emp,
             CAST( 29121.0 as NUMERIC(9, 0)) pro ,
             CAST( 7.896011105178E12 as NUMERIC(18, 0)) gtin,
             to_timestamp( '2023-03-07 12:48:00' , 'YYYY-MM-DD HH24:MI:SS') data3,
             CAST( 1.98 as NUMERIC(18, 8)) preco ,
             CAST( 10.0 as NUMERIC(18, 8)) valor1 ,
             CAST( 0.0 as NUMERIC(18, 8)) preco,
             CAST( 0.0 as NUMERIC(18, 8)) valor3,
             CAST( '1' as NUMERIC(1, 0)) flag ,
             0,
             current_timestamp,
             current_timestamp,
             CAST( 'USER' as varchar(255)) usr,
             to_timestamp( '2023-03-07 23:59:00' , 'YYYY-MM-DD HH24:MI:SS') data1,
             CAST( 9.0 as NUMERIC(18, 8)) codigo2,
             CAST( 5.0 as NUMERIC(1)) opt,
             to_timestamp( '2023-03-07 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') data2,
             CAST( '2' as NUMERIC(1)) codigo3 
             )
        select
    *
from
    dados
linux