gavenkoa's questions

我做了测试表：

create table tmp (id BIGINT NOT NULL IDENTITY PRIMARY KEY)

现在我想插入一个值：

insert into tmp () values();

它抱怨：

SQL Error [102] [S0001]: Incorrect syntax near ')'.

我无法插入 ID，因为它失败并显示：

insert into tmp (id) values(1);
-- SQL Error [544] [S0001]: Cannot insert explicit value for identity column
-- in table 'tmp' when IDENTITY_INSERT is set to OFF.

DEFAULT语法 forVALUES也被禁止：

insert into tmp values(default);
-- SQL Error [8101] [S0001]: An explicit value for the identity column in table 'tmp'
-- can only be specified when a column list is used and IDENTITY_INSERT is ON.

是否可以将数据移动到新表中以获取其他表而不会在中间丢失进度？

我认为plpgsql在每 N 次插入后按块读取数据并提交......如果过程在中间停止，则条件NOT EXISTS (...)允许通过再次重复过程来添加丢失的数据。

我认为它是重要的模式，并且可能直接使用以下语法支持它：

INSERT INTO ... SELECT ... WHERE ... AND NOT EXISTS (...)

复制中断的原因可能是约束或用户暂停操作。我们仍然希望不丢失进度和恢复过程（在NOT EXISTS (...)警卫的帮助下）。

我试图从“自动提交”概念中理解。

最初我在 Java JDBC API Connection.setAutoCommit(boolean autoCommit)的上下文中对此很感兴趣

快速搜索发现该术语并非特定于 JDBC，PostgreSQL 也指“自动提交”：

https://www.postgresql.org/docs/current/sql-start-transaction.html

在标准中，不必​​发出START TRANSACTION启动事务块：任何 SQL 命令都隐式地开始一个块。PostgreSQL 的行为可以被看作是在每个不跟随START TRANSACTION（或BEGIN）的命令之后隐式发出 COMMIT，因此它通常被称为“自动提交”。为了方便起见，其他关系数据库系统可能会提供自动提交功能。

如果它很普遍，自动提交模式的隔离级别是什么？

从概念上讲，因为在隐式隔离级别之前我们只有一个 SQL 语句COMMIT ，因此自动提交几乎没有意义。只有 READ UNCOMMITTED 可能会影响结果，其他异常是按照一系列 SQL 语句定义的。

我可以将自动提交隔离级别更改为/远离未提交的阅读吗？

我真的不明白在并发数据访问的情况下等待的承诺。

所有手册均按期限锁定。没有人解释锁可能会导致 SQL 客户端在服务器端等待锁获取（并且可能需要几秒/分钟/无穷大）？

是否存在锁不会导致等待的情况？我能想象：

• 可能READ UNCOMMITTED在某些情况下，比如没有 DDL？
• 不等就报错了...
• 没有人等待，但谁先commit获胜，其他人失败commit

我回顾了JDBC API：它从字面上提到：

SQLTimeoutException - 当驱动程序确定已超过setQueryTimeout方法指定的超时值并且至少尝试取消当前正在运行的语句时`

根据文档在客户端检测到超时。广泛使用的客户端 API对服务器端超时没有影响（包括由锁引起的）。可能您可以设置一些专有的连接属性来影响数据库行为。

我看到了一些专有的 SQL 扩展，例如：

• https://learn.microsoft.com/en-us/sql/t-sql/statements/set-lock-timeout-transact-sql SET LOCK_TIMEOUT timeout_period
• https://www.postgresql.org/docs/13/runtime-config-client.html set lock_timeout ms &set statement_timeout ms

对我来说，由于锁而等待的数据库是一个灰色区域。锁定是否意味着等待？

当列设置为时，Postgres 采取的策略尚不清楚NULL：

UPDATE tbl SET
  col1 = NULL,
  col2 = NULL
WHERE created < current_date - INTERVAL '1 year';

文档https://www.postgresql.org/docs/current/mvcc.html有点冗长和技术性，所以我不能可靠地推断：

如果设置为 NULL 或复制受影响的行/页？

看起来任何 UPDATE 都应该为 MVCC 语义创建新行，但如果设置为 NULL 是一种特殊情况怎么办？

对于 GDPR 合规性，我认为将所有个人历史数据都归零，并尝试理解大量周期性的含义UPDATE SET x = NULL。之后我应该考虑VACUUM吗？

我们有几个列的命名模式（如id/key或created/ creation_date/ create_date）并希望统一它。

我们也想以向后兼容的方式来做。我们希望能够同时运行旧代码和新代码，直到我们摆脱旧代码，所以如下：

ALTER TABLE table_name 
RENAME COLUMN column_name TO new_column_name;

是一个突破性的变化。

列是否有某种形式的别名，例如表的视图（对于 Postgresql）？

回答@CL（tnx 提示！）https://www.postgresql.org/docs/12/ddl-generated-columns.html

无法直接写入生成的列。在 INSERT 或 UPDATE 命令中，不能为生成的列指定值，但可以指定关键字 DEFAULT。

因此，如果一个想法是在您更新所有报告脚本之前保留列 - 这很好。但是，如果您想回滚到以前的应用程序版本-您不走运（（

目前我们在 Google Postgres Cloud 上提供：https ://cloud.google.com/sql/docs/postgres/db-versions并且 v12 仅处于测试阶段。

我们将货币汇率导入 DB：

CREATE TABLE currency_rate (
    id int8 NOT NULL,
    date date NOT NULL,
    currency varchar(3) NOT NULL,
    rate numeric(12,6) NOT NULL,
    CONSTRAINT currency_rate_pk PRIMARY KEY (id)
);

ALTER TABLE currency_rate add constraint currency_rate_un UNIQUE (currency, date);

但实际上我们只需要最新的可用汇率即可。

用 sort 和 写 CTE 很麻烦distinct on (currency)：

with cr as (
  select distinct on (currency) currency, rate from currency_rate
  order by currency, date)
select
  ...,
  sum((nd.original_amount - nd.new_amount)*cr.rate) as amount
from notification_data nd
join cr on cr.currency = nd.currency
...

查询具有以下很好的执行计划：

  CTE cr
    ->  Result  (cost=0.28..69.66 rows=13 width=16)
          ->  Unique  (cost=0.28..69.66 rows=13 width=16)
                ->  Index Scan using currency_rate_un on currency_rate  (cost=0.28..67.17 rows=995 width=16)
  ...
              ->  Hash Join  (cost=1029.26..57129.68 rows=18 width=60)
                    Hash Cond: ((nd.currency)::text = (cr.currency)::text)

我创建了视图：

CREATE OR REPLACE VIEW latest_currency_rate AS
SELECT
  DISTINCT ON (currency) currency, rate, date
FROM currency_rate
ORDER BY currency, date DESC;

但数据库优化器不使用来自的索引currency_rate_un：

explain select * from latest_currency_rate;

Unique  (cost=60.83..65.38 rows=12 width=16)
  ->  Sort  (cost=60.83..63.10 rows=910 width=16)
        Sort Key: currency_rate.currency, currency_rate.date DESC
        ->  Seq Scan on currency_rate  (cost=0.00..16.10 rows=910 width=16)

甚至对于：

explain select * from latest_currency_rate where currency = 'USD';

Unique  (cost=16.87..17.13 rows=12 width=16)
  ->  Sort  (cost=16.87..17.13 rows=104 width=16)
        Sort Key: currency_rate.date DESC
        ->  Bitmap Heap Scan on currency_rate  (cost=5.08..13.38 rows=104 width=16)
              Recheck Cond: ((currency)::text = 'USD'::text)
              ->  Bitmap Index Scan on currency_rate_un  (cost=0.00..5.06 rows=104 width=0)
                    Index Cond: ((currency)::text = 'USD'::text)

新视图与原始查询的集成给出：

explain select
  sum((nd.original_amount - nd.new_amount)*cr.rate) as amount
from notification_data nd
join latest_currency_rate cr on cr.currency = nd.currency
...

...
 ->  Hash  (cost=73.54..73.54 rows=13 width=12)
       ->  Subquery Scan on cr  (cost=68.37..73.54 rows=13 width=12)
             ->  Unique  (cost=68.37..73.41 rows=13 width=16)
                   ->  Sort  (cost=68.37..70.89 rows=1008 width=16)
                         Sort Key: currency_rate.currency, currency_rate.date DESC
                         ->  Seq Scan on currency_rate  (cost=0.00..18.08 rows=1008 width=16)
...

现在我很困惑。为什么原始 CTE 查询使用Index Scan和视图不使用相同的索引？

我应该用一些替代技巧（而不是distinct on）重写视图吗？

我正在考虑materialized view避免顺序扫描...

假设我们在 Postgres 中创建一个部分索引来避免无用的 NULL 数据：

 CREATE INDEX my_ix ON my (col1) WHERE col1 IS NOT NULL;

我应该将 SELECT 查询写为：

SELECT * FROM my WHERE col1 = 'abc';

或作为：

SELECT * FROM my WHERE col1 = 'abc' AND col1 IS NOT NULL;

对于 JDBC 连接库，它可能如下所示：

SELECT * FROM my WHERE col1 = ?;

而且我不确定 Postgres 是否可以在没有以下情况的情况下推断索引适用性：

 AND col1 IS NOT NULL

如果 Postgres 可以为索引推断传递性，这也很有趣：

 CREATE INDEX my_ix ON my (col1) WHERE col1 > 0;

我希望我不需要写：

SELECT * FROM my WHERE col1 > ? AND col1 > 0;

如果参数是 10、23 等 (> 0)。

Postgres FK 约束和索引是独立的。

最近，我正在寻找未使用的索引，并发现了通过以下方式消除约束执行所需的索引的技巧：

 WHERE NOT EXISTS (SELECT 1 
                   FROM pg_constraint c 
                   WHERE c.conindid = pg_stat_user_indexes.indexrelid)

我们独立于索引创建了 FK 约束。

是否有可能以某种方式pg_constraint.conindid将索引与 FK 约束相关联，以便从 FK 指向索引？

就像是：

ALTER TABLE my ALTER CONSTRAINT my_your_fk USING INDEX my_your_fk_i;

更新我在 Postgres 文档中看到了一些相关的内容：

ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

我在Postgres 文档中看到：

两个事务不能同时在同一张表上持有冲突模式的锁

ALTER TABLE在维护（架构迁移）期间，我们看到了由于挂起SELECT事务（pg_locks+ ）而无法进行的情况pg_stat_activity。

为了继续迁移，我们撤销CONNECT了“流氓”用户的权限并踢掉了他们的所有会话。

这是正确的：

BEGIN;
SELECT ...;

将阻塞ALTER TABLE直到COMMIT或ROLLBACK？

我们有很多：

select count(*) from TBL where XDATE between ? and ? and FLD = ?;

我们希望通过调整XDATE列（具有DATE类型）上的索引来提高性能。

什么类型的索引更适合DATE类型和条件之间？

我停止使用SQL Developer ，因为发现在 Emacs SQLi 模式下使用SQL Plus更方便。

SQL Developer对具有大量行的选择仅显示几行。但是SQL Plus倾向于转储所有匹配条件的行。我可以放ROWNUM <= N，但我经常忘记这样做。

SQL Plus是否有任何选项可以限制它从select语句中输出？我会把它/opt/instantclient/glogin.sql放在以适用于每个会话...

是否可以在包主体块之外定义包过程/功能（对于 Oracle）？

定义包体的简化语法是：

create or replace package body <package_name> IS

  function <function_name>
   <function_body>

  procedure <procedure_name>
   <procedure_body>

end <package_name>;
/

是否可以在包中定义程序，例如：

procedure <package_name>.<procedure_name>
 <procedure_body>

我问这个问题，因为我是新手，不明白为什么我只需要从单个程序中应用更改时需要编译整个包......

有关的

• 包相对于独立的过程和函数有什么好处？
• https://stackoverflow.com/questions/1812049/performance-standalone-procedure-vs-packaged-procedure-in-oracle

汤姆说：

• 始终使用包。
• 除了演示、测试和独立实用程序（什么都不调用并且什么都不调用）之外，永远不要使用独立的过程

在应用程序开发过程中，我有很多表存储“少量”数据（通常是 10-40 个值，id+value有时是type），这些数据包含“对象”的属性，例如产品的新鲜/腐烂、红色/绿色/蓝色。

我没有把这个属性放到产品表中，因为电子元件不能新鲜，氧气不能红色，表不能无限行数......

为了存储属性，我使用自定义小表格，其中 2-3 字段：id用于链接，name用于在应用程序中显示，有时type是否在同一类别中的属性组。

主要“对象”通过中间多对多表链接到属性。

是否有任何理由为那些少于 1000 个项目（通常是 10-40 个）的“小词典”创建和维护索引？

我的目标数据库是 Oracle，但我希望答案独立于供应商......

我填充 - 不，但没有技术技能来证明我的填充......