Erwin Brandstetter's questions

根据标准 SQL UNION/UNION ALL不保证任何特定的排序顺序没有外部ORDER BY子句 - 就像 SQL 中几乎没有任何地方可以保证排序顺序没有ORDER BY.

但是，Postgres 对 的普通情况使用“追加”步骤UNION ALL，因此第一条腿的结果（即使在它们的分区中未排序）总是在下一条腿之前，等等。Postgres 只是按照给定的顺序附加来自每条腿的结果。这与一个LIMIT子句特别相关：

SELECT 1 FROM tbl  -- or any complex query
UNION ALL
SELECT 2
LIMIT  1

显然，这不适用于UNION（没有ALL）。但除此之外，我从未见过 Postgres 乱序返回，即上述查询中的“2”SELECT ，而第一个也会返回行。即使第一站非常昂贵，也不是。

我过去曾基于此行为的查询。现在我遇到了一个说法，Postgres 可能会在这里返回乱序的行，但没有得到实际证据的证实。

当前的Postgres 手册对此事有这样的说法：

UNION有效地将结果附加到query2结果query1（尽管不能保证这是实际返回行的顺序）。此外，它从结果中消除重复行，与使用 , 的方式 DISTINCT相同UNION ALL。

这很不清楚。引用的顺序是否适用于SELECT子句列表，或每个子句中的行，还是仅适用于返回的集合？此外，UNION ALL仅在第二句中提及，因此尚不清楚最重要的第一句是否应该适用于UNION ALL...

任何人都可以举一个例子，其中行被乱序返回，破坏了UNION ALL子句的顺序？在任何版本的 Postgres 中。（即使最新版本最有趣。）

如果不是这样，是否有理由相信这可能会在未来的版本中发生变化？

ORDER BY不是这里的直接问题。问题是多个UNION ALL子句是否返回给定序列中的行（之前LIMIT可以启动并阻止进一步的分支执行）。

psql13.2 无论如何在退出编辑器时都会执行查询缓冲区，即使在退出编辑器之后也是如此 - 在这种情况下，缓冲区现在可能会保存最近执行的 SQL 查询。

重现：

1. 使用控制台或从控制台启动编辑器（vim在我的情况下） 。\e\e filenamepsql
2. 输入或不输入，没有区别。
3. 取消编辑:q（或:q!强制）

预期行为：回到启动编辑器之前的状态。（我取消了编辑！）
观察到的行为：重新执行上次执行的 SQL 查询。

似乎发生了什么\e：

1. 它执行 (相当于) \p，因此当前查询缓冲区在它为空时包含上一个命令。（编辑文件时用户看不到！） 手册\p ：

   \p或者\print

   将当前查询缓冲区打印到标准输出。如果当前查询缓冲区为空，则打印最近执行的查询。

2. 启动编辑器。如果提供了文件名，则加载文件，否则，加载查询缓冲区。

3. 如果编辑器以写入结束，则将结果复制到查询缓冲区。
   如果编辑被取消（:q在 vim 中），不要覆盖查询缓冲区。（它现在保存上一个命令！）

4. 执行查询缓冲区。或者，准确地说，执行以下操作：

   • 退出psql中的\e编辑器后如何防止查询执行？

该行为似乎可以\p（只需复制查询缓冲区，不会造成伤害），但不适用于\e，它可以执行我什至没有看到的命令。

它是一个错误吗？
无论哪种方式，我至少想在离开编辑器时禁用自动执行。但这似乎是不可能的。还是我错过了什么？

Postgres 12 的手册说：

以下操作始终受到并行限制。

• 公用表表达式 (CTE) 的扫描。
• 扫描临时表。
• ...

在同一手册页的下方：

PARALLEL RESTRICTED[...] 类似地，如果函数访问临时表、客户端连接状态、游标、预准备语句或系统无法跨工作器同步的杂项后端本地状态，则必须标记函数。例如，setseed并且random由于最后一个原因而受到并行限制。

没有提到 CTE。现在我不确定我是否可以PARALLEL SAFE用于包含 CTE 的函数。对我来说，只有那些是有意义的PARALLEL RESTRICTED。

上下文：我必须确定现有用户定义函数的最佳标签。该设置是自 Postgres 9.6 以来的新设置，并且可能对性能产生巨大影响，因为涉及非并行工作者的操作PARALLEL SAFE不会由并行工作人员执行，而PARALLEL RESTRICTED只能由领导者执行。（并PARALLEL USAFE完全禁用并行性。）

我在 pgsql-general 上发布了一个相关问题。

断言 1

连接运算符||可以连接任何字符串类型的值，返回text. 在 Postgres 中，每种类型都有一个文本表示，并且可以转换为text. 因此，引用手册：

但是，字符串连接运算符 ( ||) 仍然接受非字符串输入，只要至少一个输入是字符串类型

有关的：

• 如何连接 Postgres SELECT 中的列？

断言 2

连接一个或多个NULL值会产生结果NULL。

test=# SELECT (text 'foo' || NULL) IS NULL
test-#      , (text 'bar' || char '1' || NULL ) IS NULL
test-#      , (NULL::bigint || text 'baz') IS NULL;
 ?column? | ?column? | ?column? 
----------+----------+----------
 t        | t        | t

问题

是否可以连接 atext和一个NULL值并获得非空结果？

换句话说，这怎么可能？

test=# SELECT col IS NULL AS col_is_null
test-#     , (text 'foo' || col) IS NULL AS result_is_null
test-# FROM   tbl;
 col_is_null | result_is_null 
-------------+----------------
 t           | f

适用于任何 Postgres 版本。
我的一个客户偶然发现了这个，依靠结果是NULL，我发现它很有趣，可以分享。
这是一个有点技巧的问题，因为我知道答案。

_{注意： CASE或COALESCE捕捉NULL值通常是好的风格，但这不是这个问题的目的。这是关于与实际NULL值的连接，使用连接运算符||并且仍然得到非空结果。}

使用Amazon Aurora Postgres 2.0 版，它基本上是 PostgreSQL 10.4，带有修改的存储层。

尝试恢复包含多行的转储，例如：

ALTER FUNCTION myschema.f_myfunc(anyarray, anyelement) OWNER TO myrole;

ALTER FUNCTION对于每一个这样的情况，我都会收到一条错误消息：

ERROR:  improper qualified name (too many dotted names)

在 psql 中尝试相同的操作会产生相同的错误。即使是最简单的形式：

ALTER FUNCTION foo() OWNER TO myrole;

没有虚线名称。我在这里发现了类似的投诉： https ://forums.aws.amazon.com/thread.jspa?messageID=872096&tstart=0

一定是 Aurora 中的错误 - 还是我错过了什么？

我们一再看到尝试索引值超过最大大小的列失败。Postgres 10 有这样的错误信息：

ERROR:  index row size xxxx exceeds maximum 2712 for index "foo_idx"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
       Consider a function index of an MD5 hash of the value, or use full text indexing.

例子：

• 字符变化索引开销和长度限制
• 索引最大行大小错误

等等。

现在，a_horse_with_no_name 演示了一个具有更大text值（10000 个字符）的案例，它似乎仍然适用UNIQUE于 Postgres 9.6 中的索引。引用他的测试用例：

create table tbl (col text);
create unique index on tbl (col);

insert into tbl
values (rpad(md5(random()::text), 10000, md5(random()::text)));

select length(val) from x;  -- 10000

没有错误，并且列值确实测试了 10000 个字符的长度。

最近是否发生了变化，或者这怎么可能？

Postgres 函数使用波动率分类声明VOLATILE，STABLE或IMMUTABLE。众所周知，该项目对内置功能的这些标签非常严格。并且有充分的理由。突出的例子：表达式索引只允许IMMUTABLE函数，并且那些必须是真正不可变的以避免不正确的结果。

用户定义的函数仍然可以根据所有者的选择自由声明。该手册建议：

为了获得最佳优化结果，您应该使用对其有效的最严格的波动率类别来标记您的函数。

...并添加了一个广泛的列表，其中列出了不正确的波动率标签可能会出错的事情。

不过，在某些情况下，假装不变性是有道理的。大多数情况下，当您知道该函数实际上在您的范围内是不可变的时。例子：

• PostgreSQL 是否支持“不区分重音”排序规则？

除了对数据完整性的所有可能影响之外，对性能有什么影响？有人可能会认为声明一个函数IMMUTABLE只会对性能有益。是这样吗？

声明函数波动性IMMUTABLE 会损害性能吗？

让我们假设当前的 Postgres 10 可以缩小范围，但所有最近的版本都值得关注。

这是对上一个问题的评论的衍生：

• Postgres 9.4.4 查询需要永远

Recheck Cond:使用 PostgreSQL 9.4，在查询计划输出的位图索引扫描之后似乎总是有一行EXPLAIN。

就像在EXPLAIN引用问题的输出中一样：

->  Bitmap Heap Scan on table_three  (cost=2446.92..19686.74 rows=8159 width=7)
      Recheck Cond: (("timestamp" > (now() - '30 days'::interval)) AND (client_id > 0))
      ->  BitmapAnd  (cost=2446.92..2446.92 rows=8159 width=0)
            ->  Bitmap Index Scan on table_one_timestamp_idx  (cost=0.00..1040.00 rows=79941 width=0)
                  Index Cond: ("timestamp" > (now() - '30 days'::interval))
            ->  Bitmap Index Scan on fki_table_three_client_id  (cost=0.00..1406.05 rows=107978 width=0)
                  Index Cond: (client_id > 0)

或者在EXPLAIN ANALYZE一个简单的、巨大的表（很少work_mem）的输出中：

EXPLAIN ANALYZE SELECT * FROM aa WHERE a BETWEEN 100000 AND 200000;

Bitmap Heap Scan on aa  (cost=107.68..4818.05 rows=5000 width=4) (actual time=27.629..213.606 rows=100001 loops=1)
  Recheck Cond: ((a >= 100000) AND (a <= 200000))
  Rows Removed by Index Recheck: 758222
  Heap Blocks: exact=693 lossy=3732
  ->  Bitmap Index Scan on aai  (cost=0.00..106.43 rows=5000 width=0) (actual time=27.265..27.265 rows=100001 loops=1)
        Index Cond: ((a >= 100000) AND (a <= 200000))

这是否意味着必须在位图索引扫描后再次检查索引条件？
我们还能从EXPLAIN输出中学到什么？

鉴于当前 Postgres 9.4 中的此设置（来自此相关问题）：

CREATE TABLE foo (ts, foo) AS 
VALUES (1, 'A')  -- int, text
     , (7, 'B');

CREATE TABLE bar (ts, bar) AS
VALUES (3, 'C')
     , (5, 'D')
     , (9, 'E');

db<>fiddle here（也来自上一个问题）。

我用 a 写了SELECT一个FULL JOIN来实现引用问题的目标。简化：

SELECT ts, f.foo, b.bar
FROM   foo f
FULL   JOIN bar b USING (ts);

根据规范，处理该列的正确方法ts是没有表限定。任何一个输入值 (f.ts或b.ts) 都可以为 NULL。该USING子句创建了一些奇怪的情况：引入了一个实际上不存在于输入中的“输入”列。到目前为止如此优雅。

我把它放在一个 plpgsql 函数中。为了方便（或要求），我希望表函数的结果具有相同的列名。所以我们必须避免相同的列名和函数参数之间的命名冲突。最好通过选择不同的名称来避免，但我们在这里：

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR ts, foo, bar IN
      SELECT COALESCE(f.ts, b.ts), f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- so something
      RETURN NEXT;
   END LOOP;
END
$func$;

大胆强调突出问题。我不能ts像以前那样在没有表限定的情况下使用，因为 plpgsql 会引发异常（不是绝对必要的，但在大多数情况下可能很有用）：

ERROR:  column reference "ts" is ambiguous
LINE 1: SELECT ts, f.foo, b.bar
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

我知道我可以使用不同的名称或子查询或使用其他函数。但我想知道是否有办法引用该列。我不能使用表限定。人们会认为应该有一种方法。
在那儿？