主页 / user-108666

Zach Smith's questions

Martin Hope
Zach Smith
Asked: 2023-02-23 15:44:18 +0800 CST
  • 5

为了测试删除查询是否按预期工作，我想运行一个简单的选择查询，但查询只是挂起并且永远不会完成。看起来pg_stat_activity查询正在运行，并且在和 其他状态wait_event之间发生变化（包括，令人惊讶的是，）。DataFileReadNULLWalWrite

解释的查询：

explain select *
from public.some_table
where foreign_id = 99
limit 1

显示预期的查询计划：

Limit  (cost=0.58..4.51 rows=1 width=45)
  ->  Index Scan using idx_unique_cols on "some_table"  (cost=0.58..32.04 rows=8 width=45)
        Index Cond: (foreign_id = 99)

idx_unique_cols有这个 DDL：

CREATE UNIQUE INDEX idx_unique_cols ON public."some_table" USING btree (foreign_id DESC, col1, col2 DESC, foreign_id_2);

该表相当大 - 大约 380GB，我预计每个foreign_id. 我期待一个相当快速的索引扫描来确定没有 a 为foreign_id99 的行。但这显然没有发生，我不知道为什么查询会花费这么长时间。

问题：

  1. 为什么这个查询需要等待一个WalWrite事件（我假设这意味着选择查询，出于某种原因，需要写入预写日志）
  2. 查看索引，为什么要花这么长时间才能确定索引中的值不存在？
  3. 对我知道的值运行相同的查询foreign_key比some_table立即返回中存在的值高得多。为什么对低于表中现有值的值运行查询需要这么长时间才能返回？
postgresql
Martin Hope
Zach Smith
Asked: 2022-11-01 04:40:17 +0800 CST
  • 5

我有一个 PostgreSQL 表，它在 4 列上定义了一个唯一约束：

create table public.values (
  id bigint primary key generated always as identity,
  a smallint not null references .... (id),
  b smallint not null,
  c smallint not null,
  d int not null references .... (id),
  -- ... some other cols
  constraint unique_cols unique (a, b, c, d)
);

create index values_d on public.values using btree (dasc);

这张表有很多行（它是 +/- 400GB 并且每天增长 8GB）——我认为此时它大约有 25 亿行。我想删除通过 column 标识的行a，但除非我在 where 子句中指定b或指定，否则c查询计划表明将执行顺序扫描public.values。

即这个查询：

-- ... After running `analyze` on the tables that are used

explain delete from public.values where a = 2

显示查询计划（具有 60 个唯一a值）：

Delete on "values"  (cost=0.00..62375313.80 rows=0 width=0)
  ->  Seq Scan on "values"  (cost=0.00..62375313.80 rows=48160325 width=6)
        Filter: (a = 2)
JIT:
  Functions: 3
  Options: Inlining true, Optimization true, Expressions true, Deforming true

这对我来说毫无意义——我不明白为什么不使用唯一约束索引（我希望这个查询会删除 5000 万行）。

如果我将查询更新为指定b，那么我可以获得查询计划以指示将完成索引扫描：

explain delete from public.values where a = 2 and b = 1

显示此查询计划：

Delete on "values"  (cost=0.58..4845719.33 rows=0 width=0)
  ->  Index Scan using values_unique_cols on "values"  (cost=0.58..4845719.33 rows=1940861 width=6)
        Index Cond: ((a = 2) AND (b = 1))
JIT:
  Functions: 3
  Options: Inlining true, Optimization true, Expressions true, Deforming true

然后，如果我将删除查询b子句“where”条件更改为greater than条件：

explain delete from public.values where a = 2 and b >= 1

...回到顺序扫描。

但是再次更改子句：

explain delete from public.values where a = 2 and b  >= 7

并且计划更好（b lower than 6导致 seq 扫描 - b 是 1 到 20 之间的值）：

Delete on "values"  (cost=0.58..65344628.48 rows=0 width=0)
  ->  Index Scan using values_unique_cols on "values"  (cost=0.58..65344628.48 rows=36595426 width=6)
        Index Cond: ((a = 2) AND (b >= 7))
JIT:
  Functions: 3
  Options: Inlining true, Optimization true, Expressions true, Deforming true

如何强制 PostgreSQL 为特定操作使用索引，或者快速删除大量行？

我可以从应用程序多次运行查询，每次都使用一个新b值，但我更愿意进行一次数据库调用。

== 编辑

随后发布此问题，我在 上创建了一个索引a，并重新分析了该表。但是规划器仍然指示一个seq。扫描。

postgresql
Martin Hope
Zach Smith
Asked: 2021-02-02 03:18:55 +0800 CST
  • 1

使用 PostgreSQL，我看到该pg_dump命令（可能还有其他命令）需要访问“序列”。我理解这些只是行识别所必需的增量 ID...

为什么必须授予服务器角色访问序列对象的权限？如果每个角色都可以访问所有可访问模式的序列，那么潜在的安全问题是什么？

postgresql
Martin Hope
Zach Smith
Asked: 2020-08-25 01:18:32 +0800 CST
  • 0

查看 MongoDB 文档，我发现不鼓励用户使用 MapReduce 来支持聚合管道。

例如，在文档中的这个页面上。

但就 Aggregation Framework 是否完全取代 MapReduce 而言，同样的通知是模棱两可的：

可以使用聚合管道运算符重写各种 map-reduce 操作，例如 $group、$merge 等。对于需要自定义功能的 map-reduce 操作，MongoDB 从版本 4.4 开始提供 $accumulator 和 $function 聚合运算符。

这是否意味着“所有可能的” MapReduce 配置都可以通过聚合框架复制？

我知道 MapReduce 执行上下文可能比聚合管道慢，因为前者需要一个 JavaScript 引擎，它是/曾经是/仍然是？比聚合管道实现慢。

deprecated与其他功能不同，据我所知，MapReduce 尚未被标记。所以，我的问题是：

聚合框架是否完全取代了 MapReduce（针对每个可能的用例）？或者是否存在 MapReduce 仍然提供更大灵活性的用例？

mongodb
Martin Hope
Zach Smith
Asked: 2019-09-19 00:57:56 +0800 CST
  • 0

我正在处理表单中的点数据(p1, p2)，它在文本中表示为((x, y), (x, y))。我想从这个字符串中得到 4 个值（x1, x2, y1, y2），但是我在使用数组索引时遇到了麻烦。

例如：

(1) 得到一个point[]代表 2 个点的

select concat('{', replace(replace('(64,38),(-26,-40)', '(', '"('), ')', ')"'), '}')::point[];

-- This correctly gives me {(64.0,38.0),(-26.0,-40.0)}

(2) 从该数组中获取第一个点

select ( select concat('{', replace(replace('(64,38),(-26,-40)', '(', '"('), ')', ')"'), '}')::point[] )[1]

-- this gives me (64.0,38.0) (i.e. index = 1 gives me the FIRST point)

(3) 获取第一个点的x值

select ( select ( select concat('{', replace(replace('(64,38),(-26,-40)', '(', '"('), ')', ')"'), '}')::point[] )[1] )[0]

-- This gives me 64 (i.e. index = 0 gives me the FIRST point)

似乎在二维数组中，索引从 1 开始。但在一维数组中，索引从 0 开始。这怎么可能？？？？

我已经对此进行了Dbeaver单独测试，PG Admin 4并且得到了相同的结果。

postgresql
Martin Hope
Zach Smith
Asked: 2019-08-29 05:49:20 +0800 CST
  • 5

如果我有一张桌子A，像这样：

A {
 id SERIAL
 title TEXT
 ...
 parentId INT references A.id via a foreign key constraint
}

我正在从A_SOURCE没有parentId列的源表中提取数据。取而代之的是一parentTitle列。所以源表看起来像这样：

A_SOURCE {
  title TEXT
  parentTitle TEXT
}

在我意识到我无法轻松地将源中的列解析为目标中的列之前，我开始编写一个 upsert 语句以A通过从表中选择插入到表中。A_SOURCEparentTitleparentId

由于我无法确定在处理子项时是否已插入父项，因此连接或子查询可能不会返回任何结果。

我的 upsert 声明看起来像这样：

with source as (
  select
   title
   parentTitle
  from A_SOURCE 
)

insert into A
select
title
... I don't think I can resolve to parentId here?
from source
on concflict ...;

我知道我可以运行两个单独的语句：

  1. 插入null作为 parentId
  2. 然后更新第二条语句中的 parentId 字段

但是可以在单个查询中执行此操作吗？

postgresql upsert
Martin Hope
Zach Smith
Asked: 2019-07-25 05:56:25 +0800 CST
  • 0

我正在寻找一种从文本值转换的方法：

'(180,90),(-180,-90)'

到一个点[]值：

{"(180,90)","(-180,-90)"}

做这个的最好方式是什么？

我能想到的一种可能性是将字符串操作为这种形式：

'{"(180, 90)", "(-180,-90)"}'

然后我可以point[]这样转换：

select '{"(180, 90)", "(-180,-90)"}'::point[];

肯定有更好的方法吗？我目前的解决方案是这样的：

select concat('{', replace(replace(<text>, '(', '"('), ')', ')"'), '}')::point[]
postgresql cast
Martin Hope
Zach Smith
Asked: 2019-07-25 04:40:32 +0800 CST
  • 1

使用 PostgreSQL，我可以轻松地执行一个 select 语句来返回一个 datatype 数组的列point。即colA是数据类型point[]

select colA from tbl

返回包含点数组的列：

+-----------------------------+
|            colA             |
+-----------------------------+
| (180.0,90.0),(-180.0,-90.0) |
| (64.0,38.0),(-26.0,-40.0)   |
| etc                         |
+-----------------------------+

我确定该列是 type point[]，因为select colA[0]按预期返回。

我正在尝试从另一台服务器使用DBLINK(). 这是查询：

select
colA
from dblink(
  'my_conn',
  'select
   colA
   from public.tbl'
) as s (
  colA point[]
)

此查询失败并显示以下错误消息：

SQL 错误 [22P02]：错误：格式错误的数组文字：“（180,90），（-180，-90）”详细信息：数组值必须以“{”或维度信息开头。

我测试了我能够point使用DBLINK. 即这个查询成功：

select
colA
from dblink(
  'my_conn',
  'select
   colA[0]
   from public.tbl'
) as s (
  colA point
)

所以，我的问题是如何通过 DBLINK 函数选择数组？.

postgresql dblink
Martin Hope
Zach Smith
Asked: 2019-07-17 05:38:07 +0800 CST
  • 2

我有一个文件db.backup，我想恢复到 PostgreSQL 版本 11。

当我尝试通过 Azure Data Studio 安装时，出现错误：

恢复：pg_restore: [archiver] 文件头中不支持的版本 (1.13)

然后我发现运行pg_restore我实际上可以读取文件：

pg_restore /..db.backup -U postgres > db-restore.sql

我可以看到该文件是人类可读的。但是我更愿意从备份中恢复数据库。

我怎样才能做到这一点？我尝试安装旧版本的 PostgreSQL 服务器，但我不知道如何安装旧版本的 PSQL（还）。这会有帮助吗？

是否有一些标志可以添加到恢复命令中以便恢复工作？

- 编辑

的输出pg_restore --version，即那个版本pg_restore给了我上面提到的错误信息：

pg_restore (PostgreSQL) 11.4 (Ubuntu 11.4-1.pgdg18.04+1)

postgresql pg-restore
Martin Hope
Zach Smith
Asked: 2018-09-06 04:11:35 +0800 CST
  • 3

我有一个列类型为text. 来自SQL Server背景，这是否等同于(n)varchar(max)类型？

我提出这个问题的具体示例/原因是，我有一个表，其中包含一个类型的列text，我想在其中存储唯一值。该表根据常规 CSV 导入进行更新，这意味着对于 CSV 中的每一行，text都会检查该列是否存在现有条目（CSV 中的列值），如果没有找到，则将该值插入到表中.

我的理解是，这可能意味着将数千个（或数十万个）text值与其他text值进行比较。我想这是非常无能的。是这样吗？

postgresql
Martin Hope
Zach Smith
Asked: 2018-06-06 01:09:39 +0800 CST
  • 1

我正在尝试在存储过程中设置输出参数，但我认为当我回滚事务时，我认为我也在将分配回滚到@out. 那是对的吗？

如果是这样，我如何返回消息并回滚事务？我正在@out从 C# 获取参数。

create PROCEDURE [dbo].[sp]
@out varchar(2000) output
AS
BEGIN
    SET NOCOUNT ON
    BEGIN TRANSACTION
    BEGIN TRY
        SET @OUT = "success";
        COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
        set @out = 'not success';
        ROLLBACK TRANSACTION
    END CATCH
END

我最初是在做一个

SELECT 
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;

...但这并没有帮助，尽管我更喜欢这种方法。

sql-server stored-procedures
Martin Hope
Zach Smith
Asked: 2018-03-23 04:03:05 +0800 CST
  • 12

我发现如果我将 SQL Server 代理作业编写为脚本开头的“创建到” use msdb- 所以我假设作业存储在 msdb 数据库中？

将 SQL Server 作业添加到 Visual Studio 数据库项目的最佳方法是什么？我将 msdb 数据库添加到数据库解决方案中，但这似乎对作业没有任何参考。

我希望将作业与数据库一起部署/更新......这似乎是不可能的？

sql-server sql-server-agent
Martin Hope
Zach Smith
Asked: 2018-02-27 08:47:50 +0800 CST
  • 6

在 SQL Server 2016 中，我可以按照文档Format Query Results as JSON with FOR JSON (SQL Server)JSON中的描述输出查询结果

SELECT * 
FROM x 
FOR JSON PATH

但我发现这在 SQL Server 2012 中不受支持。

JSONSQL Server 2012中是否有将表格数据转换为数据的内置方法？

为了使这种交叉兼容，我希望能够for json path在 SQL Server 2012 中使用。

sql-server sql-server-2012
Martin Hope
Zach Smith
Asked: 2018-02-15 01:34:10 +0800 CST
  • 2

SSDT 如何处理Data Flow任务的批处理？

我正在使用 SSDT 将大约 1GB 的 CSV 解析到 SQL Server 中。

在数据流中有一个Flat File源，它转到 OLE DB 目标（临时表）。然后使用 SQL 任务执行 SP。

正在解析的 CSV 包含一个汇总表和一个带有对该汇总表的外键引用的子表。因此，CSV 包含汇总表的重复 ID（因此每个子行有一行）。如果要将单个摘要 ID 拆分为两个批次，那么我会丢失数据 - SP 会执行类似“从暂存 ID 的子表中删除，然后从暂存中重新插入到子表中”之类的操作。以前我们必须这样做，因为供应商没有为子数据导出唯一 ID。他们现在，所以我可以使用合并语句。

但。我仍然想知道Data Flow任务是否是批处理的，如果是，如何处理？

ssdt data-flow
Martin Hope
Zach Smith
Asked: 2017-12-30 06:59:47 +0800 CST
  • 0

可以使用自引用 CTE 和 union all 在 SQL Server 中模拟循环，如下所示：

declare @a int = 1;
declare @b int = 5;

;with Tbl as (
    select @a a
    UNION ALL
    select (a + 1) a
    from Tbl
    where (a + 1) < @b
)
select * from Tbl

当您想要创建从开始日期到结束日期的日期列表然后在这些特定日期加入时，这很有用。

我也可以想象也想对列这样做。有没有一种方法可以在不使用动态 SQL 的情况下在 SQL Server 中实现这一点？我可能希望将这样的查询保存为视图，而不是 SP（尽管我知道这可以通过动态 SQL 来实现）

sql-server sql-server-2016
Martin Hope
Zach Smith
Asked: 2017-12-19 07:58:34 +0800 CST
  • 1

有没有一种方法可以替换 SQL Server 中的子字符串，同时保持“了解”我要替换的内容，以便我可以重新插入该内容？

我正在构建一串列名以进行透视，并且这些列名需要在查询中聚合。

例如，我将此列列表作为 nvarchar(max) 字符串：

[A], [B], [C]

我想将此字符串转换为如下所示：

sum([A]) [A], sum([B]) [B], sum([C]) [C],

我刚刚找到了这个stuff功能，但我还不知道如何实现我想要的。

但我觉得我可以使用它来插入子字符串，而不是使用替换子字符串。

查看 stuff 功能：

STUFF( source_string, start, length, add_string )

如何一次插入多个位置？

我目前的解决方案是这样的：

declare @cols nvarchar(max);
declare @colsAggregate nvarchar(max);
declare @sql nvarchar(max);

select @cols = stuff((
    select distinct ',' + quotename(sCode)
    from EntityList EL
    where EL.bActive = 1 for xml path(''), type).value('.', 'nvarchar(max)'),
1, 1, '');

select @colsAggregate = stuff((
    select distinct ', max(' + quotename(sCode) + ') ' + quotename(sCode)
    from EntityList EL
    where EL.bActive = 1 for xml path(''), type).value('.', 'nvarchar(max)'),
1, 1, '');
sql-server t-sql
Martin Hope
Zach Smith
Asked: 2017-12-07 05:14:05 +0800 CST
  • 10

将表格“展平”成单行的最佳方法是什么？

例如，使用下表：

+-----+-------+-------------+------------------+
| Id  | hProp | iDayOfMonth | dblTargetPercent |
+-----+-------+-------------+------------------+
| 117 |    10 |           5 |           0.1400 |
| 118 |    10 |          10 |           0.0500 |
| 119 |    10 |          15 |           0.0100 |
| 120 |    10 |          20 |           0.0100 |
+-----+-------+-------------+------------------+

我想制作下表：

+-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+
| hProp | iDateTarget1 | dblPercentTarget1 | iDateTarget2 | dblPercentTarget2 | iDateTarget3 | dblPercentTarget3 | iDateTarget4 | dblPercentTarget4 |
+-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+
|    10 |            5 |              0.14 |           10 |              0.05 |           15 |              0.01 |           20 |              0.01 |
+-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+

我已经设法使用枢轴来做到这一点，然后多次重新加入原始表，但我相当确定有更好的方法。这按预期工作：

select
X0.hProp,
X0.iDateTarget1,
X1.dblTargetPercent [dblPercentTarget1],
X0.iDateTarget2,
X2.dblTargetPercent [dblPercentTarget2],
X0.iDateTarget3,
X3.dblTargetPercent [dblPercentTarget3],
X0.iDateTarget4,
X4.dblTargetPercent [dblPercentTarget4]
from (
    select
        hProp,
        max([1]) [iDateTarget1],
        max([2]) [iDateTarget2],
        max([3]) [iDateTarget3],
        max([4]) [iDateTarget4]
    from (
        select
            *,
            rank() over (partition by hProp order by iWeek) rank#
        from [Table X]
    ) T
    pivot (max(iWeek) for rank# in ([1],[2],[3], [4])) pv
    group by hProp
) X0
left join [Table X] X1 on X1.hprop = X0.hProp and X1.iWeek = X0.iDateTarget1
left join [Table X] X2 on X2.hprop = X0.hProp and X2.iWeek = X0.iDateTarget2
left join [Table X] X3 on X3.hprop = X0.hProp and X3.iWeek = X0.iDateTarget3
left join [Table X] X4 on X4.hprop = X0.hProp and X4.iWeek = X0.iDateTarget4
sql-server t-sql
Martin Hope
Zach Smith
Asked: 2017-11-15 02:05:37 +0800 CST
  • 1

如果我有一个查询：

SELECT * FROM A
INNER JOIN B ON B.ID = A.ID
INNER JOIN C ON C.ID = B.ID

这会为此查询返回不同的结果吗？

SELECT * FROM A
INNER JOIN B ON B.ID = A.ID
INNER JOIN C ON C.ID = A.ID

在我看来，在第一个查询中我会得到 A、B、C 的交集。在第二个查询中，我将得到 A、B 的交集以及 A、C 的交集。

如果是这种情况，我会在 B、C 之间没有交集的 B/C 列中找到 NULL 值吗？

join