Gili's questions

鉴于：

CREATE TABLE operation
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY;
);

CREATE TABLE listing
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  operation_id BIGINT REFERENCES operation (id)
);

CREATE TABLE listing_card
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  listing_id BIGINT REFERENCES listing (id),
  listing_operation_id BIGINT REFERENCES operation (id),
  index SMALLINT NOT NULL,
  UNIQUE (listing_operation_id, index)
);

• 多个列表可能引用相同的操作。

如何确保该表listing包含与's匹配的(id, operation_id)组合？listing_card(listing_id, listing_operation_id)

我想要一些类似的东西，FOREIGN KEY (listing_id, listing_operation_id) REFERENCES listing (id, operation_id)但我不能这样做，因为(id, operation_id)它不是唯一的（多个列表可以引用相同的操作）。

操作由不同类型的表引用。上述模式的最终目标是确保它listing_card.index在任何列表操作中都是唯一的。单个操作可能会被 3 个不同的列表引用，但它们的卡片索引在彼此之间必须是唯一的（在整个操作中）。

我想将 URL 存储在数据库列中，并强制执行值必须唯一的约束。不幸的是，MySQL 对索引键的长度有限制，这意味着只检查 URL 的前 X 个字符的唯一性。因此，我遇到了误报，其中两个不同的 URL 触发了约束集成违规，因为前 X 个字符恰好是相同的。

有没有办法在 VARCHAR 列上强制唯一性而对其长度没有任何限制？

例如，是否可以在前 X 个字符上创建一个非唯一索引，然后如果其余字符相同，则有一个触发器块 INSERTs？

如果客户端建立事务，锁定行或表，并且客户端进程在有机会回滚或提交事务之前崩溃，会发生什么？SQL Server 对事务做了什么（如果有的话）？

1. SQL Server 是否有心跳机制来检测套接字被杀死？
2. 在检测到套接字被杀死后，SQL Server 是否会自动回滚事务？或者行/表锁是否持续存在（导致其他客户端死锁）？

请提供权威来源来支持您的答案。

更新：客户端进程正在崩溃。我不是在谈论调用KILL命令的人。

我不明白 Craig Ringer 评论时的意思：

如果插入事务回滚，此解决方案可能会丢失更新；没有检查强制 UPDATE 影响任何行。

在https://stackoverflow.com/a/8702291/14731上。请提供一个示例事件序列（例如线程 1 执行 X，线程 2 执行 Y），以说明丢失更新是如何发生的。

我有一个由两个表组成的邻接表：

CREATE TABLE permission (id SMALLINT AUTO_INCREMENT(-32768, 1) PRIMARY KEY);
CREATE TABLE permission_graph (parent_id SMALLINT NOT NULL, child_id SMALLINT NOT NULL,
    UNIQUE KEY (parent_id, child_id),
    FOREIGN KEY (parent_id) REFERENCES permission(id) ON DELETE CASCADE,
    FOREIGN KEY (child_id) REFERENCES permission(id) ON DELETE CASCADE);

当我运行以下CTE时，我得到一个空集：

WITH RECURSIVE cte (parent_id, child_id)
AS
(
  (
    SELECT anchor.parent_id, anchor.child_id
    FROM permission_graph anchor
    WHERE anchor.child_id = -32763
  )
  UNION ALL
  (
    SELECT recursive.parent_id, recursive.child_id
    FROM cte, permission_graph recursive
    WHERE recursive.child_id = cte.child_id
  )
)
SELECT cte.parent_id, cte.child_id
FROM cte

但是如果我运行锚点条件：

SELECT anchor.parent_id, anchor.child_id
FROM permission_graph anchor
WHERE anchor.child_id = -32763

我得到：

[parent_id = -32767, child_id = -32763]
[parent_id = -32768, child_id = -32763]

当锚点结果非空时，为什么 CTE 返回一个空集？CTE 结果不应该包含锚点结果吗？

当用多个简单查询替换单个复杂查询时，我应该期望什么样的开销？

我的目标是提高所有 SQL 代码的可读性和可移植性，因此我将尽可能使用简单的结构并用 ANSI SQL 替换特定于数据库的扩展。

例如：

• 假设客户端正在调用动态 SQL（而不是存储过程）
• 场景 1：客户端调用：INSERT INTO employee SELECT name FROM user
• 场景 2：客户端调用：

Statement getNames = connection.createStatement();
try (ResultSet rs = getNames.executeQuery("SELECT name FROM user"))
{
  while (rs.next())
  {
    String name = result.getString(1);
    PreparedStatement prepared = connection.prepareStatement("INSERT INTO employee SET name = ?");
    prepared.setString(1, name);
    prepared.executeUpdate();
  }
}

场景 1 不是一个复杂的查询，但为了论证起见，我们假设它是。场景 2 使用多个（更简单的）查询获得相同的结果。与场景 1 相比，场景 2 的开销是多少？这是我应该担心的事情还是可以忽略不计？

更新：https ://stackoverflow.com/a/14408631/14731提出了一个很好的观点。手动分解查询会硬编码执行计划，而不是让数据库的优化器进行选择。话虽这么说，但仍不清楚开销是否有意义。

日志消息是否可以跨越多行？我期待：

RAISE WARNING 'Line 1\nLine 2';

产生以下日志：

Line 1
Line 2

但相反，我得到：

Line 1\nLine 2

有任何想法吗？

当我执行时：

CREATE VIEW foreign_keys AS
SELECT
    tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc, information_schema.key_column_usage AS kcu,
    information_schema.constraint_column_usage AS ccu
WHERE
    tc.constraint_type = 'FOREIGN KEY' AND tc.constraint_name = kcu.constraint_name AND
    ccu.constraint_name = tc.constraint_name;

CREATE FUNCTION GetPermissionColumns() RETURNS TABLE(column_name foreign_keys.column_name%TYPE)
    AS $$
BEGIN
    RETURN QUERY SELECT column_name FROM foreign_keys WHERE table_name = tg_table_name AND
        foreign_table_name = 'permission';
END;
$$ LANGUAGE plpgsql;

在 Postgresql 9.3 下，我收到以下警告消息：

type reference foreign_keys.column_name%TYPE converted to information_schema.sql_identifier (SQL State: 00000 - Error Code: 0)

我做错了什么，我该如何纠正？

我正在尝试将我的单元测试从 H2 迁移到 Postgresql。

目前，H2 为我提供了一个内存模式，这样每个连接都映射到一个唯一的模式、创建表、运行测试并删除模式。架构创建和销毁由 H2 自动处理。

单元测试同时运行。

在 Postgresql 中执行此操作的最佳方法是什么？具体来说，

1. 如何获得每个连接的唯一架构？
   • 测试框架应该生成唯一名称还是有内置机制来执行此操作？
2. 如何确保删除连接时删除架构？
   • 当单元测试被杀死时，我不想以悬空模式结束。
3. 什么方法会产生最高的性能？
   • 我需要每秒创建/删除数十个模式。

更新：我在这里找到了一个相关的答案，但如果运行单元测试的进程被杀死，它无法删除模式。

我刚刚发现H2 不支持使用不同事务隔离级别的并发连接。这意味着，更改一个连接的事务隔离会影响所有其他连接。

Postgresql 是否支持对每个连接使用不同的隔离级别？

根据https://stackoverflow.com/a/174047/14731，拆分不常用的列可以释放缓存，从而可以更快地检索常用列。

我有一个表，其列总是一起检索，但出于设计原因，我仍然想将它们拆分（减少跨多个表的重复，提高代码重用）。例如，我有不同的表使用相同的权限方案。我不想向每个表添加权限列，而是使用外键来引用单独的权限方案表。

我已经用 100 万行填充了 MySQL，对两个版本都运行了查询，发现带有 JOIN 的版本慢了大约 3 倍（0.9 秒对 2.9 秒）。

这是我的表：

original
(
    id BIGINT NOT NULL,
    first BIGINT NOT NULL,
    second BIGINT NOT NULL,
    third BIGINT NOT NULL
);
part1
(
    id BIGINT NOT NULL,
    first BIGINT NOT NULL,
    second BIGINT NOT NULL,
    PRIMARY KEY(id)
);
part2
(
    link BIGINT NOT NULL,
    third BIGINT NOT NULL,
    FOREIGN KEY (link) REFERENCES part1(id)
);

这是我的查询：

SELECT first, second, third FROM original;
SELECT part1.first, part1.second, part2.third FROM part1, part2 WHERE part2.link = part1.id;

有什么办法可以降低拆分设计的性能开销？

如果您想在您身边重现此测试，您可以使用以下 Java 应用程序生成 SQL 脚本来填充数据库：

import java.io.FileNotFoundException;
import java.io.PrintWriter;

public class Main
{
    public static void main(String[] args) throws FileNotFoundException
    {
        final int COUNT = 1_000_000;
        try (PrintWriter out = new PrintWriter("/import.sql"))
        {
            for (int i = 0; i < COUNT; ++i)
                out.println("INSERT INTO original VALUES (" + i + ", " + i + ", 0);");
            out.println("INSERT INTO original VALUES (" + (COUNT - 2) + ", " + (COUNT - 1) +
                ", 1);");
            out.println();
            for (int i = 0; i < COUNT; ++i)
            {
                out.println("INSERT INTO part1 (first, second) VALUES (" + i + ", " + i + ");");
                out.println("INSERT INTO part2 VALUES (LAST_INSERT_ID(), 0);");
            }
            out.println("INSERT INTO part1 (first, second) VALUES (" + (COUNT - 2) + ", " +
                (COUNT - 1) + ");");
            out.println("INSERT INTO part2 VALUES (LAST_INSERT_ID(), 1);");
            out.println();
        }
    }
}

如果我理解正确，当用户更新数据库记录并提交事务时， commit() 调用将阻塞，直到完成以下步骤：

• 数据库在预写日志 (WAL)中记录更改。
• 数据库更新实际记录。
• 数据库删除 WAL 条目。

我对 WAL 有以下疑问：

1. 数据库什么时候知道从 WAL 中删除条目是安全的？
2. WAL 是否意味着将记录写入磁盘两次（一次在 WAL 中，一次在数据库文件中）？
3. Postgres声称 WAL“显着减少了磁盘写入次数，因为只有日志文件需要刷新到磁盘”但是（回到问题 #1）在从瓦尔？