Perguntas[postgresql-10](dba)

Eu fiz esta pergunta anteriormente , e alguém postou uma resposta muito útil no db fiddle aqui . Isso tem funcionado muito bem para mim. No entanto, agora eu gostaria de fazer uma nova pergunta (relacionada):

Suponha que eu elimine todas as linhas da tabela (ou seja, no exemplo do db fiddle, as linhas maçã e laranja são excluídas fazendo "delete from fruit where id > 0"). Se eu inserir outra fruta como melancia, o id começará em 4, não em 1. Como faço para forçar o tipo de id a ser redefinido para 0, para que a próxima inserção tenha id=1 ?

Ambiente: PostgreSQL v10.6 (instância do AWS RDS) Para a tabela fornecida:

CREATE temp TABLE lve
    ("fin" timestamptz, "vehiculo" int, "equipo" int)
;
    
INSERT INTO lve
    ("fin", "vehiculo", "equipo")
VALUES
    ('2021-12-01 00:00:00', 1, 888),
    (NULL, 3, 888),
    ('2021-05-01 00:00:00', 2, 888),
    ('2021-11-05 00:00:00', 10, 333),
    (NULL, 9, 333),
    ('2021-09-05 00:00:00', 5, 333)
;

Eu preciso do último registro (pela coluna ' fin ') onde ' fin ' não é nulo, para cada valor em uma determinada lista de ' equipo '.

Para um único dado ' equipo ', esta consulta funciona:

select * from lve
where equipo = 333 and fin is not null
order by fin desc
limit 1

para dois ' equipo ' , com um UNION posso obter o resultado desejado:

(select * from lve
where equipo = 333 and fin is not null
order by fin desc
limit 1)
union 
(select * from lve
where equipo = 888 and fin is not null
order by fin desc
limit 1);

resultado:

Mas como na realidade a tabela contém muito mais dados para muitos outros vehiculo/equipo e posso ter uma lista com N ' equipo ' para consultar, não é ideal continuar adicionando vários UNIONs manualmente.

Existe uma maneira de reescrever isso em uma única consulta onde posso passar a lista/matriz de determinado ' equipo ' (888, 333, nnn)?

PD: Meu plano B é criar uma função que retorne meu registro desejado para um único ' equipo ' e depois usar isso com um array, mas eu realmente quero saber se isso pode ser feito da maneira que estou pedindo.

Obrigado.

Estou usando AWS Aurora PostgreSQL Serverlesscom escalonamento automático. Parece que o dimensionamento limpa o buffer compartilhado, então, quando queremos aumentar o desempenho, somos forçados a enfrentar um gargalo de E/S. Após o aquecimento, vemos uma grande melhora no desempenho. No entanto, se executarmos consecutivamente uma vez dimensionados, a segunda execução será muito mais rápida. Embora eu não tenha visto nada específico sobre se o buffer compartilhado é limpo no dimensionamento, tenho quase certeza de que é.

O Aurora Serverless está usando atualmente PostgreSQL 10.14e oferece suporte à pg_prewarmextensão. Parece que a documentação mais recente sugere que o pré-aquecimento suporta o pré-aquecimento automático após a reinicialização do servidor, mas isso é sem servidor e uma versão que não parece mencionar o pré-aquecimento automático na documentação .

Encontrei este post que funciona muito bem para o PostgreSQL ao reiniciar o servidor ou se recuperar de uma falha.

1. Se pudéssemos pelo menos reter o conteúdo do buffer compartilhado do nó ACU inferior após o dimensionamento, tudo bem.
2. Se pudéssemos pré-aquecer exatamente o que precisa estar na memória antes do tempo, seria incrível!
3. Existem certas mesas que são bastante grandes e gostaríamos de pré-aquecer seletivamente as peças que queremos. pg_prewarmsuporta first_blocke last_blockbloqueia números para uma tabela/índice, mas como saber quais valores colocar lá?

Sabemos com antecedência quando é nosso pico e dizemos ao RDS para dimensionar logo antes, então temos uma janela de tempo em que podemos nos preparar.

Quais são minhas opções?

Eu passei pela documentação e pelas perguntas anteriores do stackoverflow, mas não encontrei as úteis. Postgres versão 10.6

Mesmo para o índice apenas verifica os valores de idx_tup_fetch estão aumentando. Portanto, não consigo entender a diferença entre idx_tup_read e idx_tup_fetch.

Inicialmente o entendimento foi: idx_tup_read: registros correspondentes lidos do índice.

idx_tup_fetch: linhas ativas buscadas na tabela depois que os registros são correspondidos no índice. Portanto, meu entendimento é que, apenas para o índice, verifica-se, pois nenhum dado é obtido da tabela, portanto, isso não deve ser incrementado.

Mas quando fiz os testes, esse entendimento não é verdade.

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        4 |            2 |             2
(1 row)

postgres=> select * from test_monika where rank=100;
 id | name | rank 
----+------+------
 95 |      |  100
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        5 |            3 |             3
(1 row)

postgres=> select rank from test_monika where rank=100;
 rank 
------
  100
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        6 |            4 |             4
(1 row)

postgres=> select id from test_monika where rank=100;
 id 
----
 95
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        7 |            5 |             5
(1 row)

postgres=> select 1 from test_monika where rank=100;
 ?column? 
----------
        1
(1 row)

postgres=> select * from pg_stat_all_indexes where indexrelname like 'test_monika_rank';
 relid | indexrelid | schemaname |   relname   |   indexrelname   | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+-------------+------------------+----------+--------------+---------------
 16413 |      16418 | public     | test_monika | test_monika_rank |        8 |            6 |             6
(1 row)

Considere este CTE:

WITH division_by_zero AS (
  SELECT 1/0
)
SELECT 42

Ele retorna 42 em vez de gerar um erro. Como posso forçar o PostgreSQL a avaliar o SELECT 1/0?

Tenho uma coluna com strings como 2015-W02, ou seja, a segunda semana de 2015. Gostaria de convertê-la em uma data (por exemplo, correspondente ao sábado dessa semana). Não parece que CONVERT nem PARSE suportam este estilo. Alguma ideia de como fazer isso?

Atualmente temos um cluster postgresql 10 ativo/standby (baseado em PAF/clusterlabs). De qualquer forma, os servidores são mantidos em sincronia usando: wal_level = replica

Isso está bom e funcionando.

No entanto, gostaríamos de replicar um único banco de dados do cluster para uma instância autônoma (para análise), para isso imaginamos que uma replicação lógica do banco de dados único funcionaria bem, mas percebemos que precisaríamos alterar o tipo de replicação no cluster (o que essencialmente o quebraria)

wal_level = lógico

A suposição acima está correta (não posso misturar "réplica e lógica")? Existe alguma outra sugestão sobre como obter o mesmo resultado (replicando um único banco de dados fora do cluster com muitos bancos de dados)

Obrigado por qualquer feedback/sugestão

Eu tenho essa tabela simples e registros de usuário.

Eu só quero que os usuários com ilike ou outra coisa consigam uma ordem melhor no PostgreSQL 10.12.

CREATE TABLE users(
   ID INT PRIMARY KEY      NOT NULL,
   username           CHAR(50) NOT NULL
);

INSERT INTO users (id, username) VALUES(1,'mandela');
INSERT INTO users (id, username) VALUES(2,'delpiero');
INSERT INTO users (id, username) VALUES(3, 'gondela');
INSERT INTO users (id, username) VALUES(4, 'del');
INSERT INTO users (id, username) VALUES(5, 'dell');
INSERT INTO users (id, username) VALUES(6, 'andel');

Quando eu consulto "del", quero obter o usuário "del" cujo id é 4 em primeiro lugar. tudo bem com pedidos por comprimento. Mas ainda não é o que eu quero. A ordem deve ser assim. A segunda ordem deve ser "dell", a terceira ordem deve ser "delpiero", a quarta "andel", a quinta "gondela" e a sexta "mandela".

select * from users where username ilike '%del%' order by length(users.username) asc;

Aqui está o SQL Fiddle

Estou preso com uma consulta:

CREATE TABLE public.bulk_sample (
    serial_number character varying(255),
    validation_date timestamp,  -- timestamp of entry and exit
    station_id integer,
    direction integer           -- 1 = Entry | 2 = Exit
);

INSERT INTO public.bulk_sample VALUES
  ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 08:31:58', 120, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 08:50:22', 113, 2)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 10:16:56', 113, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 10:47:06', 120, 2)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 16:02:12', 120, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 16:47:45', 102, 2)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 19:26:38', 102, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 20:17:24', 120, 2)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 07:58:20', 119, 1)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 08:43:35', 104, 2)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 16:38:10', 104, 1)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 17:15:01', 119, 2)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 17:42:29', 119, 1)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 17:48:05', 120, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 15:17:59', 120, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 15:25:25', 118, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 16:16:12', 118, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 16:32:51', 120, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 19:31:20', 120, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 19:39:33', 118, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 20:57:50', 118, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 21:16:25', 120, 2)
;

Eu tenho que criar uma consulta que dá um resultado da seguinte forma

source | dest | Count
120    | 113  |  1
113    | 120  |  1

Eu tentei o seguinte código, mas não consegui obter o resultado desejado:

SELECT serial_number
     , count(*)
     , min(validation_date) AS start_time
     , CASE WHEN count(*) > 1 THEN max(validation_date) END AS end_time
FROM  (
   SELECT serial_number, validation_date, count(step OR NULL) OVER (ORDER BY serial_number, 
validation_date) AS grp
   FROM  (
      SELECT *
           , lag(validation_date) OVER (PARTITION BY serial_number ORDER BY validation_date)
           < validation_date - interval '60 min' AS step
      FROM   table1 
       where BETWEEN '2020-02-01 00:00:00' AND '2020-02-01 23:59:59'
      ) sub1
   ) sub2
GROUP  BY serial_number, grp;

O intervalo de tempo é de cerca de 55 minutos a 60 minutos entre cada entrada e saída.

Eu também tentei uma junção interna, mas não consigo agrupar pelo intervalo de tempo em uma junção interna

SELECT source.station_id AS source_station ,dest.station_id AS destination_station ,source.count FROM 
    (
        SELECT serial_number,station_id,count(bulk_transaction_id) FROM table1
        WHERE 
            direction = 1 AND 
            validation_date BETWEEN '2020-02-01 00:00:00' AND '2020-02-01 23:59:59' 
        GROUP BY serial_number,station_id
    )source

 INNER JOIN 
    (
        SELECT serial_number,station_id,count(bulk_transaction_id) FROM table1
        WHERE 
            direction = 2 AND 
            validation_date BETWEEN '2020-02-01 00:00:00' AND '2020-02-01 23:59:59'
        GROUP BY serial_number,station_id
    )dest
ON source.serial_number = dest.serial_number and source.station_id <> dest.station_id

O desafio é que às vezes há nulo na data de entrada e às vezes é nulo na data de saída.

Estou tentando atualizar um valor de campo na coluna jsonb com valor de texto presente em outra coluna, mas recebendo alguns erros de sintaxe; não obtendo nenhuma solução.

estou tentando trocar valores de OutgoingVertexid & IncomingVertexId abaixo do JSONB

'[
      {
         "Edgeid":10,
         "Weight":100,
         "Active":1,
         "OutgoingVertexid":"",
         "IncomingVertexid":"G",
         "EdgeColor":"Black"
      }
   ]
'

então usado abaixo do código, colocando todos os valores OutgoingVertexid e IncomingVertexid na tabela temporária.

UPDATE temp_table 
        SET 
        owner_property = CASE 
                            WHEN owner_outgoing_edge IS NOT NULL 
                                THEN jsonb_set(owner_property, '{OutgoingVertexid}', '""')
                            ELSE 
                                jsonb_set(owner_property, '{OutgoingVertexid}', ''||owner_incoming_edge::jsonb||'') 
                                END;

mas ficando abaixo do erro:

ERRO: o elemento de caminho na posição 1 não é um inteiro:

Desde já, obrigado