主页 / dba / 问题

问题[update](dba)

Martin Hope
ConanTheGerbil
Asked: 2023-04-11 15:28:34 +0800 CST
  • 5

我有一些简单的 SQL 来重置表中的一些值

UPDATE tableA SET field1 = 0
WHERE field2 IN (SELECT .......)

这工作正常，但我想修改它以返回所有field1值在重置为零之前的值。这是我试过的......

UPDATE tableA SET field1 = 0
WHERE field2 IN (SELECT .......)
RETURNING field1

要注意的是，这仅在修改后返回 field1 的值。是否可以事先获得价值？不运行单独的查询？

update
Martin Hope
Valentin Nasraty
Asked: 2022-10-12 08:04:37 +0800 CST
  • 4

我正在使用具有以下数据库架构的 PostgreSQL：

CREATE TABLE plans (
    slug VARCHAR(500) PRIMARY KEY
);

CREATE TABLE users (
    id VARCHAR(16) PRIMARY KEY,
    org_id VARCHAR(16) NOT NULL
);

CREATE TABLE orgs (
    id VARCHAR(16) PRIMARY KEY,
    plan_slug VARCHAR(500) NOT NULL,
    last_write_at DOUBLE PRECISION
);

就我而言，我想编写一个查询来更新一些组织plan_slug并保护它免受其他可能的并发更新。为此，我SELECT在子查询中使用 a 和 aFOR UPDATE以特定顺序锁定行以避免死锁。像下面的查询：

UPDATE orgs
SET plan_slug = 'plan_1'
WHERE id = ANY(
    SELECT subquery_orgs.id
    FROM orgs AS subquery_orgs
    JOIN users ON users.org_id = subquery_orgs.id
    WHERE users.id = ANY('{user_1, user_2, user_3}')
    ORDER BY subquery_orgs.id
    FOR UPDATE
);

我注意到，如果此请求需要很长时间才能运行，则很有可能与另一个尝试更新last_write_at组织的查询发生冲突（一个已被第一个查询更新的组织）。

如下：

UPDATE orgs 
SET last_write_at = 999 
FROM plans 
WHERE orgs.id = 'org_1';

查询将成功。但是如果我在查询plan_slug的WHERE子句中添加，它总是无法更新。Postgre 返回UPDATE 0.

查询如下：

UPDATE orgs 
SET last_write_at = 999 
FROM plans 
WHERE orgs.id = 'org_1' 
AND plans.slug = orgs.plan_slug;

据我了解，该过程应该是：

  1. 第一个查询执行。该plan_slug行已锁定。
  2. 第二个查询执行。它停止执行并等待行解锁。
  3. 第一个查询完成其更新。
  4. 第二个查询重新启动并重新评估更改。
  5. plan_slug已更改，但表中存在新的，plans第二个查询应该成功。

plan_slug那么为什么当我添加到WHERE子句时查询无法更新？

postgresql update
Martin Hope
WebViewer
Asked: 2022-10-03 00:44:11 +0800 CST
  • 6

我继承了一个系统，其中对 MySQL 表的所有更新（甚至是单行/记录）都不是使用UPDATE table_name SET. 相反，它们是通过以下方式完成的：

  1. 将现有表导出到 CSV（文本）文件。
  2. 修改 CSV 文件中的相应行。
  3. 使用 重新加载 CSV 文件LOAD DATA ... REPLACE INTO TABLE。

这是我第一次看到这种更新表记录的方法，我想知道这样做的合理性是什么。

顺便说一句，由于更新时需要锁定 CSV 文件，这种方案会导致大量线程同步问题。

我将不胜感激有关使用LOAD DATA ... REPLACE INTO TABLE而不是UPDATE table_name SET.

mysql update
Martin Hope
Fajela Tajkiya
Asked: 2022-09-23 06:15:04 +0800 CST
  • 5

我正在学习 SQL Server 并试图了解 SQL Server 如何更新一行。

据我了解，首先 SQL Server在数据库上放置一个意图排他锁，然后在表上放置一个意图排他锁，然后在要更新的记录上放置一个更新锁。稍后，它会将行上的更新锁转换为排他锁，以便修改行中的数据。

但问题是共享锁与更新锁兼容。因此，虽然该行上有一个更新锁，但其他会话仍然可以使用共享锁读取该行。使用行上的共享锁，不可能将更新锁转换为排他锁，因为共享锁和排他锁不兼容。

这是否意味着如果有会话连续读取该行，SQL Server 根本没有机会更新该行。这可能会持续很长时间。这是真的？SQL Server 会等到行上没有共享锁后才开始更新其中的数据吗？

“连续”是指一个会话读取该行，并且在从该行释放共享锁之前，另一个会话在同一行上放置一个共享锁。在第二个共享锁从行中释放之前，另一个会话在同一行上放置另一个共享锁，依此类推。因此，基本上，该行上总是至少有一个共享锁。

sql-server update
Martin Hope
sharknado
Asked: 2022-08-17 08:54:06 +0800 CST
  • 1

我有一个tbl具有以下结构的表：

CREATE TABLE tbl (
  org  text
, data jsonb
);

字段中的数据jsonb是一个数组，结构如下：

INSERT INTO tbl VALUES
('SOMETHING'
, '[
      {
        "type": "XYZ",
        "valueA": "500",
        "valueB": "ABC"
      },
      {
        "type": "ABC",
        "valueA": "300",
        "valueB": "CDE"
      }
   ]')
;

我想将一个键添加到对象具有值为 的键valueC的元素中。data"type""XYZ"

valueC的值将是一个字符串数组。数组的值将取决于org列的值。

我想对所有行执行此操作，如果存在特定行org，并且列中的jsonb数组data包含带有 的对象"type": "XYZ"，那么我会得到以下结果：

[
  {
    "type": "XYZ",
    "valueA": "500",
    "valueB": "ABC",
    "valueC": ["SOMETHING"],
  },
  {
    "type": "ABC",
    "valueA": "300",
    "valueB": "CDE",
  }
]

我还想确保此脚本仅valueC在与条件匹配的对象中不存在时才运行，因此除非需要，否则在迁移/回滚期间不会重新运行。

这是我到目前为止所拥有的，但是当它没有找到子查询的结果并且我无法弄清楚如何仅在valueC不存在的情况下运行它时它不起作用：

UPDATE tbl SET  
  data = jsonb_set(
    data, 
    '{data}', 
   (SELECT jsonb_agg(elem ||'{"valueC":["SOMETHING"]}') FROM jsonb_array_elements(data->'data') as elem where elem ->> 'type' = 'XYZ')
  )
WHERE org = 'SOMETHING';
postgresql update
Martin Hope
Francesco Mantovani
Asked: 2022-07-13 02:28:37 +0800 CST
  • 1

SQL Server 仍在顺利运行，但我注意到我在 Windows 更新上有一条警报，上面写着：

Servicing Update for SQL Server 2019 RTM GDR (KB4517790) 
Install error - 0x80070643

在此处输入图像描述

我尝试重新启动和更新服务器，但它仍然没有安装。

所以我下载了KB4517790，但是当我尝试安装服务时，出现错误：

There are no SQL Server instances or shared features that can be updated on this computer.

在此处输入图像描述

奇怪的。

我单击返回两次，然后检查查看详细报告，一切都很好：

Microsoft SQL Server 2019 - System Configuration Check Report
规则名称 规则说明 结果 消息/纠正措施
PatchGlobalRules：SQL Server 2019 安装程序配置检查规则组“PatchGlobalRules”
# Engine_ResourceDLLUpdateRestartCheck 检查是否将更新共享资源 DLL，从而导致此节点上活动的群集 SQL Server 实例重新启动。 不适用 此规则不适用于您的系统配置。
# AclPermissionsFacet 检查 SQL Server 注册表项是否一致。 通过 SQL Server 注册表项是一致的，可以支持 SQL Server 安装或升级。
# Cluster_IsLocalNodeGroupOwner 检查本地计算机是否是包含 SQL Server 服务、Analysis Services 服务或通用服务的故障转移群集实例的联机群集组的所有者。 通过 本地计算机不是包含 SQL Server 服务、Analysis Services 服务或通用服务的故障转移群集实例的联机群集组的所有者。SQL Server 实例不需要脱机。
# Cluster_IsOnlineIfClustered 验证计算机未集群或计算机已集群且集群服务处于联机状态。 通过 计算机未建立群集或群集已启动并联机。
# 需要重启检查 检查是否需要挂起的计算机重新启动。挂起的重新启动可能会导致安装程序失败。 通过 计算机不需要重新启动。
# ThreadHasAdminPrivilegeCheck 检查运行 SQL Server 安装程序的帐户是否在计算机上具有管理员权限。 通过 运行 SQL Server 安装程序的帐户在计算机上具有管理员权限。
# WmiServiceStateCheck 检查 WMI 服务是否已在计算机上启动和运行。 通过 Windows Management Instrumentation (WMI) 服务正在运行。

所以我在 PowerShell 中找到了一个很棒的命令，可以让我恢复所有 Windows 更新日志。

我键入Get-WindowsUpdateLog，一个名为 WindowsUpdate.log 的文件现在在我的桌面上。

当我打开它时，我发现：

2022/07/12 11:35:06.5502777 14516 14848 ComApi          Byte length of the input buffer for deserialization: 36024
2022/07/12 11:35:06.5518950 14516 14848 ComApi          Deserialized installable update Servicing Update for SQL Server 2019 RTM GDR (KB4517790), UpdateID = {CA10020A-BECD-4535-B06D-E30E92F01AD6.200}
2022/07/12 11:35:06.5521232 14516 14848 Test            AUTest.cab validation: Test keys are not allowed
2022/07/12 11:35:06.6192658 14516 8864  ComApi          Serializing CUpdate CA10020A-BECD-4535-B06D-E30E92F01AD6.200, Last modified time 2022-07-12T09:25:10Z
2022/07/12 11:35:06.6198416 14516 8864  ComApi          Update serialization complete. BSTR byte length = 36024
2022/07/12 11:35:21.4414511 21868 24160 Agent           Earliest future timer found: 
2022/07/12 11:35:21.4414806 21868 24160 Agent               Timer: 29A863E7-8609-4D1E-B7CD-5668F857F1DB, Expires 2022-07-12 12:48:15, not idle-only, not network-only
2022/07/12 11:35:22.4513120 21868 25304 Shared          UninitializeSUS
2022/07/12 11:35:22.4513184 21868 25304 Misc            CSusClientGlobal::DoServicePreShutdown
2022/07/12 11:35:22.4517025 21868 25304 IdleTimer       Idle timer disabled in preparation for service shutdown
2022/07/12 11:35:22.4517139 21868 25304 Misc            WUTaskManager uninit
2022/07/12 11:35:22.4517196 21868 25304 Agent           Earliest future timer found: 
2022/07/12 11:35:22.4517386 21868 25304 Agent               Timer: 7971F918-A847-4430-9279-4A52D1EFE18D, Expires 2022-07-12 10:37:14, not idle-only, network-only
2022/07/12 11:35:22.4683034 21868 25304 Misc            CreateSessionStateChangeTrigger, TYPE:2, Enable:No
2022/07/12 11:35:22.4683300 21868 25304 Misc            CreateSessionStateChangeTrigger, TYPE:4, Enable:No
2022/07/12 11:35:22.4916515 21868 25304 Misc            Agent uninit
2022/07/12 11:35:22.4916711 21868 25304 Agent           *FAILED* [8007139F] file = onecore\enduser\windowsupdate\client\engine\handler\cbs\lib\uhcbs.cpp, line = 4848
2022/07/12 11:35:22.4916832 21868 25304 DownloadManager Exiting CDownloadHandlerCallbackHandler::Uninit within timeout bounds
2022/07/12 11:35:22.4925943 21868 18656 Agent           Deployment session (host process 21868) signalled to exit...
2022/07/12 11:35:22.4926590 21868 18656 Deployment      Deployment job Id 3A2949A0-E56C-4FA1-AB01-8282F1D469DA : Destructor invoked for deployment job.
2022/07/12 11:35:22.4926639 21868 18656 Deployment      Deployment job Id 3A2949A0-E56C-4FA1-AB01-8282F1D469DA : WaitForEnd invoked for deployment job. Beginning the wait now!
2022/07/12 11:35:22.4926702 21868 18656 Deployment      Deployment job Id 3A2949A0-E56C-4FA1-AB01-8282F1D469DA : WaitForEnd for deployment job completed. Job state = Complete
2022/07/12 11:35:22.4942066 21868 25304 Misc            Reporter uninit
2022/07/12 11:35:22.4943410 21868 25304 Misc            network cost manager uninit
2022/07/12 11:35:22.4943651 21868 25304 Misc            Eventer uninit
2022/07/12 11:35:23.5095262 21868 25304 Misc            ServiceManager uninit
2022/07/12 11:35:23.5098089 21868 25304 Misc            PersistentTimeoutScheduler uninit
2022/07/12 11:35:23.5098136 21868 25304 Misc            datastore uninit
2022/07/12 11:35:23.5630608 21868 25304 Misc            setting cache uninit
2022/07/12 11:35:23.5630619 21868 25304 Misc            security checker uninit
2022/07/12 11:35:23.5630660 21868 25304 Misc            Test Hook uninit
2022/07/12 11:35:23.5630667 21868 25304 Misc            IdleTimer uninit
2022/07/12 11:35:23.5635012 21868 25304 Shared          * END * Service exit Exit code = 0x240001

我看到有东西掉了下来，但我不知道如何移动。

我已经尝试过 SQL Server 修复，但没有帮助。

我在最新版本：

Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64)   May 29 2022 15:55:47   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 22000: ) (Hypervisor)
sql-server update
Martin Hope
Alvin
Asked: 2022-07-09 16:36:27 +0800 CST
  • 1

对于同一操作，这两种形式之间的批量更新通常哪个更有效？

选项1：

UPDATE table SET a = val.b FROM (VALUES (0,1), (2,3)) AS val(b,id) WHERE table.id = val.id

选项 2：

UPDATE table SET a = 0 WHERE table.id = 1;
UPDATE table SET a = 2 WHERE table.id = 3;

我在文档中找到了这个：

当出现 FROM 子句时，实质上发生的情况是目标表连接到 from_item 列表中提到的表，连接的每个输出行代表目标表的更新操作。

这是否意味着这两个选项在引擎盖下是等效的？

对于选项 1，我想知道 Postgres 是否只会在最后更新一次索引，在所有行都更新之后，或者每行更新一次。前者应该比后者更有效，这将使选项 1 比选项 2 更好。

postgresql update
Martin Hope
g00dds
Asked: 2022-06-27 10:53:36 +0800 CST
  • 0

我有一个大型数据库和一个文件，每行都有一个 ID-Name 对，其中 ID 对应于数据库中表的主键。我需要使用相应的 ID 将文件中的所有名称写入数据库。当然，如果表中有这样的ID（如果没有该ID的用户，则不要插入新记录，只需更新现有的）。

为此，我编写了一个简单的 python 脚本。我意识到它工作得非常慢。比 UPDATE （甚至不是 INSERT）要慢得多。ID 是一个主键，所以它必须被自动索引并且它不应该减慢更新，对吧？

然后我开始试验该脚本，发现当 ID 为常数或递增时，UPDATE 的速度要快得多。实验脚本如下：

import sqlite3
import traceback
import time
import random

conn = sqlite3.connect('database.sqlite')

c = conn.cursor()
c.execute('BEGIN')

PERIOD_SEC = 3

prev_tp = 0
counter = 0

try:
    while True:
        tp = time.time()
        elapsed_sec = tp - prev_tp
        if elapsed_sec > PERIOD_SEC:
            prev_tp = tp

            speed = counter / elapsed_sec

            print('Speed: %.02f/sec' % (speed))
            counter = 0

        c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', 555))
        counter += 1


    c.execute('COMMIT')
except Exception:
    c.execute('ROLLBACK')
    traceback.print_exc()

的值username不会影响性能，所以让它一直是random。在上面的示例中，ID 是恒定的并且等于555（该值无关紧要，对于较大的 ID 值，速度也相同）。对于此示例，速度为（每秒更新数）：

Speed: 376665.88/sec
Speed: 404738.08/sec
Speed: 404942.04/sec
Speed: 403681.24/sec

如果我更换

c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', 555))

和

c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', inc))

whereinc在每次 UPDATE 后增加 1，那么结果将是：

Speed: 233977.17/sec
Speed: 229630.93/sec
Speed: 223424.88/sec
Speed: 218353.56/sec

情况更糟，但还没有那么糟。

但是..如果我使用随机 ID

c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', random.randint(0, 1000000000)))

然后结果变得令人失望：

Speed: 514.18/sec
Speed: 732.49/sec
Speed: 886.28/sec
Speed: 999.34/sec

慢了100多倍！我还注意到我选择的 ID 范围越大，它变得越慢，但该范围内的 ID 始终存在于表中。

所以我的问题是：

  1. 为什么会发生这种情况？我认为这可能与缓存有某种关系，但我不知道这是否正常。我的意思是在真实情况下，ID 几乎是随机的，而且我知道性能没有我的那么差。
  2. 如何提高性能？特别是，如果我有一个未排序的 ID-Name 对列表。除了排序然后才写入数据库之外，还有其他方法吗？

提前致谢！

performance update
Martin Hope
Joe
Asked: 2022-06-16 07:26:42 +0800 CST
  • 2

我有一个大表（最终可能有 10 亿行，但目前约为 2600 万行），我想一次性为给定分组在最高 PK 上设置一个标志。

我选择创建一个临时表来存储应该设置的 PK，current=true其余的都应该设置current=false。我制作了一个临时表而不是物化视图，但我认为它不会产生真正的区别。

为每个发现最大 ID 的过程并不太痛苦：

CREATE TABLE assertion (
    pk integer NOT NULL,
    a bigint NOT NULL,
    b bigint NOT NULL,
    c bigint NOT NULL,
    d integer NOT NULL,
    current boolean DEFAULT false NOT NULL
);

CREATE INDEX assertion_current_idx ON assertion USING btree (current) WHERE (current = true);
CREATE INDEX assertion_current_idx1 ON assertion USING btree (current);
CREATE UNIQUE INDEX assertion_a_b_c_d_idx ON assertion USING btree (a, b, c, d) WHERE (current = true);

SELECT COUNT(pk) FROM assertion;

-- 26916858
-- Time: 2912.403 ms (00:02.912)

CREATE TEMPORARY TABLE assertion_current AS
    (SELECT MAX(pk) as pk, a, b, c, d
      FROM assertion
      GROUP BY a, b, c, d);

-- Time: 72218.755 ms (01:12.219)

ANALYZE assertion_current;

CREATE INDEX ON assertion_current(pk);

-- Time: 22107.698 ms (00:22.108)

SELECT COUNT(pk) FROM assertion_current;

-- 26455092
-- Time: 15650.078 ms (00:15.650)

根据 的计数assertion_current，我们需要为 98% 的行设置“当前”标志为真。

棘手的是如何assertion根据当前值在合理的时间内更新表格。有一个a, b, c, d, current必须维护的唯一约束，因此对current列的更新需要是原子的，以避免破坏约束。

我有几个选择：

选项1

仅更新那些current更改的值。这具有根据索引字段更新所需的最少行数的好处：


BEGIN;
UPDATE assertion
   SET current = false
   WHERE assertion.current = true AND PK NOT IN (SELECT pk FROM assertion_current);
UPDATE assertion
   SET current = true
   WHERE assertion.current = false AND PK IN (SELECT pk FROM assertion_current);
COMMIT;

但是这两个查询都涉及序列扫描assertion_current（我认为）必须乘以大量行。

Update on assertion  (cost=0.12..431141.55 rows=0 width=0)
   ->  Index Scan using assertion_current_idx on assertion  (cost=0.12..431141.55 rows=1 width=7)
         Index Cond: (current = true)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.00..787318.40 rows=29982560 width=4)
                 ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=4)

和

 Update on assertion  (cost=595242.56..596693.92 rows=0 width=0)
   ->  Nested Loop  (cost=595242.56..596693.92 rows=17974196 width=13)
         ->  HashAggregate  (cost=595242.00..595244.00 rows=200 width=10)
               Group Key: assertion_current.pk
               ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=10)
         ->  Index Scan using assertion_pkey on assertion  (cost=0.56..8.58 rows=1 width=10)
               Index Cond: (pk = assertion_current.pk)
               Filter: (NOT current)

这意味着这些查询之一（许多当前为真或许多当前为假）总是需要很长时间。

选项 2

单次通过，但必须不必要地触摸每一行。

UPDATE assertion
   SET current =
     (CASE WHEN assertion.pk IN (select PK from assertion_current)
     THEN TRUE ELSE FALSE END)

但这会导致再次对 assertion_current 进行序列扫描

 Update on assertion  (cost=0.00..15498697380303.70 rows=0 width=0)
   ->  Seq Scan on assertion  (cost=0.00..15498697380303.70 rows=35948392 width=7)
         SubPlan 1
           ->  Materialize  (cost=0.00..787318.40 rows=29982560 width=4)
                 ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=4)

选项 3

与选项 1 类似，但WHERE在更新中使用：

BEGIN;
UPDATE assertion SET current = false WHERE current = true;
UPDATE assertion SET current = true FROM assertion_current
  WHERE assertion.pk = assertion_current.pk;
COMMIT;

但第二个查询涉及两次 seq 扫描：

 Update on assertion  (cost=1654256.82..2721576.65 rows=0 width=0)
   ->  Hash Join  (cost=1654256.82..2721576.65 rows=29982560 width=13)
         Hash Cond: (assertion_current.pk = assertion.pk)
         ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=10)
         ->  Hash  (cost=1029371.92..1029371.92 rows=35948392 width=10)
               ->  Seq Scan on assertion  (cost=0.00..1029371.92 rows=35948392 width=10)

选项 4

谢谢@jjanes，这花了> 6个小时，所以我取消了它。

UPDATE assertion
   SET current = not current
   WHERE current <>
     (CASE WHEN assertion.pk IN (select PK from assertion_current)
     THEN TRUE ELSE FALSE END)

生产

 Update on assertion  (cost=0.00..11832617068493.14 rows=0 width=0)
   ->  Seq Scan on assertion  (cost=0.00..11832617068493.14 rows=27307890 width=7)
         Filter: (current <> CASE WHEN (SubPlan 1) THEN true ELSE false END)
         SubPlan 1
           ->  Materialize  (cost=0.00..787318.40 rows=29982560 width=4)
                 ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=4)

选项 5

谢谢@a_horse_with_no_name。这在我的机器上需要 24 分钟。

UPDATE assertion tg SET current = EXISTS (SELECT pk FROM assertion_current cr WHERE cr.pk = tg.pk);

给

 Update on assertion tg  (cost=0.00..233024784.94 rows=0 width=0)
   ->  Seq Scan on assertion tg  (cost=0.00..233024784.94 rows=27445116 width=7)
         SubPlan 1
           ->  Index Only Scan using assertion_current_pk_idx on assertion_current cr  (cost=0.44..8.46 rows=1 width=0)
                 Index Cond: (pk = tg.pk)

有没有更好的方法来及时实现这一目标？

postgresql update