如何调试PostgreSQL中的通用表表达式?

I had a problem in a query, where one of CTEs returned no rows. But this was hard to notice, and debugging lastet quite a while.

我在查询中遇到了一个问题，其中一个cte没有返回任何行。但是这很难被注意到，调试lastet有一段时间。

Is it possible to output all CTEs in Postgres without commenting out the main query?

是否有可能输出所有CTEs在Postgres，而不需要注释掉主查询?

create or replace function generate_grid(
    poly geometry, step double precision)
    returns setof geometry as
$$ */

with 
    initial as (select st_xmin(poly) x0, st_ymin(poly) y0),
    ...(another 3 CTE skipped here)...
    grid as (select point from points where st_intersects(point, poly)),
    centroid as (select st_centroid(poly) point from grid where (select count(*)=0 from grid))
select * from grid
union all
select * from centroid;
$$ language sql;

In the example, CTE centroid was incrementally added to the function which worked well before. It should return rows in case grid is empty. The bug (which I have fixed) was that it didn't, because it selected from the empty CTE grid. Now when I described the problem it's obvious why it failed, but when you write it and debug, all sorts of things may happen, like mixed geometries SRID, wrong SRID, etc.

在本例中，CTE质心被增量地添加到函数中，该函数以前工作得很好。如果网格为空，它应该返回行。错误(我已经修复)是它没有，因为它是从空的CTE网格中选择的。当我描述这个问题时，很明显它失败了，但是当你写它和调试时，可能会发生各种各样的事情，比如混合的几何图形，错误的SRID等等。

2 个解决方案

#1

EXPLAIN ANALYZE looks to report the CTEs separately.

解释分析报告单独报告CTEs。

When I run that (Postgresql 9.4) it is showing the CTEs separately, and in the result section it does show that the actual number of rows returned from "CTE Scan on x" was 0.

当我运行这个(Postgresql 9.4)时，它分别显示CTE，在result部分中，它确实显示了从“x上的CTE扫描”返回的实际行数为0。

explain analyze
with x as (select 1 where false),
     y as (select 2 where true)
select * from x, y;

Returns:

Nested Loop  (cost=0.02..0.07 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)
  Output: x."?column?", y."?column?"
  CTE x
    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)
          Output: 1
          One-Time Filter: false
  CTE y
    ->  Result  (cost=0.00..0.01 rows=1 width=0) (never executed)
          Output: 2
  ->  CTE Scan on x  (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1)
        Output: x."?column?"
  ->  CTE Scan on y  (cost=0.00..0.02 rows=1 width=4) (never executed)
        Output: y."?column?"
Planning time: 0.034 ms
Execution time: 0.018 ms

I don't know the explain would always display data like this, I suspect it depends on how Postgresql decides to optimize the query, but it should be a good start point.

我不知道解释总是会显示这样的数据，我怀疑这取决于Postgresql如何决定优化查询，但它应该是一个很好的起点。

Explain documentation at http://www.postgresql.org/docs/current/static/sql-explain.html

在http://www.postgresql.org/docs/current/static/sql-explain.html解释文档

#2

The problem with CROSS JOIN is that it would produce no output when one of derived tables is empty:

交叉连接的问题是，当派生表为空时，它不会产生输出:

with x as (select 1 where false),
     y as (select 2 where true)
select * from x, y;

You need something like OUTER CROSS JOIN.

你需要一些类似外交叉连接的东西。

In SQL Server there is great OUTER APPLY:

在SQL Server中有很大的外部应用:

with x(c) as (select 1 where 1=0),
     y(d) as (select 2 where 1=1)
select * 
FROM (values ('Base')) AS s(val)   -- always one row
OUTER APPLY x
OUTER APPLY y;

LiveDemo

LiveDemo

You could simulate this behaviour using LEFT JOIN LATERAL, but it looks a bit "ugly":

你可以用左连接横向来模拟这种行为，但是它看起来有点“丑陋”:

;WITH x(c) AS (SELECT 1 WHERE false),
      y(d) AS (SELECT 2 WHERE true)
SELECT * 
FROM (VALUES ('Base row')) AS s(val)
LEFT JOIN LATERAL (SELECT * FROM x) AS x(c) ON true
LEFT JOIN LATERAL (SELECT * FROM y) AS y(d) ON true;

SqlFiddleDemo

SqlFiddleDemo

Output:

输出:

╔═══════════╦═════════╦═══╗
║   val     ║   c     ║ d ║
╠═══════════╬═════════╬═══╣
║ Base row  ║ (null)  ║ 2 ║
╚═══════════╩═════════╩═══╝

or simple LEFT JOIN in this case:

或者简单的左连接:

;WITH x(c) AS (SELECT 1 WHERE false),
     y(d) AS (SELECT 2 WHERE true)
SELECT * 
FROM (VALUES ('Base row')) AS s(val)
LEFT JOIN x ON true
LEFT JOIN y ON true;

#1