探索 PostgreSQL® 14 中的新功能 - SEARCH 和 CYCLE

使用递归查询？在较早的博客文章的此更新中查看 PostgreSQL 14 中可用的新 SEARCH 和 CYCLE 功能。

假期、旅行、快乐时光总是在我们的脑海中，几个月前我们看到了如何Solving the knapsack problem in PostgreSQL。然而，博客文章并不总是像葡萄酒一样陈年。发布日期几周后，PostgreSQL 14 发布了，它包含了几个非常有趣的新特性：CYCLE和SEARCH. 它们大大简化了我们编写递归查询的方式。这篇文章给出了几个例子，基于一个最喜欢的话题：旅行计划！

创建数据库

本文中的示例适用于任何 PostgreSQL 14 或更高版本的数据库。我将使用Aiven 管理的 PostgreSQL 服务和 Aiven CLI（查阅安装和登录的专用文档)。这是创建数据库的 CLI 命令：

avn service create holidays-pg \
    --service-type pg \
    --plan hobbyist \
    --cloud google-europe-west3 \
    -c pg_version=14

上面创建了一个在google-europe-west3区域上的名为holidays-pg的PostgreSQL 14 (-c pg_version=14 ) 服务，具有最小的hobbyist计划。这对我们的测试来说已经足够了。要检查我们提供的工具的版本，请使用专用文档中记录的avn service versions命令。

需要一点时间来启动PostgreSQL 14 数据库并运行。可以打开我们最喜欢的旅游指南并开始浏览目的地。同时，我们可以通过以下方式关注服务创建任务的进度：

avn service wait holidays-pg

上述命令将定期请求服务器的状态，直到启动。一旦它返回，我们就可以通过以下方式连接到我们的holidays-pgPostgreSQL 14 服务：

avn service cli holidays-pg

创建数据集

我们想穿越欧洲，在预算范围内参观一些主要城市。

为了存储想要参观的城市，我们创建了一个cities表，并插入挑选的城市。

create table cities(
    city_id int primary key,
    city_name varchar
    );

insert into cities values (0, 'Rome'),
                         (1, 'London'),
                         (2, 'Paris'),
                         (3, 'Helsinki'),
                         (4, 'Barcelona');

如何在城市之间旅行呢？很容易，我们前往旅行预订网站，并找到对应的城市。通常我们会返回这样的图表：

为了在 PostgreSQL 中表示上述信息，我们创建了一个trips表，出发地 ( city_a_id)、目的地 ( city_b_id) 和以欧元为单位的旅行费用 ( price_in_eur)

create table trips(
    trip_id int primary key,
    city_a_id int references cities(city_id),
    city_b_id int references cities(city_id),
    price_in_eur int
    );

insert into trips values
    (1, 0, 1, 200),
    (2, 0, 2, 250),
    (3, 0, 3, 150),
    (4, 1, 0, 120),
    (5, 1, 3, 350),
    (6, 1, 4, 450),
    (7, 2, 0, 170),
    (8, 2, 3, 320),
    (9, 3, 0, 50),
    (10, 3, 4, 120),
    (11, 4, 0, 30),
    (12, 4, 1, 500);

trips表包含所有可用路线以及相关成本。例如，id=2的旅行，从Rome(city_id:0)出发，到达Paris (city_id:2) 需要花费250欧。

计划行程

旅程需要从某个地方开始，条条大路通罗马，我们可以选择永恒之城作为起点。

为了查看我们可以去哪里，需要在cities表和trips表之间进行连接。

select
    src.city_name,
    dst.city_name,
    trips.price_in_eur
from cities src
    join trips on src.city_id = trips.city_a_id
    join cities dst on trips.city_b_id = dst.city_id
where src.city_name='Rome';

查询结果与上图显示的信息相同：可以直接到达London，Paris以及Helsinki

city_name | city_name | price_in_eur
-----------+-----------+--------------
Rome      | London    |          200
Rome      | Paris     |          250
Rome      | Helsinki  |          150
(3 rows)

为旅程添加更多的落脚点

好的，下一步在哪里？我们可以使用递归查询来遍历所有可能的组合。

假如有足够的钱，我们就可以环游世界。用数据库术语来解释，这意味着我们在递归查询中可能有无限循环。为了避免无线循环，设定一个800欧元的总预算。

为旅程编写递归查询，如下所示：

with recursive trip_journey(
    city_id,
    trip_id,
    total_price_in_eur,
    journey_stops
    )
as (
    select
        city_id as city_id,
        null::int as trip_id,
        0 price_in_eur,
        ARRAY[city_name] as journey_name
    from cities
    where city_id=0
    UNION ALL
    select
        trips.city_b_id,
        trips.trip_id,
        tj.total_price_in_eur + trips.price_in_eur,
        tj.journey_stops || city_b.city_name
    from trip_journey tj join trips on tj.city_id = trips.city_a_id
    join cities city_a on trips.city_a_id = city_a.city_id
    join cities city_b on trips.city_b_id = city_b.city_id
    where tj.total_price_in_eur + trips.price_in_eur < 800
    )
select * from trip_journey;

让我们分解一下。第一部分陈述了起点：我们要从Rome( city_id=0) 开始。如果我们不去旅行，那trip_id就是null，总成本是0。

select
    city_id as city_id,
    null::int as trip_id,
    0 price_in_eur,
    ARRAY[city_name] as journey_name
from cities
where city_id=0

然后我们开始添加旅行，使用递归部分，将先前定义trip_journey的与trips表连接起来，以发现所有可能的目的地以及对应的成本。

UNION ALL
select
    trips.city_b_id,
    trips.trip_id,
    tj.total_price_in_eur + trips.price_in_eur,
    tj.journey_stops || city_b.city_name
from trip_journey tj join trips on tj.city_id = trips.city_a_id
join cities city_a on trips.city_a_id = city_a.city_id
join cities city_b on trips.city_b_id = city_b.city_id
where tj.total_price_in_eur + trips.price_in_eur < 800

将city_b.city_name包含在journey_stops中。 然后，将之前的总费用和当前的行程价格相加来计算总行程成本 ( tj.total_price_in_eur + trips.price_in_eur)。最后，通过where子句限制总预算在800欧以内

查询结果 89 行，从不旅行（留在Rome）开始，到长途旅行{Rome,Helsinki,Rome,Helsinki,Rome,Helsinki,Barcelona,Rome}跨越多个城市。

city_id | trip_id | total_price_in_eur |                          journey_stops
---------+---------+--------------------+-----------------------------------------------------------------
      0 |         |                  0 | {Rome}
      1 |       1 |                200 | {Rome,London}
      2 |       2 |                250 | {Rome,Paris}
      3 |       3 |                150 | {Rome,Helsinki}
      0 |       4 |                320 | {Rome,London,Rome}
      3 |       5 |                550 | {Rome,London,Helsinki}
....
      4 |      10 |                770 | {Rome,Helsinki,Rome,Helsinki,Barcelona,Rome,Helsinki,Barcelona}
      0 |      11 |                700 | {Rome,Helsinki,Rome,Helsinki,Rome,Helsinki,Barcelona,Rome}
(89 rows)

使用 SEARCH 选项定义搜索路径

上面的89行总结了所有可能的行程。但是该数据集是如何进行排序的呢？PostgreSQL14中，SEARCH选项提供了一种新的方法来定义递归查询方式：

• 如果想根据参观城市的数量来安排行程，可以使用BREADTH选项。

  首先看到涉及 0 站的行程，然后是涉及 1 站、2 站等的行程。

• 如果想根据旅行路径来安排行程，可以使用DEPTH选项。

  可以看到行程的每一步都在扩展，例如{Rome}-> {Rome->London}->{Rome->London->Helsinki}直到找到旅程的最大深度，然后它将搜索树的连续分支。

BREADTH示例：

上述递归查询只需将最后一条select * from trip_journey语句替换为以下内容：

SEARCH BREADTH FIRST BY city_id SET ordercol
select * from trip_journey order by ordercol limit 15;

为了节省计算量，不打算扫描整个结果集，将查询限制为仅返回前 15 行limit 15。因为我们正在使用BREADTH选项，所以结果集仍按站点数排序。

 city_id | trip_id | total_price_in_eur |         journey_stops          | ordercol
---------+---------+--------------------+--------------------------------+----------
       0 |         |                  0 | {Rome}                         | (0,0)
       1 |       1 |                200 | {Rome,London}                  | (1,1)
       2 |       2 |                250 | {Rome,Paris}                   | (1,2)
       3 |       3 |                150 | {Rome,Helsinki}                | (1,3)
       0 |       4 |                320 | {Rome,London,Rome}             | (2,0)
       0 |       9 |                200 | {Rome,Helsinki,Rome}           | (2,0)
       0 |       7 |                420 | {Rome,Paris,Rome}              | (2,0)
       3 |       5 |                550 | {Rome,London,Helsinki}         | (2,3)
       3 |       8 |                570 | {Rome,Paris,Helsinki}          | (2,3)
       4 |       6 |                650 | {Rome,London,Barcelona}        | (2,4)
       4 |      10 |                270 | {Rome,Helsinki,Barcelona}      | (2,4)
       0 |       9 |                600 | {Rome,London,Helsinki,Rome}    | (3,0)
       0 |      11 |                300 | {Rome,Helsinki,Barcelona,Rome} | (3,0)
       0 |       9 |                620 | {Rome,Paris,Helsinki,Rome}     | (3,0)
       0 |      11 |                680 | {Rome,London,Barcelona,Rome}   | (3,0)
(15 rows)

ordercol列包含一个元组(A,B)，其中第一项表示级别，第二项表示最新city_id。例如(2,0)，表示旅程包括两次行程，并以Rome( city_id=0) 结尾，相同的信息可以在包含的旅程停靠点列中找到{Rome,Paris,Rome}。

用DEPTH替换BREADTH子句，会得到按旅行路径排序的前15条组合，逐步搜索如何扩展行程。

 city_id | trip_id | total_price_in_eur |                    journey_stops                    |           ordercol
---------+---------+--------------------+-----------------------------------------------------+-------------------------------
       0 |         |                  0 | {Rome}                                              | {(0)}
       1 |       1 |                200 | {Rome,London}                                       | {(0),(1)}
       0 |       4 |                320 | {Rome,London,Rome}                                  | {(0),(1),(0)}
       1 |       1 |                520 | {Rome,London,Rome,London}                           | {(0),(1),(0),(1)}
       0 |       4 |                640 | {Rome,London,Rome,London,Rome}                      | {(0),(1),(0),(1),(0)}
       3 |       3 |                790 | {Rome,London,Rome,London,Rome,Helsinki}             | {(0),(1),(0),(1),(0),(3)}
       2 |       2 |                570 | {Rome,London,Rome,Paris}                            | {(0),(1),(0),(2)}
       0 |       7 |                740 | {Rome,London,Rome,Paris,Rome}                       | {(0),(1),(0),(2),(0)}
       3 |       3 |                470 | {Rome,London,Rome,Helsinki}                         | {(0),(1),(0),(3)}
       0 |       9 |                520 | {Rome,London,Rome,Helsinki,Rome}                    | {(0),(1),(0),(3),(0)}
       1 |       1 |                720 | {Rome,London,Rome,Helsinki,Rome,London}             | {(0),(1),(0),(3),(0),(1)}
       2 |       2 |                770 | {Rome,London,Rome,Helsinki,Rome,Paris}              | {(0),(1),(0),(3),(0),(2)}
       3 |       3 |                670 | {Rome,London,Rome,Helsinki,Rome,Helsinki}           | {(0),(1),(0),(3),(0),(3)}
       0 |       9 |                720 | {Rome,London,Rome,Helsinki,Rome,Helsinki,Rome}      | {(0),(1),(0),(3),(0),(3),(0)}
       4 |      10 |                790 | {Rome,London,Rome,Helsinki,Rome,Helsinki,Barcelona} | {(0),(1),(0),(3),(0),(3),(4)}
(15 rows)

ordercol包含city_id的串联列表，例如，journey_stops列{(0),(1),(0),(2)}表示我们将按照Rome->London->Rome->Paris的方式旅行。返回的行顺序遵循ordercol

使用 CYCLE 选项避免循环

Rome->London->Rome->Paris是一段美好的旅程么？啊，可能你并不喜欢多次经过同一个城市。循环是一种非常低效的旅行方式，我们应该尽可能避免。幸运的是，PostgreSQL 14CYCLE选项提供了一种跳过它们的方法。

在原始递归查询中，用下面的语句替换select * from trip_journey：

CYCLE city_id SET is_cycle USING journey_ids
select * from trip_journey where is_cycle=false;

以上为递归查询增加了几列：

• journey_ids在ARRAY中包含city_id的序列
• is_cycle通过检查当前city_id是否已经在journey_ids列中来标记循环

is_cycle=false条件过滤后的查询结果提供了在总预算内的所有非循环旅行的组合。

city_id | trip_id | total_price_in_eur |          journey_stops           | is_cycle |    journey_ids
---------+---------+--------------------+----------------------------------+----------+-------------------
      0 |         |                  0 | {Rome}                           | f        | {(0)}
      1 |       1 |                200 | {Rome,London}                    | f        | {(0),(1)}
      2 |       2 |                250 | {Rome,Paris}                     | f        | {(0),(2)}
      3 |       3 |                150 | {Rome,Helsinki}                  | f        | {(0),(3)}
      3 |       5 |                550 | {Rome,London,Helsinki}           | f        | {(0),(1),(3)}
      4 |       6 |                650 | {Rome,London,Barcelona}          | f        | {(0),(1),(4)}
      3 |       8 |                570 | {Rome,Paris,Helsinki}            | f        | {(0),(2),(3)}
      4 |      10 |                270 | {Rome,Helsinki,Barcelona}        | f        | {(0),(3),(4)}
      4 |      10 |                690 | {Rome,Paris,Helsinki,Barcelona}  | f        | {(0),(2),(3),(4)}
      4 |      10 |                670 | {Rome,London,Helsinki,Barcelona} | f        | {(0),(1),(3),(4)}
      1 |      12 |                770 | {Rome,Helsinki,Barcelona,London} | f        | {(0),(3),(4),(1)}
(11 rows)

避开环路后，我们还可以比较行程：例如，行程{Rome,Helsinki,Barcelona,London}和{Rome,London,Helsinki,Barcelona}包含相同的城市，但第一个便宜 100 欧元。

回程

旅行结束后回家是一个开心的时刻，但是，如果检查上面的旅行，因为避免了循环，不可能再次回到Rome。

为了实现这一点，在原始查询中，我们需要考虑与trips表的额外连接，每次旅程还增加了返回Rome的费用，可以查看下面的完整查询：

with recursive trip_journey(
    city_id,
    trip_id,
    total_price_in_eur,
    journey_stops,
    journey_prices,
    return_price
    )
as (
    select
        city_id as city_id,
        null::int,
        0 price_in_eur,
        ARRAY[city_name] as journey_name,
        ARRAY[0::int] as journey_price,
        0 return_price
    from cities
    where city_id=0
    UNION ALL
    select
        trips.city_b_id,
        trips.trip_id,
        tj.total_price_in_eur + trips.price_in_eur,
        tj.journey_stops || city_b.city_name,
        tj.journey_prices || trips.price_in_eur,
        return_trips.price_in_eur
    from trip_journey tj join trips on tj.city_id = trips.city_a_id
    join cities city_a on trips.city_a_id = city_a.city_id
    join cities city_b on trips.city_b_id = city_b.city_id
    join trips return_trips on trips.city_b_id = return_trips.city_a_id and return_trips.city_b_id = 0
    where tj.total_price_in_eur + trips.price_in_eur + return_trips.price_in_eur < 800
    ) CYCLE city_id SET is_cycle USING journey_ids
select * from trip_journey where is_cycle=false;

join trips return_trips on trips.city_b_id = return_trips.city_a_id and return_trips.city_b_id = 0部分确保我们还包括返回Rome( city_id=0) 的旅程，并且tj.total_price_in_eur + trips.price_in_eur + return_trips.price_in_eur < 800语句在预算检查中包含回程的费用。

结果显示所有 10 次可能的旅程，其中包括在预算中的返回Rome的行程。

city_id | trip_id | total_price_in_eur |          journey_stops           | journey_prices  | return_price | is_cycle |    journey_ids
---------+---------+--------------------+----------------------------------+-----------------+--------------+----------+-------------------
      0 |         |                  0 | {Rome}                           | {0}             |            0 | f        | {(0)}
      1 |       1 |                200 | {Rome,London}                    | {0,200}         |          120 | f        | {(0),(1)}
      2 |       2 |                250 | {Rome,Paris}                     | {0,250}         |          170 | f        | {(0),(2)}
      3 |       3 |                150 | {Rome,Helsinki}                  | {0,150}         |           50 | f        | {(0),(3)}
      3 |       5 |                550 | {Rome,London,Helsinki}           | {0,200,350}     |           50 | f        | {(0),(1),(3)}
      4 |       6 |                650 | {Rome,London,Barcelona}          | {0,200,450}     |           30 | f        | {(0),(1),(4)}
      3 |       8 |                570 | {Rome,Paris,Helsinki}            | {0,250,320}     |           50 | f        | {(0),(2),(3)}
      4 |      10 |                270 | {Rome,Helsinki,Barcelona}        | {0,150,120}     |           30 | f        | {(0),(3),(4)}
      4 |      10 |                690 | {Rome,Paris,Helsinki,Barcelona}  | {0,250,320,120} |           30 | f        | {(0),(2),(3),(4)}
      4 |      10 |                670 | {Rome,London,Helsinki,Barcelona} | {0,200,350,120} |           30 | f        | {(0),(1),(3),(4)}
(10 rows)

总结

新的SEARCH和CYCLE选项提供了一种新的、更优雅的定义递归查询行为的方式。

• PostgreSQL 中的 WITH 查询（公用表达式），您可以在其中找到SEARCH和CYCLE文档
• Solving the knapsack problem in PostgreSQL，您可以在其中检查如何定义搜索模式并避免以前 PostgreSQL 版本中的循环
• Aiven for PostgreSQL检查 Aiven 为 PostgreSQL 提供的托管服务。

原文地址：https://aiven.io/blog/explore-the-new-search-and-cycle-features-in-postgresql-14

原文作者：Francesco Tisiot