主页 / user-12663

Gajus's questions

Martin Hope
Gajus
Asked: 2022-10-22 10:01:29 +0800 CST
  • 7

假设我有一张宽user_account桌子。

它是重读表，并且很少发生对该表的更新，除了一列：该表有列last_active_at，每次用户发出请求时都会更新。

last_active_at碰巧很少被阅读。

last_active_at将列移动到单独的表是否有意义？

我的想法是，这将减少与user_account表相关的任何查询的锁定时间。

postgresql
Martin Hope
Gajus
Asked: 2021-05-18 18:14:37 +0800 CST
  • 8

我有桌子currency_pair(c1, c2)；值为(usd,bnb), (cake,bnb), (cake,eth).

我需要找到允许我比较的最短usd路径eth。

这里的结果将是那些相同的值。然后我可以使用第一对建立 usd-bnb 关系，然后我可以使用它来计算 usd-cake 关系，然后我可以使用它来计算 usd-eth。

由于顺序不是确定的，我做的第一步是创建一个物化视图currency_pair_map(c1, c2)，它是 的并集select c1, c2 union select c2, c1。这似乎简化了逻辑。

如果我正确地考虑了这一点，我需要做的是使用WITH RECURSIVE吗？我还应该有某种“depth_limit”参数，以确保在无法建立一对时查询失败。

大声思考这个问题，我们应该始终从以下内容开始：

SELECT *
FROM currency_pair
WHERE
  c1 = 'usd' AND
  c2 = 'eth'

如果有结果，我们应该到此为止。

如果没有，那么我们需要找到所有usd-*对并继续搜索，直到找到以 结尾的一对eth。

使用这个逻辑，到目前为止我有：


WITH RECURSIVE pair_route AS (
  SELECT
    1 depth,
    cp1.id,
    cp1.c1,
    cp1.c2
  FROM currency_pair cp1
  WHERE
    cp1.c1 = 'usd'
  UNION
  SELECT
    pr1.depth + 1,
    cp2.id,
    cp2.c1,
    cp2.c2
  FROM pair_route pr1
  INNER JOIN currency_pair cp2 ON cp2.c1 = pr1.c2
  WHERE
    pr1.depth < 4
)
SELECT *
FROM pair_route;

我认为这是正确的。现在我只需要跟踪路径 (ID) 并确定最短路径。

postgresql recursive
Martin Hope
Gajus
Asked: 2019-08-16 04:33:59 +0800 CST
  • 0

我有一张记录在电影院观看的电影的表格：

CREATE TABLE cinema_movie_name (
  id integer DEFAULT nextval('cinema_movie_name_id_seq'::regclass) PRIMARY KEY,
  cinema_id integer NOT NULL REFERENCES cinema(id) ON DELETE CASCADE ON UPDATE RESTRICT,
  name text NOT NULL,
  movie_id integer REFERENCES movie(id) ON DELETE SET NULL ON UPDATE RESTRICT,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

每次我们在电影院看到一部新电影的名字，我们都会把它记录在这个表中，并将它映射到movie_id我们的数据库中。

当前的唯一性是强制执行的(cinema_id, name)，即

CREATE UNIQUE INDEX cinema_movie_name_cinema_id_name_idx ON cinema_movie_name(cinema_id, name);

问题是，在很长一段时间内，可能会有多部同名的不同电影，即在某个任意持续时间(id, cinema_id, name, movie_id)变得无效之后。

因此，我需要创建一个索引，在±3 个月(cinema_id, name)内实施唯一约束。created_at

如何使用时间列创建唯一索引？

postgresql
Martin Hope
Gajus
Asked: 2019-07-04 08:45:10 +0800 CST
  • 2

我编写了一个自定义PostgreSQL 客户端，我想添加一个自动捕获查询执行时间的函数。

executionTimeSlonik 结果中的当前值记录了总查询执行时间，包括往返和解析。为了捕获实际查询执行时间，我计划在每个查询前加上EXPLAIN ANALYZE，解析解释并捕获查询结果。但是，我找不到运行 EXPLAIN ANALYZE 并捕获实际查询结果的方法（除了解释输出）。

如何运行 EXPLAIN ANALYZE 并获取查询结果？

postgresql
Martin Hope
Gajus
Asked: 2019-03-04 04:46:33 +0800 CST
  • 1

我正在实现基于游标的分页。

要求是按任意条件（例如描述）对数据集进行排序，并逐行返回N行X（其中X表示最后一页的最后一项）。

第一页很简单：

SELECT
  id
FROM probe
ORDER BY description ASC
LIMIT 5

假设此查询返回 IDs 4, 5, 2, 1, 3，那么下一个查询必须在id3 之后从同一个查询中产生 5 个下一个结果。

注意：我们不能LIMIT ... OFFSET在第一页之后使用，因为偏移量是相对于我们不知道的引用结果的位置。

我能想到解决这个问题的唯一方法是：

在数据集中查找 ID 3 的行号：

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  )
SELECT
  di1.row_number
FROM data_index di1
WHERE di1.id = 3;

然后使用位置来偏移数据集。

全部一起：

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  ) 
SELECT
  di1.id
FROM data_index di1
WHERE di1.row_number > (
  SELECT
    di1.row_number
  FROM data_index di1
  WHERE di1.id = 3
)
LIMIT 10;

这种方法的缺点是data_index需要将整个子查询加载到内存中。有没有更有效的方法？

postgresql pagination
Martin Hope
Gajus
Asked: 2018-05-20 03:17:04 +0800 CST
  • 0

我目前正在使用SKIP LOCKED FOR UPDATE这样安排任务：

UPDATE task
SET started_at = NOW()
WHERE id IN (
  SELECT id
  FROM task
  WHERE /* ... bunch of logic ... */
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id

我想抽象用于使用视图选择未完成任务的子查询，例如

CREATE VIEW outstanding_task AS
SELECT *
FROM task
WHERE /* ... bunch of logic ... */

然后使用视图选择任务进行调度，例如

UPDATE task
SET started_at = NOW()
WHERE id IN (
  SELECT id
  FROM outstanding_task
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id

在此设置中使用视图吗SKIP LOCKED FOR UPDATE？即它是否保证不会选择相同的 ID？

如何测试才能确定？

postgresql
Martin Hope
Gajus
Asked: 2018-05-18 16:00:52 +0800 CST
  • 5

例子：

  • 如果当前时间是 2018-05-17 22:45:30 并且期望的时间间隔是INTERVAL '5 minute'，那么期望的输出是 2018-05-17 22:50:00。
  • 如果当前时间是 2018-05-17 22:45:30 并且期望的时间间隔是INTERVAL '10 minute'，那么期望的输出是 2018-05-17 22:50:00。
  • 如果当前时间是 2018-05-17 22:45:30 并且期望的时间间隔是INTERVAL '1 hour'，那么期望的输出是 2018-05-17 23:00:00。
  • 如果当前时间是 2018-05-17 22:45:30 并且期望的时间间隔是INTERVAL '1 day'，那么期望的输出是 2018-05-18 00:00:00。
postgresql datetime
Martin Hope
Gajus
Asked: 2018-05-14 02:33:17 +0800 CST
  • 2

我有一个复杂的查询表，用于识别和之间的SELECT关系。这是一个表达式的简化：event_idattributeVALUES

SELECT event_id, attribute
FROM (
  VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
) event_attribute (event_id, attribute)

我想(event_id, '2D')为每个event_id尚未与3Dor2D属性关联的记录包含一个额外的记录。如何有条件地将行附加到结果集中？

在上表的情况下，预期结果将是：

(1, '2D'),
(1, 'IMAX'),
(2, 'IMAX'),
(2, '2D'),
(3, '3D')

还有一个表格event，每个相关的id.

postgresql
Martin Hope
Gajus
Asked: 2018-05-04 13:12:05 +0800 CST
  • 0

我需要显示一张图表，说明在不同日期的不同时间间隔发生了多少门票销售，例如，周五演出有多少门票提前 3 小时售出，等等。

最终结果将类似于：

在此处输入图像描述

我通过计算SELECT语句中不同偏移量的值编写了一个查询：

WITH
  -- Create a date range; use LEFT JOIN/ COALESCE to construct date specific report; we don't want gaps in the data
  dates AS (
    SELECT generate_series(
      '2018-04-26',
      '2018-04-28',
      INTERVAL '1 day'
    ) AS date
  ),
  sales AS (
    SELECT
      ts1.date,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at < INTERVAL '0 hour' THEN 1 ELSE 0 END) after_event_start,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '1 hour' AND INTERVAL '2 hour' THEN 1 ELSE 0 END) hour_0_to_hour_1,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '1 hour' AND INTERVAL '2 hour' THEN 1 ELSE 0 END) hour_1_to_hour_2,
    -- [..]
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '22 hour' AND INTERVAL '23 hour' THEN 1 ELSE 0 END) hour_22_to_hour_23,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '23 hour' AND INTERVAL '24 hour' THEN 1 ELSE 0 END) hour_23_to_hour_24,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at > INTERVAL '24 hour' THEN 1 ELSE 0 END) after_24_hour
    FROM ticket_sale ts1
    WHERE
      ts1.movie_id = 1012718 AND
      ts1.starts_at > '2018-04-26' AND
      ts1.starts_at < '2018-04-28'::date + INTERVAL '1 day'
    GROUP BY ts1.date
  )
SELECT
  to_char(d1.date, 'YYYY-MM-DD') "date",
  COALESCE(s1.hour_0_to_hour_1, 0) hour_0_to_hour_1,
  COALESCE(s1.hour_1_to_hour_2, 0) hour_1_to_hour_2,
  -- [..]
  COALESCE(s1.hour_22_to_hour_23, 0) hour_22_to_hour_23,
  COALESCE(s1.hour_23_to_hour_24, 0) hour_23_to_hour_24,
  COALESCE(s1.day_6_to_day_7, 0) after_24_hour
FROM dates d1
LEFT JOIN sales s1 ON s1.date = to_char(d1.date, 'YYYY-MM-DD')
ORDER BY d1.date

这行得通。但是，作为查询，它看起来很奇怪。

有没有更好的方法来计算不同时间间隔的销售额？

postgresql date
Martin Hope
Gajus
Asked: 2013-05-21 23:44:45 +0800 CST
  • 3

加入新的 Percona 节点失败：

130521  7:15:44 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (7ff38373-c1d1-11e2-0800-d595d1800e6a): 1 (Operation not permitted)
     at galera/src/replicator_str.cpp:prepare_for_IST():436. IST will be unavailable.
130521  7:15:44 [Note] WSREP: Node 0 (db-02.arqwifi) requested state transfer from '*any*'. Selected 1 (db-01.arqwifi)(SYNCED) as donor.
130521  7:15:44 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 7101)
130521  7:15:44 [Note] WSREP: Requesting state transfer: success, donor: 1

完整的错误日志：

以防万一，输出

ls -la /var/mysql/
ls -la /var/log/mysql/
ls -la /var/lib/mysql/
ls -la /var/run/mysqld/
ls -la /etc/my.cnf

SELINUX=disabled在两台服务器上。

主持人

[root@db-01 gajus]# xtrabackup --print-defaults
xtrabackup would have been started with the following arguments:
--character_set_server=utf8 --collation_server=utf8_unicode_ci --skip-name-resolve --innodb_file_per_table --log_slave_updates --server_id=155150 --wsrep_provider=/usr/lib64/libgalera_smm.so --wsrep_node_address=10.128.155.150 --wsrep_cluster_address=gcomm:// --wsrep_sst_method=xtrabackup --wsrep_cluster_name=live_cluster --wsrep_sst_auth=percona:[removed] --wsrep_slave_threads=1 --log_bin=/var/log/mysql/mysql-bin.log --binlog_format=ROW --default_storage_engine=InnoDB --innodb_locks_unsafe_for_binlog=1 --innodb_autoinc_lock_mode=2 --innodb_buffer_pool_size=4G --innodb_log_file_size=1G --query_cache_type=0 --query_cache_size=0 --join_buffer_size=4M --max_connections=1000 --table_cache=2000 --table_definition_cache=2000 --key_buffer_size=50M --sort_buffer_size=10M --read_rnd_buffer_size=1M --read_buffer_size=4M --tmp_table_size=500M --max_heap_table_size=1G --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql --user=mysql --symbolic_links=0 --default_time_zone=+00:00 --max_allowed_packet=128MB

细木工

[root@db-02 gajus]# xtrabackup --print-defaults
xtrabackup would have been started with the following arguments:
--character_set_server=utf8 --collation_server=utf8_unicode_ci --skip-name-resolve --innodb_file_per_table --log_slave_updates --server_id=155151 --wsrep_provider=/usr/lib64/libgalera_smm.so --wsrep_node_address=10.128.155.151 --wsrep_cluster_address=gcomm://10.128.155.150 --wsrep_sst_method=xtrabackup --wsrep_cluster_name=live_cluster --wsrep_sst_auth=percona:[removed] --wsrep_slave_threads=1 --log_bin=/var/log/mysql/mysql-bin.log --binlog_format=ROW --default_storage_engine=InnoDB --innodb_locks_unsafe_for_binlog=1 --innodb_autoinc_lock_mode=2 --innodb_buffer_pool_size=4G --innodb_log_file_size=1G --query_cache_type=0 --query_cache_size=0 --join_buffer_size=4M --max_connections=1000 --table_cache=2000 --table_definition_cache=2000 --key_buffer_size=50M --sort_buffer_size=10M --read_rnd_buffer_size=1M --read_buffer_size=4M --tmp_table_size=500M --max_heap_table_size=1G --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql --user=mysql --symbolic_links=0 --default_time_zone=+00:00 --max_allowed_packet=128MB
mysql replication