All perguntas(dba)

Eu estava revisando um código antigo escrito para PostgreSQL pré-8.4 e vi algo realmente bacana. Lembro-me de ter uma função personalizada fazendo isso antigamente, mas esqueci como array_agg()era pré-. Para revisão, a agregação moderna é escrita assim.

SELECT array_agg(x ORDER BY x DESC) FROM foobar;

No entanto, era uma vez, foi escrito assim,

SELECT ARRAY(SELECT x FROM foobar ORDER BY x DESC);

Então, eu tentei com alguns dados de teste.

CREATE TEMP TABLE foobar AS
SELECT * FROM generate_series(1,1e7)
  AS t(x);

Os resultados foram surpreendentes. A maneira #OldSchoolCool foi incrivelmente mais rápida: uma aceleração de 25%. Além disso, simplificá-lo sem a ORDEM, mostrou a mesma lentidão.

# EXPLAIN ANALYZE SELECT ARRAY(SELECT x FROM foobar);
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Result  (cost=104425.28..104425.29 rows=1 width=0) (actual time=1665.948..1665.949 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on foobar  (cost=0.00..104425.28 rows=6017728 width=32) (actual time=0.032..716.793 rows=10000000 loops=1)
 Planning time: 0.068 ms
 Execution time: 1671.482 ms
(5 rows)

test=# EXPLAIN ANALYZE SELECT array_agg(x) FROM foobar;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=119469.60..119469.61 rows=1 width=32) (actual time=2155.154..2155.154 rows=1 loops=1)
   ->  Seq Scan on foobar  (cost=0.00..104425.28 rows=6017728 width=32) (actual time=0.031..717.831 rows=10000000 loops=1)
 Planning time: 0.054 ms
 Execution time: 2174.753 ms
(4 rows)

Então, o que está acontecendo aqui. Por que array_agg é uma função interna muito mais lenta que o voodoo SQL do planejador?

Usando " PostgreSQL 9.5.5 em x86_64-pc-linux-gnu, compilado por gcc (Ubuntu 6.2.0-5ubuntu12) 6.2.0 20161005, 64 bits"

Eu gostaria de obter resultados por consulta isso:

SELECT *
FROM  (
   SELECT id, subject
   FROM mailboxes
   WHERE tsv @@ plainto_tsquery('avail')
   ) AS t1
ORDER BY id DESC;

Isso funciona e retorna linhas tsvcontendo 'Available' . Mas se eu usar 'avai' (truncando '...lable') ele não encontra nada.

Todas as consultas precisam estar no dicionário? Não podemos simplesmente consultar essas cartas? Tenho uma tabela que contém corpos de e-mail (conteúdo) e gostaria de fazer consultas rápidas pois ela cresce rapidamente. Atualmente, estou usando:

... WHERE content ~* 'avail'

Por que x IS NOT NULLnão é igual a NOT x IS NULL?

Este código:

CREATE TABLE bug_test (
    id int,
    name text
);

INSERT INTO bug_test
VALUES (1, NULL);

DO $$
DECLARE
    v_bug_test bug_test;
BEGIN
    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NULL);
    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NOT NULL);
    RAISE NOTICE '%: %', v_bug_test, (NOT v_bug_test IS NULL);

    SELECT *
    INTO v_bug_test
    FROM bug_test
    WHERE id = 1;

    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NULL);
    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NOT NULL);
    RAISE NOTICE '%: %', v_bug_test, (NOT v_bug_test IS NULL);
END
$$;

DROP TABLE bug_test;

dá a seguinte saída:

(,): t
(,): f
(,): f
(1,): f
(1,): f ???
(1,): t

enquanto eu esperaria obter esta saída:

(,): t
(,): f
(,): f
(1,): f
(1,): t <<<
(1,): t

Eu tenho um banco de dados muito grande, aproximadamente 100 GB. Estou executando a consulta:

select * from <table_name>;

e quero mostrar apenas as linhas 100 a 200.

Quero entender como isso acontece internamente. O banco de dados busca todos os registros do disco na memória e envia de volta 100 a 400 linhas para o cliente de consulta? Ou existe algum mecanismo, de modo que apenas esses registros (100º -200º) sejam buscados no banco de dados - usando o mecanismo de indexação como B-trees etc.?

Descobri que isso está relacionado ao conceito de paginação, mas não consegui descobrir exatamente como isso acontece internamente no nível do banco de dados.

Apenas esperando para confirmar minha observação e obter uma explicação sobre por que isso está acontecendo.

Eu tenho uma função definida como:

CREATE OR REPLACE FUNCTION "public"."__post_users_id_coin" ("coins" integer, "userid" integer) RETURNS TABLE (id integer) AS '
UPDATE
users
SET
coin = coin + coins
WHERE
userid = users.id
RETURNING
users.id' LANGUAGE "sql" COST 100 ROWS 1000
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER

Quando eu chamo essa função de uma CTE, ela executa o comando SQL mas não aciona a função, por exemplo:

WITH test AS
(SELECT * FROM __post_users_id_coin(10, 1))

SELECT
1 -- Select 1 but update not performed

Por outro lado, se eu chamar a função de um CTE e depois selecionar o resultado do CTE (ou chamar a função diretamente sem CTE) ele executa o comando SQL e aciona a função, por exemplo:

WITH test AS
(SELECT * FROM __post_users_id_coin(10, 1))

SELECT
*
FROM
test -- Select result and update performed

ou

SELECT * FROM __post_users_id_coin(10,1)

Como não me importo muito com o resultado da função (só preciso dela para realizar a atualização), existe alguma maneira de fazer isso funcionar sem selecionar o resultado do CTE?

Tenho uma tabela na qual armazeno todas as mensagens do fórum postadas pelos usuários no meu site. A estrutura de hierarquia de mensagens é implementada usando um modelo de conjunto aninhado .

A seguir, uma estrutura simplificada da tabela:

• ID (CHAVE PRIMÁRIA)
• Owner_Id (REFERÊNCIAS DE CHAVE ESTRANGEIRA PARA Id )
• Parent_Id (REFERÊNCIAS DE CHAVE ESTRANGEIRA PARA Id )
• esquerda
• certo
• nível

Agora, a tabela está mais ou menos assim:

+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| Id      | Owner_Id      | Parent_Id      | nleft      | nright      | nlevel      |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| 1       | 1             | NULL           | 1          | 8           | 1           |
| 2       | 1             | 1              | 2          | 5           | 2           |
| 3       | 1             | 2              | 3          | 4           | 3           |
| 4       | 1             | 1              | 6          | 7           | 2           |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +

Observe que a primeira linha é a mensagem raiz, e a árvore deste post pode ser exibida como:

-- SELECT * FROM forumTbl WHERE Owner_Id = 1 ORDER BY nleft;

MESSAGE (Id = 1)
    MESSAGE (Id = 2)
        Message (Id = 3)
    Message (Id = 4)

Meu problema ocorre quando tento excluir todas as linhas sob o mesmo Owner_Idem uma única consulta. Exemplo:

DELETE FROM forumTbl WHERE Owner_Id = 1 ORDER BY nright;

A consulta acima falha com o seguinte erro:

Código de erro: 1451. Não é possível excluir ou atualizar uma linha pai: uma restrição de chave estrangeira falha ( forumTbl, CONSTRAINT Owner_Id_frgnFOREIGN KEY ( Owner_Id) REFERENCES forumTbl( Id) ON DELETE NO ACTION ON UPDATE NO ACTION)

O motivo é que a primeira linha , que é o nó raiz ( Id=1), também tem o mesmo valor em seu Owner_Idcampo ( Owner_Id=1), e isso faz com que a consulta falhe devido à restrição de chave estrangeira.

Minha pergunta é: Como posso evitar essa circularidade de restrição de chave estrangeira e excluir uma linha que faz referência a si mesma? Existe uma maneira de fazer isso sem primeiro ter que atualizar o Owner_Idda linha raiz para NULL?

Eu criei uma demonstração deste cenário: http://sqlfiddle.com/#!9/fd1b1

Obrigada.

Baixei DeveloperDaysVM2016-06-02_13.ova de http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html e carreguei-o com sucesso no Virtual Box.

Entrei como usuário "oracle" e posso ver a área de trabalho.

De dentro da máquina virtual, abri o Firefox e naveguei para:

http://localhost:8080/ords/hrrest/employees/ e recebeu com sucesso uma lista de funcionários no formato JSON. Então eu sei que o banco de dados está funcionando.

Meu único problema é que não consigo acessar o banco de dados.

Eu tentei:

[oracle@vbgeneric oracle]$ sqlplus oracle

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 5 17:32:22 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password: oracle
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

meu ORACLE_SID=orcl12c

Como faço login no próprio banco de dados?

Estou no SQL Server 2016 e me divertindo muito... meu DB Mail não está enviando e estou ficando sem lugares para procurar. Verifiquei duas vezes as permissões da conta SQL para o executável do DBmail - ele leu e executou. Digitei uma regra para a porta de saída 587 do firewall. Tentei outra conta de e-mail e perfil com os mesmos problemas não enviados. As únicas entradas nos logs (logs de correio db) estão iniciando e terminando o serviço. Não há erros em qualquer lugar que eu possa encontrar. Os e-mails parecem simplesmente entrar na fila de envio e nunca mais sair dela. As contas podem enviar e receber emails por conta própria e de uma instância do SQL Server 2014 em outra máquina.

Eu tenho uma fila de itens com status de enviados "não enviados" e verifiquei todos os locais normais com resultados esperados em todos eles, além de uma longa fila de emails não enviados:

SELECT * FROM msdb..sysmail_event_log order by log_id DESC

SELECT * FROM dbo.sysmail_mailitems

SELECT * FROM dbo.sysmail_sentitems

USE msdb
SELECT sent_status, *
FROM sysmail_allitems

 SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

 EXECUTE msdb.dbo.sysmail_help_status_sp

Eu tentei desligá-lo e ligá-lo novamente... então eu perdi um DMV etc. que poderia esclarecer essa situação? Este é um problema conhecido com o SQL Server 2016 que não tenho em minhas pesquisas? Quaisquer outras etapas possíveis para enviar este e-mail?

Preciso fornecer dados em tempo real, ou quase em tempo real, para relatórios do SSRS e do Tableau. Não quero que o sistema OLTP de produção seja afetado negativamente por consultas de execução longa. A execução de uma consulta grande em um banco de dados secundário em um grupo de disponibilidade afetará o desempenho da transação no banco de dados primário?

Aqui está uma árvore de montagem que desejo pesquisar usando uma T-SQLconsulta recursiva (presumivelmente CTE) com os resultados esperados abaixo. Eu quero saber o valor total por montagem dada qualquer peça.

Ou seja, se eu pesquisar 'Rivet', quero saber a contagem total em cada nível da montagem, não apenas a contagem direta de filhos.

Assembly (id:1)
    |
    |-Rivet
    |-Rivet
    |-SubAssembly (id:2)
    |   |
    |   |-Rivet
    |   |-Bolt
    |   |-Bolt
    |   |-SubSubAssembly (id:3)
    |      |
    |      |-Rivet
    |      |-Rivet
    |
    |-SubAssembly (id:4)
       |-Rivet
       |-Bolt

    DESIRED Results
    -------
    ID, Count
    1 , 6
    2 , 3
    3 , 2
    4 , 1

Atualmente, posso obter os pais diretos, mas quero saber como estender meu CTE para permitir que eu role essas informações para cima.

With DirectParents AS(
--initialization
Select InstanceID, ParentID
From Instances i 
Where i.Part = 'Rivet'

UNION ALL
--recursive execution
Select i.InstanceID, i.ParentID
From PartInstances i  INNER JOIN DirectParents p
on i.ParentID = p.InstanceID

)

select ParentID, Count(instanceid) as Totals
from DirectParents
group by InstanceID, ParentID

Results
-------
ID, Count
1 , 2
2 , 2
3 , 2
4 , 1

script de criação

CREATE TABLE [dbo].[Instances] ( 
  [InstanceID] NVARCHAR (50) NOT NULL, 
  [Part] NVARCHAR (50) NOT NULL, 
  [ParentID] NVARCHAR (50) NOT NULL, );



INSERT INTO Instances 
Values 
  (1, 'Assembly', 0), 
  (50, 'Rivet', 1), 
  (50, 'Rivet', 1), 
  (2, 'SubAssembly', 1), 
  (50, 'Rivet', 2), 
  (51, 'Bolt', 2), 
  (51, 'Bolt', 2), 
  (3, 'SubSubAssembly', 2), 
  (50, 'Rivet', 3), 
  (50, 'Rivet', 3), 
  (4, 'SubAssembly2', 1), 
  (50, 'Rivet', 4), 
  (51, 'Bolt', 4)