Kevin Meredith's questions

鉴于：

postgres=# create table testing(a int primary key, b int not null);
CREATE TABLE
postgres=# create index on testing (b) where b = 0;
CREATE INDEX
postgres=# \d testing
              Table "public.testing"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           | not null | 
 b      | integer |           | not null | 
Indexes:
    "testing_pkey" PRIMARY KEY, btree (a)
    "testing_b_idx" btree (b) WHERE b = 0

当在!=INSERT处添加新行时，我是否需要支付更新索引的惩罚？b0

理想情况下，我想加速值为b=的查询，而不会增加在!=1处插入新行的延迟。b0

多可用区 AWS RDS文档指出：

1. 什么是“同步块级复制”？
2. 鉴于这种复制是在磁盘级别，而不是数据库级别，这种形式是否与数据库复制一样可靠？

我正在编写一个处理作业的应用程序。一个作业做两件非事务性的事情：A 部分和 B 部分。两者都是幂等的。

一个职位有三种状态：

• 已创建
• A 部分_完成
• B 部分_完成

逻辑看起来像这样：

BEGIN;
let jobId = randomUUID
INSERT INTO jobs (id, status) VALUES (jobId, 'Created');
COMMIT;

BEGIN;
do partA in application code (make HTTP request)
UPDATE jobs SET status = 'PartA_Done' WHERE id=?
COMMIT;

BEGIN;
do partB in application code (make HTTP request)
UPDATE jobs SET status = 'PartB_Done' WHERE id=?
COMMIT;

PartA 可能会成功，但无法更新jobs表。重试 cron 作业将重试完成PartB_Done。

如果我要添加多个 cron 作业（即同时运行），则存在 cron 作业执行重复工作的风险。特别是，两个 cron 作业都可以重复执行PartA，PartB或者都可以重复执行同一个作业。我想避免这种情况。

有什么方法可以让我执行, 同时在我添加的新行上的后续事务中COMMIT保持锁定？FOR UPDATEjobs

我认为这COMMIT AND CHAIN会起作用，但我不知道该怎么做。

鉴于：

$which psql
/Library/PostgreSQL/12/bin/psql
$which createdb
/Library/PostgreSQL/12/bin/createdb

最近我创建了一个数据库：

$createdb -U postgres postgres
Password: 

但是，我不明白为什么服务器版本因客户端而异。

$psql -U postgres
Password for user postgres: 
psql (12.1, server 9.6.2)
Type "help" for help.

如何在 12.1 上创建服务器？

鉴于：

$psql -U postgres
Password for user postgres: 
psql (12.1, server 9.6.2)

postgres=# \d foo
                Table "public.foo"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 

然后我跑了：

postgres=# select count(*) from foo;
 count 
-------
     0
(1 row)

postgres=# insert into foo (a) values (1) returning (select count(*) from foo);
 count 
-------
     0
(1 row)

INSERT 0 1

为什么它返回0而不是1，即因为我插入了一行？

鉴于：

postgres=# \d b
                Table "public.b"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 a      | uuid |           | not null | 
Indexes:
    "b_a_idx" btree (a)

postgres=# \d c
                Table "public.c"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 a      | text |           | not null | 
Indexes:
    "c_a_idx" btree (a)

postgres=# explain b;
ERROR:  syntax error at or near "b"
LINE 1: explain b;
                ^
postgres=# analyze b; 
ANALYZE
postgres=# analyze c;
ANALYZE

为什么下面的查询不使用b.a's 索引？

postgres=# explain (select * from b join c on (b.a :: text) = c.a);
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Merge Join  (cost=129.05..455.25 rows=12580 width=48)
   Merge Cond: (c.a = ((b.a)::text))
   ->  Index Only Scan using c_a_idx on c  (cost=0.15..64.55 rows=1360 width=32)
   ->  Sort  (cost=128.89..133.52 rows=1850 width=16)
         Sort Key: ((b.a)::text)
         ->  Seq Scan on b  (cost=0.00..28.50 rows=1850 width=16)
(6 rows)

鉴于：

postgres=# \d foo
                Table "public.foo"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           | not null | 
 b      | text    |           | not null | 
Indexes:
    "foo_pkey" PRIMARY KEY, btree (a)

postgres=# \d bar
                Table "public.bar"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           | not null | 
 b      | text    |           | not null | 
Indexes:
    "bar_pkey" PRIMARY KEY, btree (a)

postgres=# select * from foo;
 a |  b  
---+-----
 1 | one
(1 row)

postgres=# select * from bar;
 a |  b  
---+-----
 2 | two
(1 row)

然后我会join使用以下 JOIN 语法：

postgres=# select * from foo, bar;
 a |  b  | a |  b  
---+-----+---+-----
 1 | one | 2 | two
(1 row)

然后，我将其与full outer join：

postgres=# select * from foo full outer join bar using (a);
 a |  b  |  b  
---+-----+-----
 1 | one | 
 2 |     | two
(2 rows)

和cross join：

postgres=# select * from foo cross join bar;
 a |  b  | a |  b  
---+-----+---+-----
 1 | one | 2 | two
(1 row)

from a, b, c意志产生 a总是真的cross join吗？

在 PostgreSQL12.1上，给出：

postgres=> create table abc(a int not null, b text not null, c boolean not null);
CREATE TABLE

postgres=> create index on abc (a, b, c);
CREATE INDEX

然后我准备了一个只过滤 的查询a，即索引中的第一个元素。

postgres=> prepare only_a(int) as select 42 from abc where a = 1;
PREPARE

postgres=> explain execute only_a(100);
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Bitmap Heap Scan on abc  (cost=4.20..13.67 rows=6 width=4)
   Recheck Cond: (a = 1)
   ->  Bitmap Index Scan on abc_a_b_c_idx  (cost=0.00..4.20 rows=6 width=0)
         Index Cond: (a = 1)
(4 rows)

根据上述内容，它可以使用索引来过滤a。

然后，我准备了一个过滤a和的查询c，即不使用b。

postgres=> prepare a_and_c(int) as select 42 from abc where a = 1 and c = true;
PREPARE

然后，我跑了explain execute，

postgres=> explain execute a_and_c(100);
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Bitmap Heap Scan on abc  (cost=4.21..11.32 rows=3 width=4)
   Recheck Cond: (a = 1)
   Filter: c
   ->  Bitmap Index Scan on abc_a_b_c_idx  (cost=0.00..4.21 rows=3 width=0)
         Index Cond: ((a = 1) AND (c = true))
(5 rows)

我的理解是，这意味着Index Cond: ((a = 1) AND (c = true))尽管没有参与.abc_a_b_c_idxacbwhere

如果可以，c既然b不使用怎么能使用？

https://habr.com/en/company/postgrespro/blog/467437/ 给出了以下丢失更新的示例：

例如，两次交易将使同一账户的金额增加 ₽100（₽ 是俄罗斯卢布的货币符号）。第一个事务读取当前值 (₽1000)，然后第二个事务读取相同的值。第一个交易增加了金额（这给出了 ₽1100）并写入了这个值。第二个事务的行为方式相同：它得到相同的 ₽1100 并写入该值。结果，客户损失了₽100

我读了几遍。但我不明白客户是如何失去 P100 的。请解释。

使用 Postgres 10.5，给出以下内容：

postgres=# begin; 
   with updated as (update bippy set id = 100 where id = 1 returning 1)
select id, ts from bippy;
commit;
BEGIN
 id |              ts               
----+-------------------------------
 66 | 2019-07-09 10:42:32.062496-04
 80 | 2019-07-09 10:43:28.068512-04
 80 | 2019-07-09 10:43:28.596341-04
 80 | 2019-07-15 14:42:32.062496-04
  1 | 2019-07-09 10:42:23.142809-04
  1 | 2019-07-09 10:42:25.366664-04
  1 | 2019-07-09 10:42:26.142027-04
  1 | 2019-07-09 10:42:26.702398-04
(8 rows)

COMMIT

为什么上面返回一个idof1而不是100，即根据公用表表达式？

当我立即从表中选择时，我看到更新已经发生。

postgres=# select * from bippy;
              ts               | id  
-------------------------------+-----
 2019-07-09 10:42:32.062496-04 |  66
 2019-07-09 10:43:28.068512-04 |  80
 2019-07-09 10:43:28.596341-04 |  80
 2019-07-15 14:42:32.062496-04 |  80
 2019-07-09 10:42:23.142809-04 | 100
 2019-07-09 10:42:25.366664-04 | 100
 2019-07-09 10:42:26.142027-04 | 100
 2019-07-09 10:42:26.702398-04 | 100
(8 rows)

Douglas 和 Douglas 的PostgreSQL（第 2 版）注释：

使用服务器端过程（触发器和函数）来执行常见操作。服务器端过程在您第一次使用时会被解析、计划和优化，而不是每次使用时。

使用准备好的语句时，它是仅在第一次使用时进行解析、计划和优化（类似于引用文本中的触发器和函数），还是每次使用时？

Postgres 的文档说明：

虽然访问存储在物化视图中的数据通常比直接或通过视图访问基础表快得多，但数据并不总是最新的；

https://www.postgresql.org/docs/9.6/rules-materializedviews.html

为什么它“通常快得多”？

给定以下 Oracle 函数：

create or replace FUNCTION nullFunction(a NUMBER) RETURN NUMBER IS
  varX NUMBER;
  BEGIN
  select 1 into varX from dual where exists(select 1 from dual where a = 1);
    IF (varX = 1) THEN RETURN 1;
                  ELSE RETURN 0;
    END IF;
  END;

然后，我从 Oracle SQL Developer 的工作表中调用它：

select nullFunction(3) from dual回报(null)。

接下来，我将函数修改为：

create or replace FUNCTION nullFunction(a NUMBER) RETURN NUMBER IS
  varX NUMBER;
  BEGIN
  select 1 into varX from dual where exists(select 1 from dual where a = 1);
    IF (varX is NULL) THEN RETURN 0;
                      ELSE RETURN 1;
    END IF;
  END;   

3但是，当我用-的值调用它时，我收到了相同的输出null。

为什么它返回NULL？作为. 0_ varX_ null_else

使用 Oracle，我尝试编写一个函数来演示如何返回SELECT ...查询结果：

 CREATE OR REPLACE FUNCTION f(v1 number) RETURN number IS
     BEGIN
      IF TRUE THEN
         RETURN (select 42 from dual);
       ELSE
         RETURN v1;
       END IF;
     END;

但它不编译。

我怎样才能修复上面IF的RETURN返回42，即结果select 42 from DUAL？