Kevin Meredith's questions

Dado:

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

Ao INSERTinserir uma nova linha onde b!= 0, pagarei a penalidade de atualizar o índice?

Idealmente, gostaria de acelerar as consultas com um valor b= 1sem adicionar atraso na inserção de novas linhas onde b!= 0.

Os documentos Multi-AZ AWS RDS declaram:

1. O que é “replicação síncrona em nível de bloco?”
2. Dado que essa replicação está no nível do disco, e não no nível do banco de dados, esse formato é tão confiável quanto a replicação do banco de dados?

Estou escrevendo um aplicativo que processa trabalhos. Um trabalho faz duas coisas que não são transacionais: ParteA e ParteB. Ambos são idempotentes.

Um trabalho tem três status:

• Criada
• ParteA_Concluído
• ParteB_Concluído

A lógica é mais ou menos assim:

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;

A ParteA pode ter êxito, mas não consegue atualizar a jobstabela. Uma tarefa cron de nova tentativa tentará novamente a conclusão, PartB_Done.

Se eu adicionasse mais de um cron job, ou seja, executando simultaneamente, há o risco de os cron jobs realizarem trabalho duplicado. Em particular, ambos os cron jobs poderiam ser refeitos PartA, PartBou ambos para o mesmo trabalho. Eu quero evitar isso.

Existe alguma maneira de executar o COMMIT, mantendo um FOR UPDATEbloqueio nas transações subsequentes na nova jobslinha que adicionei?

Achei que COMMIT AND CHAINfuncionaria, mas não tenho certeza do que fazer.

Dado:

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

Recentemente criei um banco de dados:

$createdb -U postgres postgres
Password: 

No entanto, não entendo por que a versão do servidor varia do cliente.

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

Como posso criar o servidor no 12.1 também?

Dado:

$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 |           |          | 

Corri então:

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

Por que ele retornou 0em vez de 1, ou seja, desde que inseri uma linha?

Dado:

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

Por que a consulta a seguir não usa b.ao índice de ?

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)

Dado:

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)

Eu então joinusei a seguinte sintaxe JOIN:

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

Então comparei com full outer join:

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

e cross join:

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

É sempre verdade que a from a, b, cvontade produzirá um cross join?

No PostgreSQL 12.1, dado:

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

Eu então preparei uma consulta que filtra apenas em a, ou seja, o primeiro elemento no índice.

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)

É capaz de usar o índice para filtrar a, de acordo com o acima.

Então, preparei uma query que filtra em aand c, ou seja, não usa b.

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

Então, eu corri 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)

Meu entendimento é que isso Index Cond: ((a = 1) AND (c = true))significa que abc_a_b_c_idxfoi capaz de usar ambos ae cvalores, apesar de bnão estar envolvido no where.

Se sim, como pode cser usado já que bnão é usado?

https://habr.com/en/company/postgrespro/blog/467437/ dá o seguinte exemplo de uma atualização perdida:

Por exemplo, duas transações vão aumentar o valor na mesma conta em ₽100 (₽ é o símbolo da moeda para o rublo russo). A primeira transação lê o valor atual (₽1000) e, em seguida, a segunda transação lê o mesmo valor. A primeira transação aumenta o valor (isso dá ₽1100) e grava esse valor. A segunda transação age da mesma forma: obtém o mesmo ₽1100 e escreve este valor. Como resultado, o cliente perdeu ₽100

Li isso algumas vezes. Mas não entendo como o cliente perdeu P100. Por favor explique.

Usando o Postgres 10.5, dado o seguinte:

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

Por que o acima retorna um idof 1em vez de 100, ou seja, de acordo com a expressão de tabela comum?

Quando seleciono na tabela imediatamente depois, vejo que as atualizações ocorreram.

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)

PostgreSQL (2ª Edição) de Douglas e Douglas observa:

Use procedimentos do lado do servidor (gatilhos e funções) para realizar operações comuns. Um procedimento do lado do servidor é analisado, planejado e otimizado no primeiro uso, não sempre que você o usa.

Ao usar uma instrução preparada, ela é analisada, planejada e otimizada apenas na primeira vez em que é usada (semelhante aos gatilhos e funções do texto citado) ou cada vez que é usada?

Nota dos documentos do Postgres:

Embora o acesso aos dados armazenados em uma visualização materializada seja geralmente muito mais rápido do que acessar as tabelas subjacentes diretamente ou por meio de uma visualização, os dados nem sempre estão atualizados;

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

Por que é “muitas vezes muito mais rápido?”

Dada a seguinte função do 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;

Em seguida, chamei-o de uma planilha do Oracle SQL Developer:

select nullFunction(3) from dualretorna (null).

Em seguida, modifiquei minha função para:

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;   

Mas recebi a mesma saída ao chamá-lo com um valor de 3- null.

Por que retorna NULL? Eu esperava que ele tivesse retornado 0desde varX, que estou assumindo ser igual a null, como parte do else.

Usando o Oracle, tentei escrever uma função que demonstra como retornar o resultado de uma SELECT ...consulta:

 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;

Mas não compila.

Como posso corrigir o acima IFpara RETURNretornar 42, ou seja, o resultado de select 42 from DUAL?