主页 / user-97832

cis's questions

Martin Hope
cis
Asked: 2022-03-14 22:18:40 +0800 CST
  • 5

在 Postgres 13 中，我有一个经常更新的表。但是，更新查询相当复杂，并且多次使用相同的值。因此，使用 CTE 似乎是一件合乎逻辑的事情。

一个简化的示例如下所示：

WITH my_cte AS (
    SELECT
          my_id,
          CASE WHEN my_value1 > 100 THEN 50 ELSE 10 END AS my_addition     
    FROM my_table      
    WHERE my_id = $1
)
UPDATE my_table
        SET my_value1 = my_table.my_value1 + my_cte.my_addition,
            my_value2 = my_table.my_value2 + my_cte.my_addition
FROM my_cte
WHERE my_table.my_id = my_cte.my_id

现在我想知道：如果在SELECTCTE 和之间UPDATE，表被另一个查询更新，my_value1因此发生变化，计算会my_addition在发生时变得过时和错误UPDATE。会不会出现这样的情况？还是 Postgres 自动设置隐式锁？

如果 Postgres 在这里没有魔法，我需要自己处理它：在 CTE 中做就足够了FOR UPDATE吗SELECT？

抱歉，如果我没有在这里说清楚：这不是我想“看到”那些并发修改，我想阻止它们，即一旦计算SELECT完成，在完成之前没有其他查询可能修改该行UPDATE。

在现实生活中，我在这里嘲笑的CASE WHEN my_value1 > 100 THEN 50 ELSE 10 END是大约 20 行长，我在UPDATE. 由于我是“不要重复自己”的忠实粉丝，我认为 CTE 是要走的路。或者有没有更好的方法来避免在UPDATE没有 CTE 的情况下复制和粘贴？

postgresql update
Martin Hope
cis
Asked: 2022-03-09 06:02:25 +0800 CST
  • 0

我有一个像这样的查询

UPDATE my_table 
  SET my_value = 
    CASE WHEN random() > 0.5 THEN my_value * 2 ELSE my_value END
RETURNING *;

现在，在RETURNING语句中，我想要一个布尔值，指示my_value当前查询是否已更改。

假设，我不能将之前的值my_value作为参数传递给查询。

那么，有没有办法获得类似在 之后具有不同值的列的列表UPDATE？还是在 in 之前的状态下获取UPDATE值RETURNING？

在我的示例中，我当然可以将结果放入random()CTE 中，例如

WITH random_cte AS (
    SELECT random() AS my_random
)
UPDATE my_table 
  SET my_value = 
    CASE WHEN my_random > 0.5 THEN my_value * 2 ELSE my_value END
FROM random_cte
RETURNING *, my_random > 0.5 AS value_changed;

但这会使查询有些膨胀。所以我想知道我是否可以以更优雅的方式做到这一点？

postgresql update
Martin Hope
cis
Asked: 2021-03-20 01:18:32 +0800 CST
  • 0

使用 PostgreSQL 11。考虑一个像这样的表

CREATE TABLE "logs" 
    (
      "id" INTEGER NOT NULL,
      "userId" INTEGER NOT NULL, 
      "timestamp" TIMESTAMP NOT NULL,
      CONSTRAINT "PK_8d33b9f1a33b412e4865d1e5465" PRIMARY KEY ("id")
     )

现在，要求是每个userId. 如果有更多数据进入，则必须删除最旧的日志。如果在短时间内存储了 101 行，那么这并不是世界末日。如果多余的行在几秒钟的延迟后被删除，那很好。

我无法创建数据库TRIGGER。所以，我需要编写一个在应用层的日志创建事件上触发的查询。

纯 SQL 优于 plpgsql。

这是我想出的解决方案：

WITH "userLogs" AS (SELECT id, timestamp FROM "logs"
                    WHERE "userId" = $1
                ),
"countLogs" AS (SELECT count(id) FROM "userLogs")
        
DELETE FROM "logs" WHERE id = ANY
                (
                    SELECT id FROM "userLogs" 
                    ORDER BY "timestamp" ASC 
                    LIMIT GREATEST( (SELECT count FROM "countLogs") - 100, 0)
                );

想法是：始终运行 a并根据子查询DELETE实际是否必须删除某些内容来做出决定。LIMIT如果有超过 100 条日志，子查询将返回最旧的 id 以丢弃。否则，LIMIT将为 0，子查询不会返回任何内容，也不会删除任何内容。

我现在的问题是：

  1. DELETE对每个运行查询是否敏感INSERT- 即使它没有删除任何内容？
  2. 这里有任何性能影响吗？（或者其他我可能不知道的陷阱？）
  3. 我不太确定我是否需要一个LOCK. 在我的测试中，当并行运行 s 时，我无法产生任何意外行为INSERT，但是是否存在我需要 a 的边缘情况LOCK？

编辑：很难预测INSERT将针对该表运行多少次。如果一切顺利（业务方面），总共可能每天几千次 - 每个用户每天几十次。

编辑2：timestamp每个用户的值不一定是唯一的：可以有多个具有相同timestamp和相同的日志条目userId。预计该表将获得更多包含实际发生情况的列。

postgresql delete
Martin Hope
cis
Asked: 2020-05-19 00:53:33 +0800 CST
  • 0

使用 PostgreSQL 12，我要求每晚需要“刷新”一些数据。这意味着：数据库必须根据当前日期进行一些计算并将结果写入表中。这通常有效。然而，它的工作方式似乎并不完美。

基本上，有一个这样的 PLV8 查询：

做$$

var myData = plv8.execute('SELECT id FROM my_table');
for(let i = 0; < myData.length; i++) {
    plv8.execute('SELECT my_function_which_refreshes_lots_of_data($1)',[myData[i].id);
}
$$ LANGUAGE plv8;

my_function_which_refreshes_lots_of_data获取一项的 id 并完成所有工作。必须这样做，因为有时单行也需要触发刷新。

问题是不知何故这已经失控了。目前，查询运行了一个多小时，占用了 99% 的内存，填满了交换空间，并且大部分时间处于状态“D”-“磁盘睡眠（不间断）”。查询的持续时间不是问题，因为它通常在夜间运行，由 cronjob 触发。但是，内存消耗和进程状态（基本上就像僵尸一样）是或可能是一个问题。

所以，我的想法是：据我所知，PostgreSQL 将所有对表的更改保存在内存中，同时查询运行并仅在最后写入它们。是否可以手动触发“将您到目前为止所拥有的内容写入磁盘并释放内存”？

之后我会在我的代码中这样做plv8.execute('SELECT my_function_which_refreshes_lots_of_data($1)',[myData[i].id);。当然，如果进程崩溃或发生其他情况，这将导致状态不一致：部分项目将由 处理my_function_which_refreshes_lots_of_data，而其余项目将保持原样。但是，这总比不写任何项目要好。并且绝对比运行这么长时间并消耗所有内存的查询要好。

我知道我可以以某种方式尝试在客户端拆分查询。但是，由于某些原因，这对我来说不太方便。

postgresql memory
Martin Hope
cis
Asked: 2020-02-14 03:06:30 +0800 CST
  • 2

我最近发现了一种 PostgreSQL 行为，在我看来这是一种奇怪且有问题的行为。

考虑这个简单的查询

SELECT 
   'something confidential'
WHERE FALSE;

无意义的子句WHERE FALSE在这里类似于一个非常严格的权限检查。这按预期工作：没有返回任何内容。

现在，假设您添加了一个附加列，例如一个count函数。这给出了这个查询：

SELECT 
   'something confidential'
   ,count(CURRENT_DATE)
WHERE FALSE;

（的参数count可以是任何东西。CURRENT_DATE只是一个随机的例子。）

现在，我们得到

机密的东西| 0

我想，这是故意的，SELECT count(CURRENT_DATE);如果条件为真则返回 1，如果条件为假则返回 0。

但是，我认为在您不知道的情况下这是有问题的。所以，我的问题是

  1. 怎么来的？这种行为的背景是什么？
  2. 如何使这样的查询返回零行而不是值为 0 的一行count？
  3. 有没有办法直接在查询中通过添加聚合函数来防止意外返回您不想返回的行？（即除了外部测试等）

我使用的是 PostgreSQL 12.1，但旧版本的行为是相同的。

postgresql security
Martin Hope
cis
Asked: 2020-01-21 03:19:11 +0800 CST
  • 5

跟进我关于 Postgres 12 中的一些查询比我认为的 11 慢的问题，我能够缩小问题的范围。似乎一个基于函数值的递归 CTE 是有问题的地方。

我能够分离出一个相当小的 SQL 查询，它在 Postgres 12.1 上比在 Postgres 11.6 上运行的时间要长得多，比如 Postgres 12.1 中的 ca 150ms 和 Postgres 11.6 中的 ca 4ms。我能够在各种系统上重现这种现象：在 VirtualBox 中的多个虚拟机上；通过 Docker 在两台不同的物理机器上。（有关 docker 命令，请参阅附录）。但是，很奇怪，我无法在https://www.db-fiddle.com/上重现它（在那里没有区别，两者都很快）。

现在进行查询。首先，我们创建这个简单的函数

CREATE OR REPLACE FUNCTION public.my_test_function()
 RETURNS SETOF record
 LANGUAGE sql
 IMMUTABLE SECURITY DEFINER
AS $function$ 

SELECT 
        1::integer AS id,
        '2019-11-20'::date AS "startDate",
        '2020-01-01'::date AS "endDate"

$function$;

然后进行实际查询

WITH  "somePeriods" AS  (
      SELECT * FROM my_test_function() AS 
      f(id integer, "startDate" date, "endDate" date)
),

"maxRecursiveEndDate" AS (

SELECT "startDate", "endDate", id, 
( 
  WITH RECURSIVE prep("startDateParam", "endDateParam") AS (

  SELECT "startDate","endDate" FROM "somePeriods" WHERE id = od.id
  UNION
  SELECT "startDate","endDate" FROM "somePeriods", prep
  WHERE
    "startDate" <= ("endDateParam" + '1 day'::interval ) AND ("endDateParam" + '1 day'::interval ) <= "endDate"
  )
  SELECT max("endDateParam") FROM prep
) AS "endDateNew"

FROM "somePeriods" AS od

)

SELECT * FROM "maxRecursiveEndDate";

我想这实际上在这里并不重要。重要的一点可能是涉及多个 CTE，包括RECURSIVE一个。

我尝试了什么：

  • 我没有尝试my_test_function过，即将值直接放入第一个 CTE。这样一来，就完全没有问题了。在 12 和 11 上运行同样快。
  • 在 Postgres 12 上，我玩过MATERIALIZED，但看不到任何效果。查询仍然像以前一样慢。

我不知道这实际上是否可能是 Postgres 12 错误（或性能回归），或者我是否在这里遗漏了一些东西。

附录：我用于复现的 Docker 命令

一、拉取两个版本的镜像

docker pull postgres:12.1
docker pull postgres:11.6

现在，运行 Postgres 12

docker run -d --name my_postgres_12_container postgres:12.1

现在，执行查询

docker exec my_postgres_12_container psql -U postgres -c "

CREATE OR REPLACE FUNCTION public.my_test_function()
 RETURNS SETOF record
 LANGUAGE sql
 IMMUTABLE SECURITY DEFINER
AS \$function\$ 

SELECT 
        1::integer AS id,
        '2019-11-20'::date AS \"startDate\",
        '2020-01-01'::date AS \"endDate\"

\$function\$;

EXPLAIN ANALYZE WITH  \"somePeriods\" AS  (
      SELECT * FROM my_test_function() AS 
      f(id integer, \"startDate\" date, \"endDate\" date)
),

\"maxRecursiveEndDate\" AS (

SELECT \"startDate\", \"endDate\", id, 
( 
  WITH RECURSIVE prep(\"startDateParam\", \"endDateParam\") AS (

  SELECT \"startDate\",\"endDate\" FROM \"somePeriods\" WHERE id = od.id
  UNION
  SELECT \"startDate\",\"endDate\" FROM \"somePeriods\", prep
  WHERE
    \"startDate\" <= (\"endDateParam\" + '1 day'::interval ) AND (\"endDateParam\" + '1 day'::interval ) <= \"endDate\"
  )
  SELECT max(\"endDateParam\") FROM prep
) AS \"endDateNew\"

FROM \"somePeriods\" AS od

)

SELECT * FROM \"maxRecursiveEndDate\";
"

停止 Postgres 12 容器

docker stop my_postgres_12_container

启动 Postgres 11 进行比较

docker run -d --name my_postgres_11_container postgres:11.6

在 Postgres 11 中执行查询

docker exec my_postgres_11_container psql -U postgres -c "

CREATE OR REPLACE FUNCTION public.my_test_function()
 RETURNS SETOF record
 LANGUAGE sql
 IMMUTABLE SECURITY DEFINER
AS \$function\$ 

SELECT 
        1::integer AS id,
        '2019-11-20'::date AS \"startDate\",
        '2020-01-01'::date AS \"endDate\"

\$function\$;

EXPLAIN ANALYZE WITH  \"somePeriods\" AS  (
      SELECT * FROM my_test_function() AS 
      f(id integer, \"startDate\" date, \"endDate\" date)
),

\"maxRecursiveEndDate\" AS (

SELECT \"startDate\", \"endDate\", id, 
( 
  WITH RECURSIVE prep(\"startDateParam\", \"endDateParam\") AS (

  SELECT \"startDate\",\"endDate\" FROM \"somePeriods\" WHERE id = od.id
  UNION
  SELECT \"startDate\",\"endDate\" FROM \"somePeriods\", prep
  WHERE
    \"startDate\" <= (\"endDateParam\" + '1 day'::interval ) AND (\"endDateParam\" + '1 day'::interval ) <= \"endDate\"
  )
  SELECT max(\"endDateParam\") FROM prep
) AS \"endDateNew\"

FROM \"somePeriods\" AS od

)

SELECT * FROM \"maxRecursiveEndDate\";
"
postgresql postgresql-performance