主页 / user-113660

Radu Dumbrăveanu's questions

Martin Hope
Radu Dumbrăveanu
Asked: 2021-05-14 06:02:23 +0800 CST
  • 0

为什么DISTINCT ON要对行进行排序？例如查询

SELECT DISTINCT ON (a) a
FROM (
  VALUES (2), (3), (1)
) x(a);

会有结果

1
2
3

而没有DISTINCT ON

SELECT a
FROM (
  VALUES (2), (3), (1)
) x(a);

我会得到正确的顺序

2
3
1

如何保持定义值的自然顺序？“自然顺序”是指“定义值的顺序”。我知道如果我没有用 明确指定它，DBMS 可能会更改顺序ORDER BY，但我没想到DISTINCT ON我会以这种方式进行干预。

postgresql distinct
Martin Hope
Radu Dumbrăveanu
Asked: 2021-03-31 00:33:13 +0800 CST
  • 2

让我们创建一个临时表（我选择临时表，因为 autovacuum 不会为这种表运行）：

CREATE TEMP TABLE test (
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  value int
);
INSERT INTO test (value) SELECT 0 FROM generate_series(0, 250);
SELECT ctid, * FROM test;

我们将看到该表由两页组成：

(0,1)   1   0
(0,2)   2   0
...
(0,226) 226 0
(1,1)   227 0
...
(1,25)  251 0

现在如果我们更新一行

UPDATE test SET value = -1 WHERE id = 1;
SELECT ctid, * FROM test WHERE value <> 0;

我们将看到新的行版本被插入到表的末尾（在第二页，它有足够的可用空间进行此操作），这是标准的 PostgreSQL 行为（来自 (0,1) 的旧行版本是标记为死亡）

(1,26)  1   -1

让我们检查页面的可用空间：

CREATE EXTENSION pg_freespacemap;
SELECT * FROM pg_freespace('test');

我们得到

0   0
1   0

即没有可用空间；文档说在真空运行后更新了可用空间图（FSM）。现在，如果我们从第一页更新另一行：

UPDATE test SET value = -1 WHERE id = 2;
SELECT ctid, * FROM test WHERE value <> 0;

我们会看到

(0,227) 2   -1
(1,26)  1   -1

新行版本并没有添加到第2页而是添加到当前页，因为我们在更新id为1的记录时腾出了空间。但是这种做法不符合我对FSM的理解。以下是我的问题：

  1. pg_freespace('test')如果返回零，PostgreSQL 如何在第二次更新时知道第一页中有可用空间？我认为这个函数为您提供了 FSM，而 FSM 又在 UPDATE 的情况下使用，以确定哪个页面有足够的可用空间来存储新的行版本。
  2. 一般来说，我在想，即使第一行被标记为死，但它会在第一页占据一些空间，并且只有在真空之后才会释放这个空间。因此，我期望第二个 UPDATE 也会将新行版本添加到第二页。
postgresql vacuum
Martin Hope
Radu Dumbrăveanu
Asked: 2021-01-29 23:29:05 +0800 CST
  • 2

让我们假设一个表foo有资格进行 autovacuum 分析，例如，我插入了一些foo超过autovacuum_analyze_scale_factor * number of rows + autovacuum_analyze_threshold有效阈值的行。我不知道应该满足哪些条件才能让 autovacuum 对foo.

foo只有在没有事务的情况下才会对表运行 autovacuum 分析吗？或者它甚至可以在活动事务期间运行，条件是：（1）没有事务正在读取表的先前版本foo和（2）自动真空分析可以SHARE UPDATE EXCLUSIVE锁定表foo？

这些问题与具体案例有关。我在一个事务中执行大量 INSERT foo，然后foo在第二个事务中执行一些 UPDATE。foo我需要在第二个事务之前运行 autovacuum 分析，以便为我的查询计划程序提供更好的估计的最新统计信息。有没有办法保证 autovacuum 分析将在第二个事务之前运行？也许在事务之间睡几毫秒（我正在从 Java 应用程序运行两个事务）？

postgresql execution-plan
Martin Hope
Radu Dumbrăveanu
Asked: 2021-01-29 05:19:58 +0800 CST
  • 1

让我们在 PostgreSQL 13 数据库中创建两个测试表：

CREATE TABLE foo (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  value int NOT NULL
);
CREATE TABLE bar (
  id bigint PRIMARY KEY,
  category_id bigint NOT NULL,
  foo_id bigint REFERENCES foo (id),
  value int
);
CREATE INDEX bar_category_id_ix ON bar (category_id);

并禁用autovacuum这些表：

ALTER TABLE foo SET (autovacuum_enabled = false);
ALTER TABLE bar SET (autovacuum_enabled = false);

将 500000 条（一百万的一半）记录插入foo，将它们传输到bar表并分析：

INSERT INTO foo (value) SELECT * FROM generate_series(1, 500000);
ANALYZE foo;
INSERT INTO bar (id, category_id, foo_id, value) SELECT id, 1, id, value FROM foo WHERE value <= 500000;
ANALYZE bar;

（可选）确保在这些表上仅执行ANALYZE(no )：autovacuum

SELECT relname, last_autovacuum, last_vacuum, last_autoanalyze, last_analyze FROM pg_stat_user_tables WHERE relname IN ('foo', 'bar');

插入另一块 500000 条记录（但不要运行ANALYZE）：

INSERT INTO foo (value) SELECT * FROM generate_series(500001, 1000000);
INSERT INTO bar (id, category_id, foo_id, value) SELECT id, 2, id, value FROM foo WHERE value > 500000;

由于我们没有运行ANALYZE表统计是过时的，它与包含半百万条记录的阶段foo有关bar。现在让我们检查查询计划：

EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id
WHERE category_id = 2;
----
Nested Loop  (cost=0.85..12.89 rows=1 width=40)
  ->  Index Scan using bar_category_id_ix on bar  (cost=0.42..4.44 rows=1 width=28)
        Index Cond: (category_id = 2)
  ->  Index Scan using foo_pkey on foo  (cost=0.42..8.44 rows=1 width=12)
        Index Cond: (id = bar.foo_id)

和

EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id;
---
Hash Join  (cost=32789.00..71320.29 rows=999864 width=40)
  Hash Cond: (bar.foo_id = foo.id)
  ->  Seq Scan on bar  (cost=0.00..17351.64 rows=999864 width=28)
  ->  Hash  (cost=15406.00..15406.00 rows=1000000 width=12)
        ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=12)

我了解第一个查询计划错误地估计了仅 1 行 ( rows=1) 的条件category_id = 2，因为统计信息已过时（ANALYZE在插入记录之前执行category_id = 2）。(1) 但是，第二个查询计划是如何达到rows=999864对条件的良好估计 () 的bar.foo_id = foo.id？

此外，如果我们运行：

EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id
WHERE category_id = 1;
----
Hash Join  (cost=32789.00..73819.95 rows=999864 width=40)
  Hash Cond: (bar.foo_id = foo.id)
  ->  Seq Scan on bar  (cost=0.00..19851.30 rows=999864 width=28)
        Filter: (category_id = 1)
  ->  Hash  (cost=15406.00..15406.00 rows=1000000 width=12)
        ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=12)

(2) 为什么规划器估计条件为 999864 行category_id = 1？统计数据应该显示大约 500000 行满足它？

注意：我提出这些问题是因为根据经验，我观察到即使不分析表，只包含主键列的条件也会产生更好的查询计划，但我在 PostgreSQL 官方文档中没有找到任何关于这种行为的信息。

postgresql execution-plan
Martin Hope
Radu Dumbrăveanu
Asked: 2020-03-12 05:25:16 +0800 CST
  • 1

关于 PgBouncer 日志中的此类错误：

2020-03-10 03:46:01.730 EDT [25305] LOG C-0xa5cf00: testdb/[email protected]:49214 closing because: query_wait_timeout (age=120s)
2020-03-10 03:46:01.730 EDT [25305] WARNING C-0xa5cf00: testdb/[email protected]:49214 pooler error: query_wait_timeout

是否有可能查看（或记录）触发错误的查询？此错误的最常见原因是什么？停止的服务器，繁忙的服务器？

更新：来自 PostgreSQL 的日志文件在 2020-03-10 03:46:01 没有显示任何错误

pgbouncer
Martin Hope
Radu Dumbrăveanu
Asked: 2020-02-15 01:06:02 +0800 CST
  • 2

character varying与text作为 db 函数参数的类型之间是否有任何显着差异（例如性能） ？我有 2 个使用示例：

示例 1：

CREATE OR REPLACE FUNCTION func1(table_name <character varying|text>)
  RETURNS integer AS $$
DECLARE
  RESULT integer;
BEGIN
  EXECUTE 'SELECT COUNT(*) FROM ' || table_name INTO result;
  RETURN result;
END; 
$$ LANGUAGE plpgsql;

示例 2：

CREATE OR REPLACE FUNCTION func2(filter_value <character varying|text>)
  RETURNS integer AS $$
DECLARE
  RESULT integer;
BEGIN
  SELECT COUNT(*) INTO RESULT FROM <some_table> WHERE <some_column> = filter_value;
  RETURN result;
END; 
$$ LANGUAGE plpgsql;

注意在第二个例子中，some_column类型是textiffilter_value是类型character varying，反之亦然

postgresql functions
Martin Hope
Radu Dumbrăveanu
Asked: 2019-04-23 23:32:54 +0800 CST
  • 1

在 PostgreSQL 中是否可以临时授予用户角色/权限？当需要解决问题并且用户可能仅在短时间内需要某些特定角色/特权（甚至是超级用户）时，我认为这是一种解决方案。

postgresql security
Martin Hope
Radu Dumbrăveanu
Asked: 2019-04-05 05:33:47 +0800 CST
  • 3

在文档中CREATE FUNCTION声明“您必须拥有替换它的功能（这包括成为拥有角色的成员）。”

两个问题：

  1. 我不明白“这包括成为拥有角色的成员”部分到底是什么意思。

  2. 是否可以有多个用户（非超级用户）可以修改同一组函数的主体？我有一个创建函数、表等的用户（Liquibase 用户），我想添加一个调试用户，该用户应该有权修改第一个用户创建的函数以测试一些假设或修补程序？

postgresql permissions