问题[postgresql]

我已经有大约一周时间无法找出 Spring Boot 存储库中的问题了，并且我已经没有关于如何解决它的想法了。问题出在方法上findOrdersByFilters。如果我第一次调用它并传递日期作为参数之一，那么一切都会正常工作，并且对数据库的请求会按应有的方式处理，但是如果在启动程序后首先传递除日期之外的任何内容，那么该请求停止正常工作并抛出错误。演示该问题的视频：https://youtu.be/-pWz3vgBpIc

2025-01-06T14:28:02.035+03:00  WARN 4997 --- [nio-8082-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42846
2025-01-06T14:28:02.035+03:00 ERROR 4997 --- [nio-8082-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: cannot cast type bytea to date
  Позиция: 910
2025-01-06T14:28:02.042+03:00 ERROR 4997 --- [nio-8082-exec-5] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select o1_0.id,o1_0.client_id,o1_0.comment,o1_0.date_create,o1_0.date_end,o1_0.machine,o1_0.manager_username,o1_0.order_status,o1_0.payment_status from orders o1_0 join clients c1_0 on o1_0.client_id=c1_0.id left join employee e1_0 on o1_0.manager_username=e1_0.username where (? is null or o1_0.id=?) and (? is null or c1_0.company_name like ('%'||?||'%') escape '') and (? is null or (e1_0.first_name is not null and e1_0.last_name is not null and (e1_0.first_name like ('%'||?||'%') escape '' or e1_0.last_name like ('%'||?||'%') escape '' or (e1_0.first_name||' '||e1_0.last_name) like ('%'||?||'%') escape '' or (e1_0.last_name||' '||e1_0.first_name) like ('%'||?||'%') escape ''))) and (? is null or o1_0.payment_status like ('%'||?||'%') escape '') and (? is null or o1_0.order_status like ('%'||?||'%') escape '') and (? is null or o1_0.comment like ('%'||?||'%') escape '') and (cast(? as date) is null or o1_0.date_end=cast(? as date)) and (? is null or o1_0.manager_username=?) order by o1_0.date_create desc] [ERROR: cannot cast type bytea to date
  Позиция: 910] [n/a]; SQL [n/a]] with root cause

OrderRepo.java

package ru.limon4egtop.printingCRM.repos;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import ru.limon4egtop.printingCRM.dto.OrderFilterDto;
import ru.limon4egtop.printingCRM.models.Orders;

import java.util.List;

@Repository
public interface OrderRepo extends JpaRepository<Orders, Long> {

    @Query("""
    SELECT o FROM Orders o
    JOIN Clients c ON o.clientId = c.id
    LEFT JOIN Employee e ON o.managerUsername = e.username
    WHERE (:#{#filter.id} IS NULL OR o.id = :#{#filter.id})
      AND (:#{#filter.companyName} IS NULL OR c.companyName LIKE CONCAT('%', :#{#filter.companyName}, '%'))
      AND (
            :#{#filter.managerName} IS NULL OR 
            (
                e.firstName IS NOT NULL AND e.lastName IS NOT NULL AND
                (
                    e.firstName LIKE CONCAT('%', :#{#filter.managerName}, '%') OR
                    e.lastName LIKE CONCAT('%', :#{#filter.managerName}, '%') OR
                    CONCAT(e.firstName, ' ', e.lastName) LIKE CONCAT('%', :#{#filter.managerName}, '%') OR
                    CONCAT(e.lastName, ' ', e.firstName) LIKE CONCAT('%', :#{#filter.managerName}, '%')
                )
            )
        )
      AND (:#{#filter.paymentStatus} IS NULL OR o.paymentStatus LIKE CONCAT('%', :#{#filter.paymentStatus}, '%'))
      AND (:#{#filter.orderStatus} IS NULL OR o.orderStatus LIKE CONCAT('%', :#{#filter.orderStatus}, '%'))
      AND (:#{#filter.comment} IS NULL OR o.comment LIKE CONCAT('%', :#{#filter.comment}, '%'))
      AND (CAST(:#{#filter.dateEnd} AS DATE) IS NULL OR o.dateEnd = CAST(:#{#filter.dateEnd} AS DATE))
      AND (:#{#filter.currentUsername} IS NULL OR o.managerUsername = :#{#filter.currentUsername})
    ORDER BY o.dateCreate DESC
""")
    List<Orders> findOrdersByFilters(@Param("filter") OrderFilterDto filter);
} 

OrderServiceImp.java

package ru.limon4egtop.printingCRM.Services.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import ru.limon4egtop.printingCRM.Services.OrderService;
import ru.limon4egtop.printingCRM.dto.OrderFilterDto;
import ru.limon4egtop.printingCRM.models.Orders;
import ru.limon4egtop.printingCRM.repos.OrderRepo;

import java.util.List;

@Service
public class OrderServiceImp implements OrderService {
    private OrderRepo orderRepo;

    @Autowired
    public OrderServiceImp(OrderRepo orderRepo) {
        this.orderRepo = orderRepo;
    }

    @Override
    public List<Orders> getOrdersByFilters(final OrderFilterDto orderFilterDto) {
        System.out.println(orderFilterDto.toString());
        return this.orderRepo.findOrdersByFilters(orderFilterDto);
    }
}

OrderFilterDto.java

package ru.limon4egtop.printingCRM.dto;

import java.time.LocalDate;

public class OrderFilterDto {
    private Long id;
    private String companyName;
    private String managerName;
    private String paymentStatus;
    private String orderStatus;
    private String comment;
    private LocalDate dateEnd;
    private String currentUsername;

    public OrderFilterDto(Long id, String companyName, String managerName, String paymentStatus, String orderStatus, String comment, LocalDate dateEnd, String currentUsername) {
        this.id = id;
        this.companyName = companyName;
        this.managerName = managerName;
        this.paymentStatus = paymentStatus;
        this.orderStatus = orderStatus;
        this.comment = comment;
        this.dateEnd = dateEnd;
        this.currentUsername = currentUsername;
    }

    @Override
    public String toString() {
        return "OrderFilterDto {" +
                "id=" + id +
                ", companyName='" + companyName + '\'' +
                ", managerName='" + managerName + '\'' +
                ", paymentStatus='" + paymentStatus + '\'' +
                ", orderStatus='" + orderStatus + '\'' +
                ", comment='" + comment + '\'' +
                ", dateEnd=" + dateEnd +
                ", currentUsername=" + currentUsername +
                '}';
    }
}

有一个表“DataBase1”.Table1”，其中包含按时间间隔（Start_DT – End_DT）有关客户端（包括其服务通道）的各种信息。从此表中，您需要选择所有数据（下面的查询示例）和另一个具有递增键的计算列“Key”，每个客户端的键随着其服务渠道的每次变化而增加。任务 1 的基本请求示例：

SELECT
  CLIENT_ID, START_DT, END_DT, CHANNEL, «ваш текст» AS KEY
FROM
  "DataBase1".Table1
WHERE 1 = 1
  AND CLIENT_ID IN(123, 124)
ORDER BY
  CLIENT_ID, START_DT DESC

任务 1 的卸载示例：

那些。在 CLIENT_ID 分区内，您需要一个计数器来计算每个通道的变化。在这种情况下，它显示为该 SELECT 中的附加字段。

我试图使 DENSE_RANK() 成为窗口函数

SELECT CLIENT_ID, START_DT, END_DT, CHANNEL,
    DENSE_RANK() OVER (PARTITION BY client_id ORDER BY channel)
FROM table1
ORDER BY client_id DESC, start_dt DESC;

结论是：

任务：

BLOB（text 或 bytea）类型的字段包含 json 字符串的 zip 存档。

您需要解压存档并逐行处理记录（插入到经过一些转换的表中）

Oracle 有一个名为UTL_COMPRESS的内置实用程序，在它的帮助下我可以像这样解压数据：

UTL_COMPRESS.LZ_UNCOMPRESS(BLOB_CONTENT)

PostgreSQL 中是否有类似的实用程序？

有一个按日期分区的表。

在一个请求中，我们将日期作为参数提供给 CTE 块并计算工作日，然后在主请求中使用它：

  WITH prm 
   AS (
      SELECT 
       :dateFrom::DATE AS d_f, 
       :dateTo::DATE d_t, 
       (SELECT wrk_day d_f_wrk FROM calendar WHERE on_date=:dateFrom)
      )
   SELECT * FROM prm,tab t WHERE t.open_date BETWEEN prm.d_f_wrk AND prm.d_t

所以，如果我们在条件的主查询中使用它

BETWEEN prm.d_f_wrk AND prm.d_t

然后对所有分区进行搜索。就过滤器中的查询而言，它是这样写的：

t.open_date>=$2 AND t.open_date<='2024-10-30'::DATE

如果你使用

BETWEEN prm.d_f AND prm.d_t

然后仅过滤必要的分区。就过滤器中的查询而言，它是这样写的：

t.open_date>='2024-10-01'::DATE AND t.open_date<='2024-10-30'::DATE

如何将计算出的日期从 CTE 或以其他方式以相同日期参数的形式传递到主查询，以便分区过滤器仅过滤掉必要的日期？

另外，原则上，按分区过滤不起作用；如果日期是从另一个请求中获取的，则滑翔机会遍历所有分区。

> EXPLAIN analyze SELECT * 
FROM calendar wc, tab t 
WHERE t.open_date =wc.wrk_day  
 AND wc.on_date ='2024-10-30'
 
QUERY PLAN                                                                                                                                       |
-------------------------------------------------------------------------------------------------------------------------------------------------+
Hash JOIN  (cost=2.38..9602.67 ROWS=1099 width=85) (actual TIME=85.080..89.473 ROWS=41 loops=1)                                                  |
  Hash Cond: (t.open_date = wc.wrk_day)                                                                                                |
  ->  Append  (cost=0.00..8758.21 ROWS=316133 width=69) (actual TIME=0.590..54.404 ROWS=315957 loops=1)                                          |
        ->  Seq Scan ON t_2024_02_01 t_1  (cost=0.00..0.00 ROWS=1 width=596) (actual TIME=0.060..0.061 ROWS=0 loops=1)           |
        ->  Seq Scan ON t_2024_02_02 t_2  (cost=0.00..0.00 ROWS=1 width=596) (actual TIME=0.012..0.012 ROWS=0 loops=1)           |
        ->  Seq Scan ON t_2024_02_03 t_3  (cost=0.00..0.00 ROWS=1 width=596) (actual TIME=0.008..0.008 ROWS=0 loops=1)           |
        ->  Seq Scan ON t_2024_02_04 t_4  (cost=0.00..0.00 ROWS=1 width=596) (actual TIME=0.007..0.007 ROWS=0 loops=1)           |
...
...
->  Hash  (cost=2.37..2.37 ROWS=1 width=16) (actual TIME=0.021..0.021 ROWS=1 loops=1)                                                         |
        Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                            |
        ->  INDEX Scan USING calendar_pkey ON calendar wc  (cost=0.15..2.37 ROWS=1 width=16) (actual TIME=0.008..0.009 ROWS=1 loops=1)|
              INDEX Cond: (on_date = '2024-10-30'::DATE)

如果您在条件中明确指定日期，则会选择必要的分区

EXPLAIN analyze 
SELECT * 
FROM calendar wc, tab t 
WHERE t.open_date=wc.wrk_day  
 AND wc.on_date ='2024-10-30' AND t.open_date>='2024-10-29' AND t.open_date<'2024-11-01'
 
QUERY PLAN                                                                                                                                      |
------------------------------------------------------------------------------------------------------------------------------------------------+
Hash JOIN  (cost=2.38..33.53 ROWS=3 width=85) (actual TIME=0.037..0.320 ROWS=41 loops=1)                                                        |
  Hash Cond: (t.open_date = wc.wrk_day)                                                                                               |
  ->  Append  (cost=0.00..28.90 ROWS=845 width=69) (actual TIME=0.012..0.225 ROWS=845 loops=1)                                                  |
        ->  Seq Scan ON t_2024_10_29 t_1  (cost=0.00..1.61 ROWS=41 width=69) (actual TIME=0.011..0.016 ROWS=41 loops=1)         |
              FILTER: ((open_date >= '2024-10-29'::DATE) AND (open_date < '2024-11-01'::DATE))                                            |
        ->  Seq Scan ON t_2024_10_30 t_2  (cost=0.00..1.87 ROWS=58 width=69) (actual TIME=0.005..0.011 ROWS=58 loops=1)         |
              FILTER: ((open_date >= '2024-10-29'::DATE) AND (open_date < '2024-11-01'::DATE))                                            |
        ->  Seq Scan ON t_2024_10_31 t_3  (cost=0.00..21.19 ROWS=746 width=69) (actual TIME=0.006..0.135 ROWS=746 loops=1)      |
              FILTER: ((open_date >= '2024-10-29'::DATE) AND (open_date < '2024-11-01'::DATE))                                            |
  ->  Hash  (cost=2.37..2.37 ROWS=1 width=16) (actual TIME=0.015..0.016 ROWS=1 loops=1)                                                         |
        Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                            |
        ->  INDEX Scan USING calendar_pkey ON calendar wc  (cost=0.15..2.37 ROWS=1 width=16) (actual TIME=0.012..0.013 ROWS=1 loops=1)|
              INDEX Cond: (on_date = '2024-10-30'::DATE)                                                                                        |
Planning TIME: 0.408 ms                                                                                                                         |
Execution TIME: 0.356 ms                                                                                                                        |
 
15 ROW(s) fetched.

假设有两个事务：T1 和 T2。

BEGIN; -- Т1

UPDATE table
SET amount = 1
WHERE id = 1;

SELECT xmin, xmax FROM table
WHERE id = 1; 
-- получаем: xmin = xid транзакции Т1, xmax = 0

让我们开始第二笔交易：

BEGIN; -- T2

UPDATE table
SET amount = 2
WHERE id = 1; 
-- ожидание завершения транзакции Т1 

我们完成第一笔交易：

COMMIT;

我们继续第二个：

SELECT xmin, xmax FROM table
WHERE id = 1; 
-- получаем: xmin и xmax равный идентификатору (xid) транзакции Т2.

问题：为什么事务 T1 在更新后开始看到 xmax = 0，而事务 T2 在更新后开始看到 xmax = xid(T2)？我想强调的是，这些值相对于更新数据的事务是可见的。

PS 我之前不知何故没有注意到这一点，并且总是认为如果事务更改了一行，那么相对于其自身，它会在新行中看到 xmax = 0，因为这是一个新元组，而且还没有人更改它。