All perguntas(dba)

Estou usando o Visual Studio 2022 para desenvolver pacotes SSIS 2022, criando um fluxo de dados de uma fonte OLEDB (consulta do SQL Server) para um KingswaySoft JSON Destination para postar em um ponto de extremidade de entrada de dados. O corpo da solicitação tem dois nós, um nó "where" e um nó "do" em um relacionamento de 1 para muitos.

Minha consulta de origem tem 10 linhas, então meu nó "do" terá 10 entradas com um único nó "where" como um wrapper. Eu configurei o Modo de Mesclagem para Buffer, mas meu nó "where" está se repetindo na Visualização e no tempo de execução. Eu quero alternar "Is Repeated" para o nó "where" para False, mas a árvore inteira está desabilitada. Como posso alternar a repetição do nó?

Estou adicionando uma restrição de verificação a uma tabela grande porque quero prepará-la para transformá-la em uma tabela particionada usando a alternância de partições.

A verificação é uma verificação de desigualdade simples em uma coluna e há um índice nessa coluna. Mas quando adiciono a restrição, o SQL Server ainda executa uma varredura de toda a tabela. É possível adicionar a restrição rapidamente e mantê-la confiável? Eu esperava que fosse possível por causa do índice.

Aqui está uma reprodução que mostra essa digitalização:

USE tempdb;
GO

SELECT * 
INTO MY_MESSAGES
FROM sys.messages;

CREATE CLUSTERED INDEX IX_MY_MESSAGES ON dbo.MY_MESSAGES(message_id);

SET STATISTICS IO ON;
ALTER TABLE dbo.MY_MESSAGES
    ADD CONSTRAINT CK_mymessages CHECK (message_id < 50000);
SET STATISTICS IO OFF;

DROP TABLE IF EXISTS dbo.MY_MESSAGES;

Isso é mostrado na aba de mensagens:

Table 'MY_MESSAGES'. Scan count 1, logical reads 10955

Eu estava olhando para algumas `tabelas de trabalho' da consulta abaixo do meu amigo Martin

SELECT st.session_id
        ,at.*,
 case transaction_type   
      when 1 then 'Read/Write'   
      when 2 then 'Read-Only'    
      when 3 then 'System'   
      when 4 then 'Distributed'  
      else 'Unknown - ' + convert(varchar(20), transaction_type)     
 end as tranType,    
 case transaction_state 
      when 0 then 'Uninitialized' 
      when 1 then 'Not Yet Started' 
      when 2 then 'Active' 
      when 3 then 'Ended (Read-Only)' 
      when 4 then 'Committing' 
      when 5 then 'Prepared' 
      when 6 then 'Committed' 
      when 7 then 'Rolling Back' 
      when 8 then 'Rolled Back' 
      else 'Unknown - ' + convert(varchar(20), transaction_state) 
 end as tranState, 
 case dtc_state 
      when 0 then NULL 
      when 1 then 'Active' 
      when 2 then 'Prepared' 
      when 3 then 'Committed' 
      when 4 then 'Aborted' 
      when 5 then 'Recovered' 
      else 'Unknown - ' + convert(varchar(20), dtc_state) 
 end as dtcState
FROM    sys.dm_tran_active_transactions at
  left JOIN sys.dm_tran_session_transactions st ON st.transaction_id = at.transaction_id
ORDER BY at.transaction_begin_time

uma coisa que me chamou a atenção é o fato de que essas transações não possuem uma sessão correspondente?

como posso descobrir de onde eles vêm?

Não há nenhuma sessão associada a eles.

Tenho uma tabela grande com medições de sensores que tem esta estrutura:

create table measurements (
  sensor_id    varchar(50)      not null,
  timestamp    timestamptz      not null,
  value        decimal(10, 6)   not null,
  primary key (sensor_id, timestamp)
)

Ele contém medições de sensores (pluvômetros, mas isso não importa muito) tiradas em intervalos de 5 minutos. Os valores do sensor podem ser 0 ou positivos, mas não negativos. Atualmente, o conjunto de dados remonta a um pouco mais de um ano, mas deve ser capaz de lidar com anos de dados.

Quero recuperar as medições mais recentes por sensor para análise posterior, de modo que eu obtenha pelo menos duas semanas de medições, e o conjunto de medições deve conter pelo menos 100 valores diferentes de zero. A consulta que usei é esta:

select *
from (
    select sensor_id, timestamp, "value",
      sum(cast("value" > 0 as INT)) over w as cum_nonzero_measurements,
      row_number() over w as cum_measurements,
      first_value(timestamp) over w - timestamp as age
    from measurements
    window w as (partition by sensor_id order by timestamp desc)
    ) windowed
where (cum_nonzero_measurements <= 100 or age < interval '2 weeks' )
  and sensor_id in ($1)
order by sensor_id, timestamp desc

Esta consulta precisa apenas das medições N mais recentes para um sensor, embora N possa variar. A maneira inteligente de executar tal consulta seria começar a ler a partir do valor mais recente, voltando no tempo. No entanto, o Postgresql não percebe que a consulta pode ser executada dessa forma e insiste em carregar todas as medições para os s fornecidos sensor_id, fazendo uma agregação de janela sobre todas as linhas e, somente então, filtrando a maioria das linhas para obter o resultado.

Eu tentei com índices diferentes, o que ajuda a recuperar todas as linhas para os sensores solicitados mais rapidamente, mas o que quer que eu faça, o Postgres continua carregando todas as linhas para os sensores em questão. Atualmente, o desempenho é aceitável, mas isso não escala muito bem se o conjunto de dados cresce.

Existe alguma maneira de convencer o Postgres de que ele não precisa carregar todas as linhas, apenas as mais recentes?

Existem algumas perguntas semelhantes a esta (por exemplo, https://dba.stackexchange.com/questions/72419/filling-in-missing-dates-in-record-set-from-generate-series), mas a solução não parece funcionar no meu caso... Basicamente, estou tentando gerar entradas zero para datas não presentes em uma série, mas suspeito que o problema seja que estou tendo que extrair o valor da data de um registro de data e hora? Uso SQL há anos, mas sou muito novo no postgres - impressionado até agora, no entanto... Tentei uma junção à esquerda e à direita aqui, mas sem sucesso...

Aqui está um pequeno caso de teste (ainda é recomendável mexer em SQL?):

-- temp test table - works as expected
WITH incomplete_data(payment_date, payment_id) AS (
   VALUES 
     ('2024-09-06 11:26:57.509429+01'::timestamp with time zone, 'uuid01')
    ,('2024-09-06 12:26:57.509429+01', 'uuid02')
    ,('2024-09-07 07:26:57.509429+01', 'uuid03')
    ,('2024-09-08 10:26:57.509429+01', 'uuid05')
    ,('2024-09-08 12:26:57.509429+01', 'uuid08')
    ,('2024-09-08 14:26:57.509429+01', 'uuid11')
    ,('2024-09-10 09:26:57.509429+01', 'uuid23')
   )
select * from incomplete_data;

-- generated dates - work as expected
select * FROM  (
   SELECT generate_series(timestamp '2024-01-01'
                        , timestamp '2024-01-01' + interval '1 year - 1 day'
                        , interval  '1 day')::date
   ) d(day)
;
   
-- join - failing to do what I was hoping..
WITH incomplete_data(payment_date, payment_id) AS (
   VALUES 
     ('2024-09-06 11:26:57.509429+01'::timestamp with time zone, 'uuid01')
    ,('2024-09-06 12:26:57.509429+01', 'uuid02')
    ,('2024-09-07 07:26:57.509429+01', 'uuid03')
    ,('2024-09-08 10:26:57.509429+01', 'uuid05')
    ,('2024-09-08 12:26:57.509429+01', 'uuid08')
    ,('2024-09-08 14:26:57.509429+01', 'uuid11')
    ,('2024-09-10 09:26:57.509429+01', 'uuid23')
   )
select count(payment_id), date_trunc('day',payment_date)::date as time
FROM  (
   SELECT generate_series(timestamp '2024-01-01'
                        , timestamp '2024-01-01' + interval '1 year - 1 day'
                        , interval  '1 day')::date
   ) d(day)
right  JOIN incomplete_data p ON date_trunc('day',payment_date) = d.day
where payment_date BETWEEN '2024-09-01T12:55:36.824Z' AND '2024-09-30T13:55:36.824Z'
GROUP  BY date_trunc('day',payment_date)
ORDER  BY date_trunc('day',payment_date);

 count |    time
-------+------------
     2 | 2024-09-06
     1 | 2024-09-07
     3 | 2024-09-08
     1 | 2024-09-10
(4 rows)

Eu esperava obter uma linha para cada dia do mês com zeros para dias não preenchidos. O pano de fundo é que isso é para preencher uma consulta grafana.

Alguém pode sugerir o que estou fazendo errado ou estou falhando em entender um problema maior aqui? Minha versão é:PostgreSQL 15.9 (Debian 15.9-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

ATUALIZAÇÃO
A resposta de jjanes abaixo ajudou a esclarecer a sequência de junção e filtragem para mim - esta é a seleção necessária:

WITH incomplete_data(payment_date, payment_id) AS (
   VALUES 
     ('2024-09-06 11:26:57.509429+01'::timestamp with time zone, 'uuid01')
    ,('2024-09-06 12:26:57.509429+01', 'uuid02')
    ,('2024-09-07 07:26:57.509429+01', 'uuid03')
    ,('2024-09-08 10:26:57.509429+01', 'uuid05')
    ,('2024-09-08 12:26:57.509429+01', 'uuid08')
    ,('2024-09-08 14:26:57.509429+01', 'uuid11')
    ,('2024-09-10 09:26:57.509429+01', 'uuid23')
   )
select count(payment_id), d.day as time
FROM  (
   SELECT generate_series(timestamp '2024-01-01'
                        , timestamp '2024-01-01' + interval '1 year - 1 day'
                        , interval  '1 day')::date
   ) d(day)
left JOIN incomplete_data p ON date_trunc('day',payment_date) = d.day
where d.day BETWEEN '2024-09-01T12:55:36.824Z' AND '2024-09-30T13:55:36.824Z'
GROUP  BY d.day
ORDER  BY d.day
;

Ao ler a documentação sobre o protocolo de replicação de streaming , vejo que posso obter o nome do snapshot. Isso parece ser útil para executar a sincronização antes de começar a transmitir as mensagens de replicação e garantir que tenho todos os dados. No entanto, não consigo obter o snapshot ao chamar a função sql pg_create_logical_replication_slot (e meu cliente não implementa o protocolo de replicação de streaming). Como posso obter esse nome de snapshot para executar a sincronização inicial?

Para o contexto, tenho uma tabela somente de anexação e quero usá-la como uma espécie de "produtor de eventos", usando replicação lógica para construir um mecanismo de assinatura.

Quero configurar meu servidor PostgreSQL para habilitar a autenticação com certificado TLS e senha ao mesmo tempo (para a mesma conexão, não apenas habilitar os dois mecanismos de autenticação).

Então, para que um cliente se autentique, ele deve:

• fornecer um certificado válido com o nome de usuário definido no nome comum e assinado por uma autoridade raiz confiável
• forneça uma senha para este usuário específico

Quero fazer isso para melhorar a segurança. Dessa forma, mesmo que meu certificado de cliente ou minha CA raiz vazem, você ainda precisa de uma senha para autenticar.

Não consigo descobrir como fazer isso. Nesta página da documentação do PostgreSQL , é dito que quando você usa o certificado TLS:

Nenhum prompt de senha será enviado ao cliente

Então é possível fazer isso e, se sim, como?

Está documentado que a atribuição recursiva de variáveis ​​(como esta ↓↓↓↓) pode às vezes retornar resultados incorretos:

SELECT @var = @var + [Name]
FROM dbo.People
...

Mas isso se aplica mesmo no caso em que tenho certeza de que apenas uma linha será retornada?

Exemplo ( Fiddle ):

CREATE TABLE dbo.People
(
  Id INT IDENTITY PRIMARY KEY,
  [Name] varchar(50) NOT NULL
);

INSERT INTO dbo.People([Name]) VALUES ('Bob');

DECLARE @var varchar(50) = '';


/*Is this bad even if the query always returns only one row?*/
SELECT @var = @var + [Name]
FROM dbo.People
WHERE Id = 1;

SELECT [@var] = @var;

A documentação diz que " Nesse caso, não há garantia de que @Var seria atualizado linha por linha. " o que (para mim) sugere que a seleção de uma única linha deve ser suficiente, certo?

Outro exemplo em que se pode querer usar isso é ao calcular alguns valores agregados e adicioná-los a algum lugar:

SELECT @Total = @Total + SUM(InvoiceTotal) /*single row, aggregate of several values*/
FROM dbo.Invoice
WHERE ...;
/*Notice no GROUP BY*/

Tenho um usuário que pode ser acessado de qualquer host (estamos atualmente em desenvolvimento). Mas quero limitar a execução de procedimentos armazenados apenas para localhost. O problema é que o localhostusuário não é reconhecido.

DROP USER IF EXISTS 'admin'@'%';
CREATE USER IF NOT EXISTS 'admin'@'%' IDENTIFIED WITH 'caching_sha2_password' BY 'my_password';

###########################
### STORED PROCEDURES   ###
###########################
GRANT EXECUTE ON MY_DATABASE.* TO 'admin'@'127.0.0.1';

###########################
### SELECT              ###
###########################
GRANT SELECT ON MY_DATABASE.MY_TABLE TO 'admin'@'%' WITH GRANT OPTION;
GRANT SELECT ON MY_DATABASE.MY_TABLE2 TO 'admin'@'%' WITH GRANT OPTION;
.
.
.

Executar esse código me gera o próximo erro.

[2024-11-23 20:59:09] [42000][1410] You are not allowed to create a user with GRANT

Alterar o host "resolve" o erro, mas quero limitar a execução ao localhost. Não sei se preciso apenas adicionar um novo usuário assim.

CREATE USER IF NOT EXISTS 'admin'@'127.0.0.1' IDENTIFIED WITH 'caching_sha2_password' BY 'my_password';

###########################
### STORED PROCEDURES   ###
###########################
GRANT EXECUTE ON MY_DATABASE.* TO 'admin'@'127.0.0.1';

###########################
### SELECT              ###
###########################
GRANT SELECT ON MY_DATABASE.MY_TABLE1 TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
GRANT SELECT ON MY_DATABASE.MY_TABLE2 TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
.
.
.

Ou se houver outra maneira sem repetir a definição do usuário

Estou em um servidor com grandes problemas de bloqueio. Não deadlocks, apenas bloqueios simples. Sabemos que é o gargalo principal do servidor. No entanto, eles não sabem quais são os líderes de bloco, então não sei quais consultas ajustar. Eu poderia instalar mais monitoramento, mas notei que eles têm o Query Store habilitado. Existe alguma informação no Query Store que pode ser usada para encontrar líderes de bloco?