PostgreSQL 表要大吗？内存不足？

Question

我有一个大约有 380 万行的表。当我查询整个表时，我得到

ERROR: value overflows numeric format

指用户定义的函数返回的值。
但是如果我将 table 大致分成两半(见下文)，一切正常。

SELECT day,item,price,
    CAST(my_func(price) OVER (PARTITION BY item ORDER BY day) AS numeric(8,2)),
    FROM my_table
    --WHERE day < '3/1/2013';
    --WHERE day >= '3/1/2013';

带有 WHERE 的声明子句执行没有错误。

价格是 numeric(8,2)并且价格列中没有任何数字大于 numeric(8,2) .无论如何，将格式更改为 numeric(20,2)没有区别。

这是表定义:

    CREATE TABLE my_table
    (
        item    character(5)    NOT NULL,
        day     date            NOT NULL,
        price   numeric(8,2),
        CONSTRAINT  my_table_pk PRIMARY KEY (item, day)
    );

...和功能:

    CREATE OR REPLACE FUNCTION my_func2 (avg numeric, IN price numeric)
    RETURNS numeric AS $$
    DECLARE
        alpha numeric;
    BEGIN
        alpha := 2.0/51;
        RETURN
            CASE
                WHEN avg IS NULL THEN price  -- avg is NULL for the first row, so price is returned
                ELSE round((alpha * price + (1-alpha) * avg),2)
            END;
    END;
    $$ LANGUAGE PLPgSQL;

...在聚合中使用:

    CREATE AGGREGATE my_func(numeric) (SFUNC = my_func2, STYPE = numeric);

Answer 1

错误在于您的类型转换操作。 numeric(8,2) 格式非常严格，可能 my_func() 返回的值不满足格式定义。为了证明这一点，请查看以下查询:

select 12.34::numeric(8,2);
 numeric 
---------
   12.34

select 12.345678::numeric(8,2);
 numeric 
---------
   12.35

select 12.3456789::numeric(8,2);
 numeric 
---------
   12.35

select 123456.123456789::numeric(8,2);
  numeric  
-----------
 123456.12

select 1234567.123456789::numeric(8,2);
ERROR:  numeric field overflow
DETAIL:  A field with precision 8, scale 2 must round to an absolute value less than 10^6.

select 1234567.8::numeric(8,2);
ERROR:  numeric field overflow
DETAIL:  A field with precision 8, scale 2 must round to an absolute value less than 10^6.

如果您注意到，返回数字的总位数永远不会超过 8 位数，并且始终具有 2 十进制位数。最后两个查询给出错误，因为它们应该返回超过 8 数字。例如，您希望数字 1234567.123456789 被四舍五入为 1234567.12 但 1234567.12 由 9 数字组成，而不是 8 。即使您有 1234567.8 数字，数字 8 也是如此。这是因为在返回的数值中你想要 2 十进制数字，所以 postgres 应该输出 1234567.80 但同样，这里你有 9 数字而不是 8 。

换句话说，您有不同的方法来解决这个问题:

通过将 my_func() 用于 numeric(16,2) 总位数(选择您想要的数字)，增加您期望从 16 获得的总十进制位数。

使用不同的数字格式，例如 numeric 或 real 。例如:(my_func(price) OVER (PARTITION BY item ORDER BY day))::real

如果您需要特定的十进制长度和无限的整数位数，请尝试 round(my_func(price) OVER (PARTITION BY item ORDER BY day), 2) 。否则，编辑 my_func() 以返回 round(returned_value, 2) 。

为了帮助您理解和/或找出错误的原因，请考虑这一点。对于评估 my_func() 的至少一个或一行，您会在左侧得到一个多于 6 数字的数字。要查找生成错误的行，您只需执行以下查询:

WITH not_casted AS (
    SELECT day,item,price,
    my_func(price) OVER (PARTITION BY item ORDER BY day) AS fprice
    FROM my_table
)
SELECT * FROM not_casted
WHERE fprice > 999999.99

此查询返回的行会生成转换错误。显然，如果您不在 numeric(8,2) 内对 my_func() 进行类型转换，这会起作用，否则会在您类型转换的值上生成错误。不知道函数代码就不可能做出其他假设。

更新

我提出了一个基于模拟的例子。该代码执行以下操作:
- 创建具有不同类型转换和舍入方法的不同 AGGREGATE - 在充当数据的模拟随机样本上执行每个 AGGREGATE(希望如此)。它每天生成 10 价格，每个价格都有自己的 item，用于 10 天的 31 项目总数。为了证明精度损失，这并不重要，所以如果数据没有正确模拟，请不要怪我:)

这是创建四个函数和聚合的代码:

-- typecast price and arithmetics to numeric(8,2)
CREATE OR REPLACE FUNCTION my_func_numeric_8_2_a (avg numeric(8,2), IN price numeric(8,2))
RETURNS numeric(8,2) AS $$
DECLARE
    alpha numeric;
BEGIN
    alpha := 2.0/51;
    RETURN
        CASE
            WHEN avg IS NULL THEN price
            ELSE (alpha * price + (1-alpha) * avg)::numeric(8,2)
        END;
    END;
$$ LANGUAGE PLPgSQL;
CREATE AGGREGATE my_func_numeric_8_2(numeric(8,2)) (SFUNC = my_func_numeric_8_2_a, STYPE = numeric(8,2));


-- typecast price and arithmetics to numeric and round(arithmetics, 2)
CREATE OR REPLACE FUNCTION my_func_numeric_round_a(avg numeric, IN price numeric)
RETURNS numeric AS $$
DECLARE
    alpha numeric;
BEGIN
    alpha := 2.0/51;
    RETURN
    CASE
        WHEN avg IS NULL THEN price
            ELSE round((alpha * price + (1-alpha) * avg), 2)
        END;
    END;
$$ LANGUAGE PLPgSQL;
CREATE AGGREGATE my_func_numeric_round(numeric) (SFUNC = my_func_numeric_round_a, STYPE = numeric);

-- no typecast (double precision type)
CREATE OR REPLACE FUNCTION my_func_dp_a(avg double precision, IN price double precision)
RETURNS double precision AS $$
DECLARE
    alpha double precision;
BEGIN
    alpha := 2.0/51;
    RETURN
    CASE
        WHEN avg IS NULL THEN price
            ELSE (alpha * price + (1-alpha) * avg)
        END;
    END;
$$ LANGUAGE PLPgSQL;
CREATE AGGREGATE my_func_dp(double precision) (SFUNC = my_func_dp_a, STYPE = double precision);

-- typecast price and arithmetics to numeric
CREATE OR REPLACE FUNCTION my_func_numeric_a(avg numeric, IN price numeric)
RETURNS numeric AS $$
DECLARE
    alpha numeric;
BEGIN
    alpha := 2.0/51;
    RETURN
    CASE
        WHEN avg IS NULL THEN price
            ELSE (alpha * price + (1-alpha) * avg)
        END;
    END;
$$ LANGUAGE PLPgSQL;
CREATE AGGREGATE my_func_numeric(numeric) (SFUNC = my_func_numeric_a, STYPE = numeric);

现在，模拟数据并应用三个函数的代码:

WITH sample AS
(
    SELECT "day", (random())*10 AS price, generate_series(1,10)::text AS item
    FROM (SELECT generate_series('2000-01-01'::timestamp, '2000-01-31'::timestamp, '1 day'::interval)::date AS "day") AS calendar
)
SELECT  "day", item, price,
        -- typecast price and arithmetics to numeric(8,2)
        my_func_numeric_8_2(price::numeric(8,2))  OVER (PARTITION BY item ORDER BY "day") AS numeric_8_2,

        -- typecast price and arithmetics to numeric and round(arithmetics, 2)
        my_func_numeric_round(price::numeric)  OVER (PARTITION BY item ORDER BY "day") AS numeric_round,

        -- typecast price and arithmetics to numeric and round the final result
        round(my_func_numeric(price::numeric)  OVER (PARTITION BY item ORDER BY "day"), 2) AS round_numeric,

        -- no typecast (double precision type)
        my_func_dp(price)  OVER (PARTITION BY item ORDER BY "day") AS no_typecast,

        -- typecast price and arithmetics to numeric
        my_func_numeric(price::numeric)  OVER (PARTITION BY item ORDER BY "day") AS numeric
FROM sample
ORDER BY item, "day"

由于 random() 的使用，每次查询执行都会产生不同的结果。向下滚动结果，您会看到许多具有不同值的行，即使 price 用于计算所有四个值也是如此。此外，列按降低精度损失(或提高精度)排序: my_func_dp(price) 是四者中最精确的，而 my_func_numeric_8_2(price::numeric(8,2)) 不太精确，但最“精确”。

如果您从命令行运行前面的查询，您会注意到 my_func_numeric(price::numeric) 返回长度增加的数字，因为 numeric 尽可能精确，因此他的固定长度可以变化。如果您从 pgAdmin 执行它，您将得到一个完整长度数字的四舍五入数字。