主页 / dba / 问题

问题[postgresql-10](dba)

Martin Hope
user1068636
Asked: 2022-03-05 12:16:38 +0800 CST
  • 3

我以前问过这个问题，有人在 db fiddle 上发布了一个非常有用的答案。这对我来说非常有效。但是，现在我想问一个新的（相关）问题：

假设我清除了表中的所有行（即在 db fiddle 示例中，通过执行“从 id > 0 的水果中删除”来删除苹果和橙色行）。如果我插入另一个水果，如西瓜，id 将从 4 开始，而不是 1。如何强制 id 类型重置回 0，以便下一个插入将具有 id=1 ？

postgresql postgresql-10
Martin Hope
Felipe Guajardo
Asked: 2022-01-26 12:30:51 +0800 CST
  • 0

环境：PostgreSQL v10.6（AWS RDS 实例）对于给定的表：

CREATE temp TABLE lve
    ("fin" timestamptz, "vehiculo" int, "equipo" int)
;
    
INSERT INTO lve
    ("fin", "vehiculo", "equipo")
VALUES
    ('2021-12-01 00:00:00', 1, 888),
    (NULL, 3, 888),
    ('2021-05-01 00:00:00', 2, 888),
    ('2021-11-05 00:00:00', 10, 333),
    (NULL, 9, 333),
    ('2021-09-05 00:00:00', 5, 333)
;

对于给定的“ equipo ”列表中的每个值，我需要最后一条记录（按“ fin ”列），其中“ fin ”不为空。

对于单个给定的“ equipo ”，此查询有效：

select * from lve
where equipo = 333 and fin is not null
order by fin desc
limit 1

对于两个 ' equipo ' ，使用 UNION 我可以获得我想要的结果：

(select * from lve
where equipo = 333 and fin is not null
order by fin desc
limit 1)
union 
(select * from lve
where equipo = 888 and fin is not null
order by fin desc
limit 1);

结果：

鳍 车辆 等价
2021-11-05 00:00:00.000 -0300 10 333
2021-12-01 00:00:00.000 -0300 1 888

但实际上，由于该表包含许多其他车辆/equipo 的更多数据，并且我可以有一个包含 N ' equipo ' 的列表来查询，因此继续手动添加多个 UNION 并不是最佳选择。

有没有一种方法可以将其重写为单个查询，我可以只传递给定的“ equipo ”（888、333、nnn）的列表/数组？

PD：我的计划 B 是创建一个函数，为单个“ equipo ”返回我想要的记录，然后将它与数组一起使用，但我真的想知道这是否可以按照我要求的方式完成。

谢谢。

postgresql postgresql-10
Martin Hope
Brandon
Asked: 2021-07-02 15:02:20 +0800 CST
  • 3

我正在使用AWS Aurora PostgreSQL Serverless自动缩放。看起来好像缩放清除了共享缓冲区，所以当我们想要提高性能时，我们不得不面对 I/O 瓶颈。热身后，我们看到了巨大的性能提升。但是，如果我们在缩放后背靠背运行，第二次运行会更快。虽然我还没有看到任何关于共享缓冲区是否在缩放时被清除的具体信息，但我几乎可以肯定它是。

目前正在使用 Aurora Serverless PostgreSQL 10.14，它支持pg_prewarm扩展。看起来最新的文档表明 prewarm 支持在服务器重新启动后自动预热，但这是无服务器的，并且在文档中似乎没有提到自动预热的版本。

我发现这篇文章在重新启动服务器或从崩溃中恢复时非常适合 PostgreSQL。

  1. 如果我们至少可以在缩放后保留较低 ACU 节点的共享缓冲区的内容，那很好。
  2. 如果我们可以提前准确地预热需要在内存中的内容，那就太棒了！
  3. 有些桌子很大，我们希望有选择地预热我们想要的部分。 pg_prewarm支持first_block和last_block阻止表/索引的数字，但是如何知道要在其中放入什么值？

我们提前知道我们的高峰是什么时候，并在之前告诉 RDS 进行扩展，这样我们就有了一个可以准备的时间窗口。

我有哪些选择？

postgresql postgresql-10
Martin Hope
user3143770
Asked: 2021-01-04 16:23:17 +0800 CST
  • 1

我已经阅读了文档和以前的 stackoverflow 问题，但没有发现那些有用的问题。Postgres 版本 10.6

即使对于仅索引扫描，idx_tup_fetch 的值也在增加。所以我无法理解 idx_tup_read 和 idx_tup_fetch 之间的区别。

最初的理解是：idx_tup_read：从索引中读取的匹配记录。

idx_tup_fetch：记录在索引上匹配后从表中获取的实时行。所以我的理解是，只扫描索引，因为没有从表中获取数据，所以这不应该增加。

但是当我进行测试时，这种理解是不正确的。

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        4 |            2 |             2
(1 row)

postgres=> select * from test_monika where rank=100;
 id | name | rank 
----+------+------
 95 |      |  100
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        5 |            3 |             3
(1 row)

postgres=> select rank from test_monika where rank=100;
 rank 
------
  100
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        6 |            4 |             4
(1 row)

postgres=> select id from test_monika where rank=100;
 id 
----
 95
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        7 |            5 |             5
(1 row)

postgres=> select 1 from test_monika where rank=100;
 ?column? 
----------
        1
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        8 |            6 |             6
(1 row)
postgresql postgresql-10
Martin Hope
Rod
Asked: 2020-08-07 04:43:18 +0800 CST
  • 0

我们目前有一个活动/备用 postgresql 10 集群（基于 PAF/clusterlabs）。无论如何，服务器使用以下方式保持同步：wal_level = replica

这很好并且有效。

但是，我们想将单个数据库从集群复制到独立实例（用于分析），为此我们认为单个数据库的逻辑复制会做得很好，但是我们随后意识到我们需要更改复制类型集群（本质上会破坏它）

wal_level = 逻辑

上述假设是否正确（不能混合“副本和逻辑”）？关于如何实现相同结果是否有任何其他建议（将单个数据库复制出具有多个数据库的集群）

感谢您的任何反馈/建议

postgresql-10
Martin Hope
Oğuz Can Sertel
Asked: 2020-07-27 12:25:57 +0800 CST
  • 0

我有这个简单的表和用户记录。

我只想让具有 ilike 或其他东西的用户在 PostgreSQL 10.12 中获得更好的顺序。

CREATE TABLE users(
   ID INT PRIMARY KEY      NOT NULL,
   username           CHAR(50) NOT NULL
);

INSERT INTO users (id, username) VALUES(1,'mandela');
INSERT INTO users (id, username) VALUES(2,'delpiero');
INSERT INTO users (id, username) VALUES(3, 'gondela');
INSERT INTO users (id, username) VALUES(4, 'del');
INSERT INTO users (id, username) VALUES(5, 'dell');
INSERT INTO users (id, username) VALUES(6, 'andel');

当我查询“del”时，我想首先获得 id 为 4 的“del”用户。可以按长度排序。但这仍然不是我想要的。顺序应该是这样的。二阶必须是“dell”，三阶必须是“delpiero”，第四阶是“andel”，第五阶是“gondela”，第六阶是“mandela”。

select * from users where username ilike '%del%' order by length(users.username) asc;

这是 SQL 小提琴

postgresql postgresql-10
Martin Hope
Deepan Kaviarasu
Asked: 2020-07-16 19:36:21 +0800 CST
  • 2

我被一个查询困住了：

CREATE TABLE public.bulk_sample (
    serial_number character varying(255),
    validation_date timestamp,  -- timestamp of entry and exit
    station_id integer,
    direction integer           -- 1 = Entry | 2 = Exit
);

INSERT INTO public.bulk_sample VALUES
  ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 08:31:58', 120, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 08:50:22', 113, 2)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 10:16:56', 113, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 10:47:06', 120, 2)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 16:02:12', 120, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 16:47:45', 102, 2)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 19:26:38', 102, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 20:17:24', 120, 2)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 07:58:20', 119, 1)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 08:43:35', 104, 2)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 16:38:10', 104, 1)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 17:15:01', 119, 2)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 17:42:29', 119, 1)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 17:48:05', 120, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 15:17:59', 120, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 15:25:25', 118, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 16:16:12', 118, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 16:32:51', 120, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 19:31:20', 120, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 19:39:33', 118, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 20:57:50', 118, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 21:16:25', 120, 2)
;

我必须创建一个查询，其结果如下

source | dest | Count
120    | 113  |  1
113    | 120  |  1

我尝试了以下代码，但无法获得所需的结果：

SELECT serial_number
     , count(*)
     , min(validation_date) AS start_time
     , CASE WHEN count(*) > 1 THEN max(validation_date) END AS end_time
FROM  (
   SELECT serial_number, validation_date, count(step OR NULL) OVER (ORDER BY serial_number, 
validation_date) AS grp
   FROM  (
      SELECT *
           , lag(validation_date) OVER (PARTITION BY serial_number ORDER BY validation_date)
           < validation_date - interval '60 min' AS step
      FROM   table1 
       where BETWEEN '2020-02-01 00:00:00' AND '2020-02-01 23:59:59'
      ) sub1
   ) sub2
GROUP  BY serial_number, grp;

每次进出之间的时间间隔约为 55 分钟至 60 分钟。

我也尝试过内部联接，但无法按内部联接中的时间间隔进行分组

SELECT source.station_id AS source_station ,dest.station_id AS destination_station ,source.count FROM 
    (
        SELECT serial_number,station_id,count(bulk_transaction_id) FROM table1
        WHERE 
            direction = 1 AND 
            validation_date BETWEEN '2020-02-01 00:00:00' AND '2020-02-01 23:59:59' 
        GROUP BY serial_number,station_id
    )source

 INNER JOIN 
    (
        SELECT serial_number,station_id,count(bulk_transaction_id) FROM table1
        WHERE 
            direction = 2 AND 
            validation_date BETWEEN '2020-02-01 00:00:00' AND '2020-02-01 23:59:59'
        GROUP BY serial_number,station_id
    )dest
ON source.serial_number = dest.serial_number and source.station_id <> dest.station_id

挑战有时是进入日期为空，有时退出日期为空。

postgresql postgresql-10
Martin Hope
VBAGuy
Asked: 2020-07-09 07:37:59 +0800 CST
  • 0

我正在尝试使用另一列中存在的文本值更新 jsonb 列中的字段值，但出现一些语法错误；没有得到任何解决方案。

我正在尝试在 JSONB 下面交换 OutgoingVertexid 和 IncomingVertexId 的值

'[
      {
         "Edgeid":10,
         "Weight":100,
         "Active":1,
         "OutgoingVertexid":"",
         "IncomingVertexid":"G",
         "EdgeColor":"Black"
      }
   ]
'

因此，通过将所有值 OutgoingVertexid 和 IncomingVertexid 值放在临时表中来使用下面的代码。

UPDATE temp_table 
        SET 
        owner_property = CASE 
                            WHEN owner_outgoing_edge IS NOT NULL 
                                THEN jsonb_set(owner_property, '{OutgoingVertexid}', '""')
                            ELSE 
                                jsonb_set(owner_property, '{OutgoingVertexid}', ''||owner_incoming_edge::jsonb||'') 
                                END;

但得到以下错误：

错误：位置 1 的路径元素不是整数：

提前致谢

postgresql-10 json