All perguntas(dba)

Aqui está uma tabela simples

Estou tentando encontrar combinações distintas na coluna RoleId entre diferentes usuários. Neste exemplo, posso ver que existem 3 usuários, mas duas combinações de funções distintas.

Os usuários 1 e 2 têm funções [1,2,3,4] e o usuário 3 tem funções [1,2,3]. Não consigo entender se é possível no T-Sql retornar essas combinações distintas usando funções PARTITION.

Eu tenho alguma lógica em meu aplicativo que acho que resulta nas seguintes chamadas do MySQL; no entanto, quando duas delas são executadas em alguns milissegundos, recebo duas linhas filhas incompatíveis.

1. Inicie a transação com isolamento de leitura repetível.
2. Buscar e bloquear o objeto ContaSELECT ... FROM Account WHERE id = ? FOR UPDATE
3. Leia os destinatários existentes vinculados à contaSELECT ... FROM Address WHERE account_id = ?
4. Se o endereço existente estiver marcado como principal, atualize-o.UPDATE Addresses SET primary = false WHERE id = ?
5. Insira o novo endereço com primário = verdadeiro.
6. Transação completa.

Se esse processo for executado duas vezes rapidamente, recebo duas linhas de endereço com primário = true e account_id definido para a conta que me surpreendeu porque pensei que bloquear a conta na etapa 2 impediria que várias transações fossem executadas simultaneamente. Não quero mudar o comportamento de uma forma que limite muito meu rendimento.

Eu me perguntei se preciso mudar o nível de isolamento para "Serializable" [sic], mas não tenho certeza se uma leitura "FOR SHARE" na etapa 3 realmente resolveria o problema.

Minha estrutura de dados é provavelmente óbvia pelo que foi dito acima, mas se parece com isto:

Account:

idPC

outros dados de conta irrelevantes

Address:

idPC,

account_id- não é uma chave estrangeira real, apenas um ID que corresponde ao PK da conta.

primary- booleano, deve ter no máximo um endereço primário por conta, mas isso não é aplicado no MySQL porque complicaria um pouco o banco de dados ter uma coluna gerada para permitir isso, já que o MySQL não suporta índices parciais.

outros dados de endereço irrelevantes

Eu abri meu arquivo de configuração do Postgres e onde posso encontrar um novo oficial?

Tenho um cenário de teste de dois bancos de dados postgresql conectados via replicação lógica. O BD "A" está publicando inserções e atualizações para a tabela "a1" e o BD "B" está cadastrado e recebendo tudo sem problemas.

Adicionei um gatilho antes da atualização para a tabela "a1" no banco de dados "B". Ele está funcionando para instruções de atualização executadas localmente em "B", mas não é acionado quando a atualização ocorre por meio de replicação lógica. Aqui está a função e o gatilho que estou usando para testá-lo.

CREATE OR REPLACE FUNCTION update_tg_func()
RETURNS TRIGGER AS $$
BEGIN
    -- Extract the id value from the update query
    -- Assuming that the 'id' column is being updated
    RAISE WARNING 'Got OP %, id %', TG_OP, NEW.id;
    -- For other operations, just return the NEW row
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER update_tg
BEFORE UPDATE ON a1
FOR EACH STATEMENT
EXECUTE FUNCTION update_tg_func();

ALTER TABLE a1 ENABLE ALWAYS TRIGGER update_tg ;

A versão do Postgres é 12.1, não testada em versões mais recentes.

EDITAR

Graças ao @Melkij notei que meu gatilho não está disparando porque minha mesa está vazia. E minha tabela está vazia porque tenho outro gatilho BEFORE INSERTque "redireciona" as linhas inseridas para outras tabelas, assim como faria o particionamento, mas sem usar particionamento real.

Achei que o uso FOR EACH STATEMENTacionaria o gatilho mesmo que nenhuma linha fosse afetada. A documentação diz:

um gatilho marcado é FOR EACH STATEMENTexecutado apenas uma vez para qualquer operação, independentemente de quantas linhas ele modifica https://www.postgresql.org/docs/12/sql-createtrigger.html

Removi a trigger que "redireciona" as linhas inseridas para que minha tabela no DB B tenha algumas linhas e agora a trigger dispara.

Preciso que a trigger seja acionada mesmo que a tabela esteja vazia. Alguma dica?

Tenho uma tabela PostgreSQL dfcom as seguintes informações:

Gostaria de adicionar uma coluna continuousque verifica se o trip_nrpara um determinado area_idsegue cronologicamente desde a primeira visita com base na ordem de timestamp.

Em outras palavras, continuous=Truese an area_idfor visitado pela enésima vez e as visitas anteriores foram iguais area_iddesde n, n-1,...,1.

E, continuous=Falsese an area_idfor visitado pela enésima vez, mas um diferente area_idfoi visitado em algum lugar entre as visitas n, n-1,...,1 (ou seja, a sequência de trip_nr's é quebrada em algum ponto).

Um exemplo da saída desejada:

Como posso conseguir isso de forma otimizada? As respostas Clickhouse/SQL também são bem-vindas.

Observe que o carimbo de data/hora não é necessariamente armazenado em ordem.

Tenho a seguinte tabela no MySQL:

CREATE TABLE `userlogtype` (
  `UserLogTypeID` int NOT NULL AUTO_INCREMENT,
  `UserLogType` varchar(150) DEFAULT NULL,
  `InsertUser` varchar(100) DEFAULT NULL,
  `InsertDateTime` datetime DEFAULT '0000-00-00 00:00:00',
  `UpdateUser` varchar(100) DEFAULT NULL,
  `UpdateDateTime` datetime DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`UserLogTypeID`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

e gostaria de criar 2 gatilhos: um após a inserção e outro após a atualização.

1. Após Insert eu gostaria de atualizar a nova linha: campos InsertUser + InsertDateTime
2. Após a atualização, gostaria de atualizar a nova linha: campos UpdateUser + UpdateDateTime

Eu tentei isso:

CREATE DEFINER=`root`@`localhost` TRIGGER `userlogtype_AFTER_INSERT` AFTER INSERT ON `userlogtype` FOR EACH ROW BEGIN
    UPDATE userlogtype
    SET InsertUser = CASE WHEN InsertUser IS NULL OR InsertUser='' OR InsertUser=' ' THEN CURRENT_USER ELSE InsertUser END,
    InsertDateTime = CASE WHEN InsertDateTime IS NULL OR InsertDateTime='0000-00-00 00:00:00' OR InsertDateTime=0  THEN CURRENT_TIMESTAMP ELSE InsertDateTime END
    WHERE UserLogTypeID=NEW.UserLogTypeID ; 
END

Consigo salvar a trigger sem erro no MySQL Workbench, mas quando testo um insert (inserindo apenas um valor para UserLogType), dá o seguinte erro:

Falha na operação: ocorreu um erro ao aplicar o script SQL ao banco de dados. Executando: INSERT INTO artistabookingdb. userlogtype ( UserLogType) VALORES ('erty');

ERRO 1442: 1442: Não é possível atualizar a tabela 'userlogtype' na função/gatilho armazenado porque ela já é usada pela instrução que invocou esta função/gatilho armazenado. Instrução SQL: INSERT INTO artistabookingdb. userlogtype( UserLogType) VALORES ('erty')

É um pouco estranho que os valores padrão não apareçam quando tento inserir um valor no campo UserLogType:

Se houver índice para uma coluna, isso acelerará a pesquisa se a condição for ... WHERE column IS NULL? Na verdade, é um índice composto em 2 colunas e a segunda coluna pode ser NULL ( WHERE col1 = X AND col2 IS NULL).

Comecei a usar o mycli e quero $HOME/.mycli-historyser gerado e atualizado a partir$HOME/.cache/mycli/.mycli-history

1. Movido $HOME/.myclirc->~/.config/mycli/.myclirc
2. Criadaalias mycli='sudo mycli --myclirc ~/.config/mycli/.myclirc'
3. Atualizado log_file = ~/.cache/mycli/.mycli.logem.myclirc
4. Mudou-se $HOME/.mycli.logpara~/.cache/mycli/.mycli.log

Essas etapas foram bem-sucedidas para os arquivos rc e log. Não consegui muito pesquisando on-line ou na documentação deles, mas vi essa resposta e adicionei export MYCLI_HISTFILE="~/.cache/mycli/.mycli-history"ao meu arquivo .zshrc. Depois de reiniciar meu shell, meu arquivo de histórico ainda está sendo preenchido no formato $HOME.

Existe algum sinalizador de linha de comando, variável de ambiente ou configuração que possa alterar isso? Caso contrário, minha única opção seria criar um link simbólico?

Usando mycli v1.27.0 em zsh no MacOS Sonoma 14.2.1

Eu tenho uma mesa data:

• com aproximadamente 400 milhões de linhas
• com data.idcomo int4 , não null e definido como chave primária
• é um servidor AWS RDS, com ~ 128G de RAM
• não há linha com id > 1e9

Esta consulta:

select count(*) from data where id > 1e9;

que retorna 0, leva consistentemente cerca de 25s para ser executado. Costuma levar mais de 2 minutos (posso ter corrido analyze datadurante minhas investigações e o tempo caiu para 25s).

Em outro servidor, um aws aurora Postgres com exatamente a mesma tabela, leva cerca de 3m30s.

De qualquer forma, a mesma consulta leva alguns ms em um banco de dados MySQL, como deveria. Sou muito novo no Postgres, então provavelmente estou perdendo algo óbvio.

explicar analisar selecionar contagem (*) dos dados onde id > 1e9;

QUERY PLAN
Finalize Aggregate  (cost=11944927.24..11944927.25 rows=1 width=8) (actual time=23986.851..23988.183 rows=1 loops=1)
  ->  Gather  (cost=11944927.03..11944927.24 rows=2 width=8) (actual time=23986.779..23988.177 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=11943927.03..11943927.04 rows=1 width=8) (actual time=23984.078..23984.079 rows=1 loops=3)
              ->  Parallel Index Only Scan using data_pkey on data  (cost=0.57..11804489.05 rows=55775191 width=0) (actual time=23984.074..23984.075 rows=0 loops=3)
                    Filter: ((id)::numeric > '1000000000'::numeric)
                    Rows Removed by Filter: 133840000
                    Heap Fetches: 100863313
Planning Time: 0.078 ms
Execution Time: 23988.219 ms

O que estou fazendo de errado?

Tenho uma consulta mas não sei qual índice criar para agilizá-la.

Esta é a consulta:

select
    `id_my_table`
from
    `my_table`
where
    now() between created_data
    and ifnull(finish_data, now())
    and `stato` = 1
    and `closed` != 1
    and `closed` = 0
    and `deleted` = 0
    and `id_my_table_type` in (3, 5)

Não consigo alterar a consulta, só gostaria de agilizar.

Se eu EXPLAINfizer a consulta, vejo que o mysql não usa nenhum índice.