主页 / dba / 问题

问题[postgresql](dba)

Martin Hope
Jayadevan
Asked: 2024-12-06 13:32:56 +0800 CST
  • 6

我有几个表，大小超过 1 TB。pg_stat_user_tables 中的 last_autovacuum 和 last_autoanalyze 列对于这些表具有 NULL。n_dead_tup 与 n_live_tup 的比率为 0.0001。有几个问题，我可以依赖 n_dead_tup 和 n_live_tup 数字吗？这些表有大量插入和少量更新，没有删除。如果我手动清理和分析这些表，这些列会更新吗？PostgreSQL 版本是 13.5

postgresql
Martin Hope
leventunver
Asked: 2024-12-06 00:15:36 +0800 CST
  • 5

我们最近发生了一起事故，导致我们的服务几分钟内不可用。

以下是导致该事件的liquibase脚本：

<addColumn tableName="registration">
    <column name="retry_at" value="null" type="DATETIME"/>
    <column name="retry_counter" value="null" type="BIGINT"/>
</addColumn>

变更集日志

如您所见，更改集花费了相当多的时间。我们的团队得出结论，默认值设置为null是导致此问题的原因。然而，我对此感到有些不满意。

当我检查 Postgres 第1页和第 2页时，我发现将值设置为 null 与完全删除默认值没什么不同。因此，我不明白为什么此默认值设置会导致此类问题。顺便提一下，表中有 1200 万行。

正如标题所示，我的问题是，设置默认值是否会导致这样的停机？

应用程序启动：

Started ApplicationKt in 289.989 seconds (process running for 290.727)

liquibase操作前的一些日志：

lqbase1

lqbase2

等待变更日志锁定

postgresql
Martin Hope
J. Mini
Asked: 2024-12-01 23:30:55 +0800 CST
  • 9

设置

CREATE TABLE persons
(
  person_id int not null,
  name TEXT 
);

INSERT INTO persons VALUES
(1, 'Adam'),
(2, 'Paul'),
(3, 'Tye'),
(4, 'Sarah');

CREATE TABLE json_to_parse
(
  person_id int not null,
  block json
);

INSERT INTO json_to_parse VALUES
  (1, '{"size": "small", "love": "x"}'),
  (2, '{"size": "medium", "love": "xx"}'),
  (3, '{"size": "big", "love": "xxx"}');

错误

运行没有问题

SELECT
  *
FROM
  json_to_parse
CROSS JOIN LATERAL
  json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
INNER JOIN
  persons
ON
  persons.person_id = json_to_parse.person_id;

但这并不

SELECT
  *
FROM
  json_to_parse,
  json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
INNER JOIN
  persons
ON
  persons.person_id = json_to_parse.person_id;

我收到错误“对表“json_to_parse”的 FROM 子句条目的引用无效”

为什么第二个查询会出错？文档说得很清楚，LATERAL对于表值函数来说，这是可选的

中出现的表函数FROM前面也可以加上关键字LATERAL，但对于函数来说，关键字是可选的；函数的参数FROM在任何情况下都可以包含对前面项提供的列的引用。

dbfiddle

postgresql
Martin Hope
Antti Keskinen
Asked: 2024-11-28 23:23:44 +0800 CST
  • 5

假设我们有以下数据，每秒测量一次温度。数据跨度为几年，因此有相当多的行。这是来自现场设备的测量数据。该表还有其他列，但它们与问题无关。

时间 温度_摄氏度
2024-11-01 00:00:00+00 20.1
2024-11-01 00:00:01+00 21.2
2024-11-01 00:00:02+00 21.6
2024-11-01 00:00:03+00 20.2
... ...
2026-12-31 23:59:57+00 25.4
2026-12-31 23:59:58+00 25.2
2026-12-31 23:59:59+00 25.6

我知道我可以使用一个GROUP BY子句来获取特定开始和结束时间之间一小时窗口内的平均值，如下所示：

  SELECT to_char("time", 'YYYY-MM-DD HH'), AVG("Temperature_deg_in_C")
    FROM "measurements"
   WHERE "time" BETWEEN '2024-11-01' AND '2024-12-01'
GROUP BY to_char("time", 'YYYY-MM-DD HH')

但是，这种语法感觉像是一种黑客行为，并且它不能完全适应例如 10 秒或其他更复杂的窗口框架（或分区）。

此外，我想具体了解如何使用 PostgreSQL 手册中概述的、、、、和类似工具正确且高效地完成OVER此操作。WINDOWPARTITION BYRANGEROWS

更新：窗口函数不能用在 Postgres 上实现这个目标，因为它们不会像我想象的那样创建“存储桶”或“框架”。GROUP BY是实现这一目标的方法。

最终目标是找到一种解决方案，其中窗口长度和测量单位（分钟、小时、天）以及观察期的开始和结束时间是灵活的，但查询主体本身保持不变。也就是说，一个简单易懂的参数化查询。

postgresql
Martin Hope
cam
Asked: 2024-11-25 23:31:49 +0800 CST
  • 4

有几个类似的问题（例如https://dba.stackexchange.com/questions/72419/filling-in-missing-dates-in-record-set-from-generate-series），但解决方案似乎对我的情况不起作用...本质上，我正在尝试为系列中不存在的日期生成零个条目，但我怀疑问题是我必须从时间戳中提取日期值？我已经使用 SQL 多年了，但对 postgres 还很陌生 - 到目前为止印象深刻。在这里尝试了左连接和右连接，但没有成功......

这是一个小测试用例（仍然鼓励使用 SQL 语句吗？）：

-- temp test table - works as expected
WITH incomplete_data(payment_date, payment_id) AS (
   VALUES 
     ('2024-09-06 11:26:57.509429+01'::timestamp with time zone, 'uuid01')
    ,('2024-09-06 12:26:57.509429+01', 'uuid02')
    ,('2024-09-07 07:26:57.509429+01', 'uuid03')
    ,('2024-09-08 10:26:57.509429+01', 'uuid05')
    ,('2024-09-08 12:26:57.509429+01', 'uuid08')
    ,('2024-09-08 14:26:57.509429+01', 'uuid11')
    ,('2024-09-10 09:26:57.509429+01', 'uuid23')
   )
select * from incomplete_data;

-- generated dates - work as expected
select * FROM  (
   SELECT generate_series(timestamp '2024-01-01'
                        , timestamp '2024-01-01' + interval '1 year - 1 day'
                        , interval  '1 day')::date
   ) d(day)
;
   
-- join - failing to do what I was hoping..
WITH incomplete_data(payment_date, payment_id) AS (
   VALUES 
     ('2024-09-06 11:26:57.509429+01'::timestamp with time zone, 'uuid01')
    ,('2024-09-06 12:26:57.509429+01', 'uuid02')
    ,('2024-09-07 07:26:57.509429+01', 'uuid03')
    ,('2024-09-08 10:26:57.509429+01', 'uuid05')
    ,('2024-09-08 12:26:57.509429+01', 'uuid08')
    ,('2024-09-08 14:26:57.509429+01', 'uuid11')
    ,('2024-09-10 09:26:57.509429+01', 'uuid23')
   )
select count(payment_id), date_trunc('day',payment_date)::date as time
FROM  (
   SELECT generate_series(timestamp '2024-01-01'
                        , timestamp '2024-01-01' + interval '1 year - 1 day'
                        , interval  '1 day')::date
   ) d(day)
right  JOIN incomplete_data p ON date_trunc('day',payment_date) = d.day
where payment_date BETWEEN '2024-09-01T12:55:36.824Z' AND '2024-09-30T13:55:36.824Z'
GROUP  BY date_trunc('day',payment_date)
ORDER  BY date_trunc('day',payment_date);

 count |    time
-------+------------
     2 | 2024-09-06
     1 | 2024-09-07
     3 | 2024-09-08
     1 | 2024-09-10
(4 rows)

我希望获取该月中每一天的一行，其中未填充的天数为零。背景是这是为了填充 grafana 查询。

有人能指出我做错了什么吗？或者我没能理解这里更大的问题吗？我的版本是：PostgreSQL 15.9 (Debian 15.9-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

更新
下面的 jjanes 答案帮助我澄清了连接和过滤的顺序 - 这是所需的选择：

WITH incomplete_data(payment_date, payment_id) AS (
   VALUES 
     ('2024-09-06 11:26:57.509429+01'::timestamp with time zone, 'uuid01')
    ,('2024-09-06 12:26:57.509429+01', 'uuid02')
    ,('2024-09-07 07:26:57.509429+01', 'uuid03')
    ,('2024-09-08 10:26:57.509429+01', 'uuid05')
    ,('2024-09-08 12:26:57.509429+01', 'uuid08')
    ,('2024-09-08 14:26:57.509429+01', 'uuid11')
    ,('2024-09-10 09:26:57.509429+01', 'uuid23')
   )
select count(payment_id), d.day as time
FROM  (
   SELECT generate_series(timestamp '2024-01-01'
                        , timestamp '2024-01-01' + interval '1 year - 1 day'
                        , interval  '1 day')::date
   ) d(day)
left JOIN incomplete_data p ON date_trunc('day',payment_date) = d.day
where d.day BETWEEN '2024-09-01T12:55:36.824Z' AND '2024-09-30T13:55:36.824Z'
GROUP  BY d.day
ORDER  BY d.day
;
postgresql
Martin Hope
laurent
Asked: 2024-11-25 16:50:09 +0800 CST
  • 5

在阅读有关流复制协议的文档时，我发现我可以获取快照名称。这似乎很方便在开始流式传输复制消息之前执行同步并确保我拥有所有数据。但是，在调用 pg_create_logical_replication_slot sql 函数时我无法获取快照（并且我的客户端未实现流复制协议）。我如何获取此快照名称以执行初始同步？

就上下文而言，我有一个仅附加表，并想将其用作某种“事件生产者”，使用逻辑复制来构建订阅机制。

postgresql
Martin Hope
Téo Lefebvre
Asked: 2024-11-25 11:53:45 +0800 CST
  • 5

我想配置我的 PostgreSQL 服务器以同时启用使用 TLS 证书和密码的身份验证（对于同一个连接，而不仅仅是启用两种身份验证机制）。

然后，对于要进行身份验证的客户端，它必须：

  • 提供有效的证书，其通用名称中设置了用户名，并由受信任的根 CA 签名
  • 为该特定用户提供密码

我想这样做是为了提高安全性。这样，即使我的客户端证书或我的根 CA 被泄露，你仍然需要密码进行身份验证。

我不知道该怎么做。在PostgreSQL 文档的这个页面中，说当你使用 TLS 证书时：

不会向客户端发送任何密码提示

那么，这可能吗？如果可以，如何做？

postgresql
Martin Hope
Ruud van den Boomen
Asked: 2024-11-22 20:35:54 +0800 CST
  • 7

我知道添加外键约束需要进行表扫描并对SHARE ROW EXCLUSIVE两个表进行锁定。

为了防止可能冗长的表扫描，可以使用该方法添加约束NOT VALID。但我想知道，在添加新列时，是否也应该使用NOT VALID，或者 Postgres 是否足够智能，可以识别出这是一个新列，因此不需要扫描整个表？

我正在使用Django，添加外键列的生成的SQL如下所示：

ALTER TABLE
    "example"
ADD
    COLUMN "new_column_id" integer NULL CONSTRAINT "example_new_column_id_fk" REFERENCES "another_table"("id") DEFERRABLE INITIALLY DEFERRED;

SET
    CONSTRAINTS "example_new_column_id_b781b6be_fk" IMMEDIATE;
postgresql
Martin Hope
André Diego Piske
Asked: 2024-11-22 03:43:30 +0800 CST
  • 6

我有一个 PostgreSQL 15 部署，其中包含数千万条记录的分区表。

我一直在尝试创建索引，我对 Btree 索引占用的空间如此之小感到惊讶。

因此，分区表dummy_name_partition_01中大约有 1300 万条记录。不确定是否相关，但记录可能会变得有点大，平均每条记录 2.66 KiB（不计算索引，分区大约有 30 GiB）。

其中一列（名为record_type）是我正在使用索引处理的列，它存储了一个小字符串（<50 个字符）。虽然它是 TEXT 类型而不是 ENUM，但它的值始终是大约 300 个可能的字符串之一。

我最初为该record_type列创建了一个 BRIN 索引，以节省磁盘使用量。看起来索引大小在磁盘上只有大约 1 MiB。确实很小。

现在，我在使用 BRIN 索引时遇到了问题。它坚持进行顺序扫描，所以 brin 索引好像没用。我担心 btree 索引会太大，但后来我删除了 BRIN 索引并将其创建为 BTREE，它的大小只有 92 MiB。我预计至少在 1 GiB 范围内！

为了测量索引大小，我查询了information_schema.tables表并使用了函数pg_table_size。pg_indexes_size也就是说，我在没有索引时使用查询索引大小pg_indexes_size，然后在创建索引后运行它，并将差值作为索引大小。当然，我这样做了几次，这样我就可以从 BRIN 和 BTREE 中获得数字。

该索引与 btree 索引一样简单，与brin 索引CREATE INDEX foo_bar ON dummy_namy_partition_01 (record_type)一样。USING BRIN

现在，我想知道：Postgres 是否以某种方式将指向列中数据的指针存储起来record_type，而不是到处存储重复的字符串，那么这就是索引位于近一百个 MiB 而不是几 GB 的原因吗？或者，这里发生了什么？

postgresql
Martin Hope
snorris
Asked: 2024-11-22 02:33:45 +0800 CST
  • 5

PG17 注释的第一个不兼容说明：

需要引用非默认模式的表达式索引和物化视图所使用的函数必须在函数创建期间指定搜索路径。

ltree使用依赖于所安装扩展的函数重新创建物化视图（在 PG 14-16 中运行良好）public在 PG17 上失败，原因如下：

ERROR:  operator does not exist: public.ltree <@ public.ltree
LINE 3:   code_b <@ code_a

CONTEXT:  SQL function "myfunc" during inlining

如果我set search_path = public按照不兼容性说明的建议添加函数定义，则可以创建物化视图。

但是一旦set search_path添加，该功能就不再可用 - 规划器不会获取ltree类型的索引。

有没有一种解决方法可以让物化视图中使用的函数来选择ltree运算符public？或者促使规划器使用索引？还是我做错了什么？

在全新的 PG 17.0 数据库上进行重现（在 dockerized 上也基本相同PostgreSQL 17.1 (Debian 17.1-1.pgdg110+1)）：

select version();
                                                           version
------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.0 (Homebrew) on aarch64-apple-darwin24.1.0, compiled by Apple clang version 16.0.0 (clang-1600.0.26.4), 64-bit

-- check the default search path
SELECT setting FROM pg_settings WHERE name = 'search_path';
     setting
-----------------
 "$user", public
(1 row)


-- setup test data
create extension if not exists ltree;

create schema test;

create table test.table
(id serial primary key,
 code ltree);

insert into test.table (code)
select
  concat_ws('.',
    rpad(trunc(random() * 10 + 1)::text, 3, '0'),
    rpad(trunc(random() * 10 + 1)::text, 3, '0'),
    rpad(trunc(random() * 100 + 1)::text, 3, '0'),
    rpad(trunc(random() * 100 + 1)::text, 3, '0'),
    rpad(trunc(random() * 100 + 1)::text, 3, '0')
)::ltree as code
from generate_series(1, 1000000) s(i);

create index on test.table using gist (code);
create index on test.table using btree (code);

-- create test function as per pg16 (no search path)
create or replace function test.myfunc(
  code_a ltree,
  code_b ltree)
RETURNS boolean language sql AS $$
SELECT
  code_b <@ code_a
$$
immutable parallel safe;

-- test the function - it picks up the index
explain analyze
select
  a.id
from test.table a
inner join (select * from test.table limit 10) as b
on test.myfunc(a.code, b.code);
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.41..15541.90 rows=100000 width=4) (actual time=0.105..1.078 rows=11 loops=1)
   ->  Limit  (cost=0.00..0.20 rows=10 width=36) (actual time=0.015..0.019 rows=10 loops=1)
         ->  Seq Scan on "table"  (cost=0.00..20310.00 rows=1000000 width=36) (actual time=0.014..0.016 rows=10 loops=1)
   ->  Index Scan using table_code_idx on "table" a  (cost=0.41..1454.16 rows=10000 width=36) (actual time=0.086..0.104 rows=1 loops=10)
         Index Cond: (code @> "table".code)
 Planning Time: 1.475 ms
 Execution Time: 1.175 ms
(7 rows)

-- try and use the function in a materialized view,
-- (works fine in PG14,PG16, fails on PG17)
create materialized view test.myview as
select
  a.id
from test.table a
inner join (select * from test.table limit 10) as b
on test.myfunc(a.code, b.code);
ERROR:  operator does not exist: public.ltree <@ public.ltree
LINE 3:   code_b <@ code_a
                 ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY:
SELECT
  code_b <@ code_a

CONTEXT:  SQL function "myfunc" during inlining

-- try specifying search_path as recommended in
-- PG17 release notes
create or replace function test.myfunc(
  code_a ltree,
  code_b ltree)
RETURNS boolean set search_path = public language sql AS $$
SELECT
  code_b <@ code_a
$$
immutable parallel safe;

-- test the new function - it works, but does not pick up the index
explain analyze
select
  a.id
from test.table a
inner join (select * from test.table limit 10) as b
on test.myfunc(a.code, b.code);
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2645310.33 rows=3333333 width=4) (actual time=0.335..6668.876 rows=11 loops=1)
   Join Filter: test.myfunc(a.code, b.code)
   Rows Removed by Join Filter: 9999989
   ->  Seq Scan on "table" a  (cost=0.00..20310.00 rows=1000000 width=36) (actual time=0.034..46.823 rows=1000000 loops=1)
   ->  Materialize  (cost=0.00..0.35 rows=10 width=32) (actual time=0.000..0.000 rows=10 loops=1000000)
         ->  Subquery Scan on b  (cost=0.00..0.30 rows=10 width=32) (actual time=0.008..0.012 rows=10 loops=1)
               ->  Limit  (cost=0.00..0.20 rows=10 width=36) (actual time=0.008..0.011 rows=10 loops=1)
                     ->  Seq Scan on "table"  (cost=0.00..20310.00 rows=1000000 width=36) (actual time=0.006..0.007 rows=10 loops=1)
 Planning Time: 0.313 ms
 Execution Time: 6668.912 ms
(10 rows)

-- Try using the updated func in materialized view
-- The view is successfully created (on this small sample), 
-- but is impractical on larger data because it doesn't use the index
create materialized view test.myview as
select
  a.id
from test.table a
inner join (select * from test.table limit 10) as b
on test.myfunc(a.code, b.code);
SELECT 11
postgresql