All perguntas(dba)

Eu tenho tabelas criadas como tal:

BEGIN;
CREATE TABLE tag (
  id          SERIAL,
  name        VARCHAR(255)  UNIQUE NOT NULL,
  description VARCHAR(511)  NOT NULL          DEFAULT '',
  PRIMARY KEY(id)
);
CREATE TABLE fandom (
  id            SERIAL,
  name          VARCHAR(255)  UNIQUE NOT NULL,
  link          TEXT          UNIQUE NOT NULL,
  PRIMARY KEY(id)
);
CREATE TABLE character (
  fandom_id INT   REFERENCES fandom(id), -- allowed to be null
  link      TEXT  UNIQUE,
  PRIMARY KEY(id)
) INHERITS (tag);
COMMIT;

Em seguida eu faço algo como

INSERT INTO tag (name) VALUES ('John Doe')

Na hora da inserção não sei se 'John Doe' é um personagem, mas posso verificar depois. Eu gostaria de fazer algo como

BEGIN;
DELETE FROM tag WHERE name='John Doe';
INSERT INTO character (name) VALUES ('John Doe');
COMMIT;

O problema, a meu ver, é que o ID é incrementado para o segundo 'John Doe' e agora todas as chaves estrangeiras que fazem referência à tag 'John Doe' por ID estão desativadas em um, se a exclusão funcionar.

Existe uma maneira de simplesmente mover a linha de 'John Doe' de 'tag' para 'personagem'?

Sou um novato em banco de dados, então notas adicionais são bem-vindas nos comentários.

Estou tentando acelerar a seguinte consulta no postgres:

select MAX(msg."timestamp") AS latestDate, msg.channel_id from message msg group by msg.channel_id

o explainé como tal:

Finalize GroupAggregate  (cost=1000.63..2442779.42 rows=305 width=24)
  Group Key: channel_id
  ->  Gather Merge  (cost=1000.63..2442770.27 rows=1220 width=24)
        Workers Planned: 4
        ->  Partial GroupAggregate  (cost=0.57..2441624.90 rows=305 width=24)
              Group Key: channel_id
              ->  Parallel Index Only Scan using message_channel_id_timestamp on message msg  (cost=0.57..2243767.89 rows=39570792 width=24)
JIT:
  Functions: 6
  Options: Inlining true, Optimization true, Expressions true, Deforming true

O DDL da tabela é assim:

CREATE TABLE public.message (
    message_pgid bigserial NOT NULL,
    id uuid NOT NULL,
    "timestamp" timestamptz NOT NULL,
    "content" text NOT NULL,
    channel_id uuid NOT NULL,
    CONSTRAINT message_pk PRIMARY KEY (message_pgid),
    CONSTRAINT message_un UNIQUE (channel_id, id)
);
CREATE INDEX message_channel_id_idx ON public.message USING btree (channel_id);
CREATE INDEX message_channel_id_timestamp ON public.message USING btree (channel_id, "timestamp");
CREATE INDEX message_id ON public.message USING btree (id);
CREATE INDEX message_timestamp_idx ON public.message USING btree ("timestamp");


-- public.message foreign keys
ALTER TABLE public.message ADD CONSTRAINT channel_fk FOREIGN KEY (channel_id) REFERENCES public.channel(id) DEFERRABLE;
ALTER TABLE public.message ADD CONSTRAINT message_fk FOREIGN KEY (user_id) REFERENCES public."user"(id);

e por fim, o explain analyze:

Finalize GroupAggregate  (cost=1000.63..2442779.42 rows=305 width=24) (actual time=7631.501..7673.692 rows=597 loops=1)
  Group Key: channel_id
  ->  Gather Merge  (cost=1000.63..2442770.27 rows=1220 width=24) (actual time=7631.383..7673.511 rows=1667 loops=1)
        Workers Planned: 4
        Workers Launched: 4
        ->  Partial GroupAggregate  (cost=0.57..2441624.90 rows=305 width=24) (actual time=305.736..6125.479 rows=333 loops=5)
              Group Key: channel_id
              ->  Parallel Index Only Scan using message_channel_id_timestamp on message msg  (cost=0.57..2243767.89 rows=39570792 width=24) (actual time=0.557..4938.221 rows=31656633 loops=5)
                    Heap Fetches: 32082
Planning Time: 4.032 ms
JIT:
  Functions: 18
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 12.315 ms, Inlining 193.685 ms, Optimization 122.739 ms, Emission 100.570 ms, Total 429.309 ms
Execution Time: 7684.655 ms  

Como você pode ver, mesmo com um índice btree, a operação ainda leva 7,6 segundos, a maior parte dos quais é gasta apenas na varredura paralela do índice. Não sei como acelerar isso ainda mais. o índice tem um tamanho relativo de 5,7 G e dou à minha instância 6 GB de RAM, o que deve ser mais que suficiente para uma pesquisa btree max. Eu defini minhas configurações de acordo com pgtune ( https://pgtune.leopard.in.ua/ ).

Há alguma coisa que esteja faltando em face das coisas?

Se eu restaurar um backup do mysqldump e precisar recuperar as outras transações nos logs binários, devo iniciar a recuperação a partir do horário de início do dump (quando o backup foi iniciado) ou do horário de término do dump (quando o backup terminou)?

mysqlbinlog --start-datetime=?? /backup/mysql-bin.0*

Vi que a última linha do dump contém o tempo de conclusão. Devo usá-lo para iniciar a recuperação?

...
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2023-12-22 12:24:27

Mas não há hora de início no despejo. Se eu precisar me recuperar desde o início, como saber a hora?

Além disso, podemos recuperar apenas um único banco de dados (já que os logs binários alteram todos os bancos de dados)?

Eu queria tornar a consulta antiga a seguir segura contra corridas de dados ao executá-las em paralelo. A consulta verifica se existe uma linha em uma tabela usando critérios específicos e se tal linha não existir, insere uma nova com novos dados. A consulta antiga está colada abaixo:

BEGIN
    DECLARE @txtPer VARCHAR(MAX) = @nro;
    DECLARE @txtCmin VARCHAR(MAX) = @min;
    DECLARE @txtCmax VARCHAR(MAX) = @max;
    IF NOT EXISTS (SELECT 1 FROM SEND WHERE MSG LIKE (@txtPer + '%') AND STATE < 2 AND ID = 1)
    BEGIN
        ----Time of check is not time of use
        ----Someone could possibly do another insert before this == data race
        INSERT INTO SEND (SNDID, ID, MSGCODE, MSG, STATE, INFO, INFO_TEXT, CHANGEDATE, CREATEDATE)
        SELECT MAX(SNDID)+10,1,1,(@txtPer + @txtCmin + '           ' + @txtCmax),0,0,' ',getdate(),getdate() FROM SEND
    END
END

Eu criei uma nova versão que é um pouco mais limpa e usa bloqueio de tabela exclusivo:

BEGIN TRANSACTION;
DECLARE @txtPer VARCHAR(MAX) = @nro;
DECLARE @txtCmin VARCHAR(MAX) = @min;
DECLARE @txtCmax VARCHAR(MAX) = @max;

DECLARE @IdMax INT
--Get MAX and simultaneously acquire lock for the table to prevent modifications during this transaction?
SELECT @IdMax=MAX(SNDID) FROM SEND WITH(TABLOCKX)

IF NOT EXISTS(SELECT 1 FROM SEND WHERE MSG LIKE (@txtPer + '%') AND STATE < 2 AND ID = 1)
BEGIN
    INSERT INTO SEND (SNDID, ID, MSGCODE, MSG, STATE, INFO, INFO_TEXT, CHANGEDATE, CREATEDATE)
    VALUES((@IdMax + 10),1,1,(@txtPer + @txtCmin + '           ' + @txtCmax),0,0,' ',getdate(),getdate())
END
COMMIT; --writes potential change and releases table lock?

Meu entendimento está correto de que o bloqueio de tabela adquirido durante SELECT MAX()a operação (e possivelmente outros na mesma transação) é mantido até que toda a transação seja finalizada com a COMMITinstrução? DB é um antigo MS SQL Server 2005.

Suponha que exista uma tabela "Bank_Account" e contenha uma única coluna "balance", onde todos têm saldo = 100.

Se uma transação executar "atualizar Bank_Account set balance = 200 onde 1=1; commit;" É possível que quaisquer 2 transações executando uma seleção na tabela Bank_Account tenham uma leitura 200 e outra leitura 100 para uma conta bancária diferente?

Ou uma vez retornado 200 para uma determinada conta bancária, todas as outras necessariamente retornarão 200 também porque o commit foi atômico?

Minha intuição no banco de dados seria que uma vez que alguém lê 200, todos os outros devem ler 200 porque uma transação é atômica, mas também li que algumas seleções podem ser executadas sem bloquear a tabela ou linhas e retornar um instantâneo dos dados no início de uma transação, mesmo assumindo o nível de isolamento de leitura confirmada.

Se for esse o caso, como os bancos de dados implementam esse comportamento sem bloqueios? Que mágica o commit e os selects realizam para garantir que os selects operem em um comportamento atômico, tudo ao mesmo tempo, de um commit?

Obrigado

Normalmente tenho usuários comuns que se conectam ao meu banco de dados SQL Server e executam procedimentos armazenados no esquema chamado myschema.

Se esses usuários tentarem obter dados das tabelas do banco de dados, eles não verão nenhuma tabela, mas poderão acessá-las por meio de um procedimento armazenado em myschema.

Agora estou consultando sys.dm_exec_sessionse sys.dm_exec_connectionsgostaria de ter a mesma abordagem, mas não, tenho que conceder VIEW SERVER STATEpermissão para um usuário que está chamando a myschema.GetUserSessionsprocedure!

Afinal eu quero que os usuários tenham acesso apenas a alguns campos de sys.dm_exec_sessionse sys.dm_exec_connections, não a todas as visualizações, e apenas através deste procedimento armazenado, mas não diretamente.

Atualmente estou usando uma solução alternativa que verifica a permissão e retorna um conjunto de resultados vazio com os tipos de dados corretos:

CREATE PROCEDURE myschema.GetUserSessions
AS
    /* To be able to run this procedure an user should have VIEW SERVER STATE permission:
    USE master;
    GRANT VIEW SERVER STATE TO [HOST\Username];
    */

    IF EXISTS(SELECT permission_name FROM fn_my_permissions(NULL, 'SERVER') WHERE permission_name = 'VIEW SERVER STATE') BEGIN
        SELECT
            ses.login_name,
            ses.[host_name],
            con.client_net_address,
            ses.[program_name],
            ses.login_time,
            ses.last_request_start_time,
            ses.last_request_end_time,
            con.net_transport,
            con.encrypt_option
        FROM
            sys.dm_exec_sessions ses
            inner join sys.dm_exec_connections con on con.session_id = ses.session_id
        WHERE
            is_user_process = 1
    END
    ELSE BEGIN
        SELECT
            login_name = CAST('' AS NVARCHAR),
            [host_name] = CAST(NULL AS NVARCHAR),
            client_net_address = CAST(NULL AS VARCHAR),
            [program_name] = CAST(NULL AS NVARCHAR),
            login_time = CAST(0 AS DATETIME),
            last_request_start_time = CAST(0 AS DATETIME),
            last_request_end_time = CAST(NULL AS DATETIME),
            net_transport = CAST('' AS NVARCHAR),
            encrypt_option = CAST('' AS NVARCHAR)
        WHERE
            1 = 0
    END

Sinceramente, não gosto da ideia de nem conceder VIEW SERVER STATEpermissão aos usuários nem salvar os dados de vez em quando em alguma tabela intermediária.

Estou no MySQL 8.0.28 e tenho esta função:

DELIMITER $$

CREATE FUNCTION DelayedHello()
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
  DO SLEEP(3);
  RETURN CONCAT('Hello ', 'World!');
END$$

DELIMITER ;

E isso foi criado pelo usuário root.

Mas quando um usuário chamado testexecuta essa função, no show processlist, é isso que vejo. Então, parece que o usuário rootestá executando.

127.0.0.1> select * from information_schema.processlist ;
+-------+-----------------+------------------+-----------+---------+---------+------------------------+----------------------------------------------+
| ID    | USER            | HOST             | DB        | COMMAND | TIME    | STATE                  | INFO                                         |
+-------+-----------------+------------------+-----------+---------+---------+------------------------+----------------------------------------------+
| 48547 | root            | %:40814          | helpscout | Query   | 1       | User sleep             | DO SLEEP(3)                                  |

Tentei consultar information_schema.processlistpara ver o usuário, mas ele retornou a mesma informação de que o usuário root parece estar executando isso. Então, existe uma maneira de mostrar na lista de processos que é quem testestá executando isso? Caso contrário, como descubro quem realmente chamou essa função?

Estou lendo a documentação do MongoDB há horas, mas não consegui encontrar uma resposta clara e direta. Ao ler ou escrever um documento no MongoDB, que tipo de bloqueio (S, X, IS, IX) ele se aplica no nível do documento?

documento

No vídeo que assisti, explica que um bloqueio S é aplicado ao documento durante o processo de leitura e um bloqueio X é aplicado durante o processo de escrita. Isso também se aplica ao MongoDB?

vídeo que eu assisti

Li vários documentos, mas não consegui encontrar as informações que procuro.

Dada uma tabela assim:

Como eu escreveria uma consulta para retornar o caminho ltree correspondente mais longo, dada uma entrada?

Por exemplo:

(input) => expected output

(a.b.c) => a.b.c
(d.e.f) => d.e
(f.g.h) => f
(a.b)   => a.b

Eu gostaria de poder usar isso para unir uma tabela contendo caminhos ltree em seu "caminho correspondente mais longo" em outra tabela de uma forma que tenha bom desempenho. Então, dada uma tabela contendo linhas de todos os itens inputsdo exemplo acima, como eu a juntaria à tabela para obter linhas com a "correspondência mais longa"?

Meu fluxo de trabalho atual é o seguinte:

1. Uma tarefa 'Executar tarefa SQL' executa um procedimento armazenado que preenche uma tabela.

2. Depois disso, uma tarefa 'Dataflow Task' faz o seguinte:

   2A. Leia cada linha desta tabela por meio de um 'Componente Dataflow' que usa OpenRowset.
   2B. Passa isso para outro componente do Dataflow que faz algumas conversões.
   2C. Grava os resultados em CSV.

A passagem da Etapa 1 para a Etapa 2A parece redundante, pois a tabela no servidor nunca mais é usada. Gostaria de fazer com que o procedimento armazenado retornasse as linhas que ele gravaria na tabela e que o fluxo as usasse em vez de ler a tabela. Isso é possível?