Gili's questions

Dado:

CREATE TABLE operation
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY;
);

CREATE TABLE listing
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  operation_id BIGINT REFERENCES operation (id)
);

CREATE TABLE listing_card
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  listing_id BIGINT REFERENCES listing (id),
  listing_operation_id BIGINT REFERENCES operation (id),
  index SMALLINT NOT NULL,
  UNIQUE (listing_operation_id, index)
);

• Várias listagens podem fazer referência à mesma operação.

Como posso garantir que a tabela listingcontenha uma (id, operation_id)combinação que corresponda listing_cardà de (listing_id, listing_operation_id)?

Eu quero algo na linha de, FOREIGN KEY (listing_id, listing_operation_id) REFERENCES listing (id, operation_id)mas não posso fazer isso porque (id, operation_id)não é exclusivo (várias listagens podem fazer referência à mesma operação).

As operações são referenciadas por diferentes tipos de tabelas. O objetivo final do esquema acima é garantir que listing_card.indexseja exclusivo em todas as operações de listagem. Uma única operação pode ser referenciada por 3 listagens diferentes, mas seus índices de cartão devem ser únicos entre si (em toda a operação).

Gostaria de armazenar URLs em uma coluna de banco de dados e impor uma restrição de que os valores devem ser exclusivos. Infelizmente, o MySQL tem um limite no comprimento das chaves de índice, o que significa que apenas os primeiros X caracteres da URL são verificados quanto à exclusividade. Portanto, encontrei falsos positivos em que duas URLs diferentes acionaram uma violação de integração de restrição porque os primeiros caracteres X eram idênticos.

Existe uma maneira de impor exclusividade em uma coluna VARCHAR sem qualquer limite de comprimento?

É possível, digamos, criar um índice não UNIQUE sobre os primeiros caracteres X e, em seguida, ter um bloco de gatilho INSERTs se os caracteres restantes forem idênticos?

O que acontece se um cliente estabelece uma transação, bloqueia uma linha ou tabela e o processo do cliente falha antes de ter a chance de reverter ou confirmar a transação? O que, se houver, o SQL Server faz com a transação?

1. O SQL Server possui um mecanismo de pulsação para detectar que o soquete foi eliminado?
2. Ao detectar que um soquete foi eliminado, o SQL Server reverte a transação automaticamente? Ou o bloqueio de linha/tabela persiste (causando um impasse em outros clientes)?

Forneça fontes confiáveis ​​para respaldar sua resposta.

ATUALIZAÇÃO : O processo do cliente está travando. Não estou falando de alguém invocando o comando KILL .

Não entendo o que Craig Ringer quis dizer quando comentou:

Esta solução está sujeita a atualizações perdidas se a transação de inserção for revertida; não há verificação para garantir que UPDATE afetou alguma linha.

em https://stackoverflow.com/a/8702291/14731 . Forneça uma amostra de sequência de eventos (por exemplo, Thread 1 faz X, Thread 2 faz Y) que demonstre como as atualizações perdidas podem ocorrer.

Eu tenho uma lista de adjacências composta por duas tabelas:

CREATE TABLE permission (id SMALLINT AUTO_INCREMENT(-32768, 1) PRIMARY KEY);
CREATE TABLE permission_graph (parent_id SMALLINT NOT NULL, child_id SMALLINT NOT NULL,
    UNIQUE KEY (parent_id, child_id),
    FOREIGN KEY (parent_id) REFERENCES permission(id) ON DELETE CASCADE,
    FOREIGN KEY (child_id) REFERENCES permission(id) ON DELETE CASCADE);

Quando executo o seguinte CTE , obtenho um conjunto vazio:

WITH RECURSIVE cte (parent_id, child_id)
AS
(
  (
    SELECT anchor.parent_id, anchor.child_id
    FROM permission_graph anchor
    WHERE anchor.child_id = -32763
  )
  UNION ALL
  (
    SELECT recursive.parent_id, recursive.child_id
    FROM cte, permission_graph recursive
    WHERE recursive.child_id = cte.child_id
  )
)
SELECT cte.parent_id, cte.child_id
FROM cte

Mas se eu executar a condição de âncora:

SELECT anchor.parent_id, anchor.child_id
FROM permission_graph anchor
WHERE anchor.child_id = -32763

Eu recebo:

[parent_id = -32767, child_id = -32763]
[parent_id = -32768, child_id = -32763]

Por que o CTE está retornando um conjunto vazio quando o resultado da âncora não está vazio? O resultado CTE não deveria conter o resultado âncora?

Que tipo de sobrecarga devo esperar ao substituir uma única consulta complexa por várias consultas simples?

Meu objetivo é melhorar a legibilidade e a portabilidade de todo o código SQL, portanto, favorecerei construções simples e substituirei extensões específicas de banco de dados por ANSI SQL sempre que possível.

Por exemplo:

• Imagine que o cliente está invocando SQL dinâmico (em oposição a procedimentos armazenados)
• Cenário 1: Cliente invoca:INSERT INTO employee SELECT name FROM user
• Cenário 2: Cliente invoca:

Statement getNames = connection.createStatement();
try (ResultSet rs = getNames.executeQuery("SELECT name FROM user"))
{
  while (rs.next())
  {
    String name = result.getString(1);
    PreparedStatement prepared = connection.prepareStatement("INSERT INTO employee SET name = ?");
    prepared.setString(1, name);
    prepared.executeUpdate();
  }
}

O Cenário 1 não é uma consulta complexa, mas, para fins de argumentação, vamos fingir que é. O Cenário 2 obtém o mesmo resultado usando várias consultas (mais simples). Que tipo de sobrecarga posso esperar do Cenário 2 em relação ao Cenário 1? É algo com que devo me preocupar ou é insignificante?

ATUALIZAÇÃO : https://stackoverflow.com/a/14408631/14731 faz um bom ponto. A decomposição manual de consultas codifica permanentemente um plano de execução em vez de permitir que o otimizador do banco de dados escolha. Dito isto, ainda não está claro se a sobrecarga é significativa.

É possível que as mensagens de log se estendam por várias linhas? Estou esperando:

RAISE WARNING 'Line 1\nLine 2';

resultando no seguinte log:

Line 1
Line 2

Mas, em vez disso, recebo:

Line 1\nLine 2

Alguma ideia?

Quando eu executo:

CREATE VIEW foreign_keys AS
SELECT
    tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc, information_schema.key_column_usage AS kcu,
    information_schema.constraint_column_usage AS ccu
WHERE
    tc.constraint_type = 'FOREIGN KEY' AND tc.constraint_name = kcu.constraint_name AND
    ccu.constraint_name = tc.constraint_name;

CREATE FUNCTION GetPermissionColumns() RETURNS TABLE(column_name foreign_keys.column_name%TYPE)
    AS $$
BEGIN
    RETURN QUERY SELECT column_name FROM foreign_keys WHERE table_name = tg_table_name AND
        foreign_table_name = 'permission';
END;
$$ LANGUAGE plpgsql;

no Postgresql 9.3, recebo a seguinte mensagem de aviso:

type reference foreign_keys.column_name%TYPE converted to information_schema.sql_identifier (SQL State: 00000 - Error Code: 0)

O que estou fazendo de errado e como posso corrigir?

Estou tentando migrar meus testes de unidade de H2 para Postgresql.

Atualmente, o H2 me fornece um esquema na memória de modo que cada conexão seja mapeada para um esquema exclusivo, crie as tabelas, execute o teste e descarte o esquema. A criação e destruição do esquema são tratadas automaticamente pelo H2.

Os testes de unidade são executados simultaneamente.

Qual é a melhor maneira de fazer isso no Postgresql? Especificamente,

1. Como obtenho um esquema exclusivo por conexão?
   • A estrutura de teste deve gerar nomes exclusivos ou há um mecanismo integrado para fazer isso?
2. Como posso garantir que o esquema seja descartado quando a conexão for interrompida?
   • Não quero acabar com esquemas pendentes quando os testes de unidade forem eliminados.
3. Qual abordagem produzirá o melhor desempenho?
   • Preciso criar/eliminar dezenas de esquemas por segundo.

ATUALIZAÇÃO : Encontrei uma resposta relacionada aqui , mas ela falha ao descartar esquemas caso o processo que executa os testes de unidade seja interrompido.

Acabei de descobrir que o H2 não oferece suporte a conexões simultâneas usando diferentes níveis de isolamento de transação . Ou seja, alterar o isolamento da transação de uma conexão afeta todas as outras conexões.

O Postgresql suporta o uso de diferentes níveis de isolamento para cada conexão?

De acordo com https://stackoverflow.com/a/174047/14731 , separar as colunas pouco necessárias libera o cache, permitindo uma recuperação mais rápida das colunas mais usadas.

Eu tenho uma tabela cujas colunas são sempre recuperadas juntas, mas ainda gostaria de dividi-las por motivos de design (reduzir a duplicação em várias tabelas, melhorar a reutilização do código). Por exemplo, tenho tabelas diferentes que usam o mesmo esquema de permissão. Em vez de adicionar colunas de permissão a cada tabela, gostaria de usar uma chave estrangeira para fazer referência a uma tabela de esquema de permissão separada.

Preenchi o MySQL com 1 milhão de linhas, executei consultas em ambas as versões e descobri que a versão com JOIN é ~3x mais lenta (0,9 segundos versus 2,9 segundos).

Aqui estão minhas tabelas:

original
(
    id BIGINT NOT NULL,
    first BIGINT NOT NULL,
    second BIGINT NOT NULL,
    third BIGINT NOT NULL
);
part1
(
    id BIGINT NOT NULL,
    first BIGINT NOT NULL,
    second BIGINT NOT NULL,
    PRIMARY KEY(id)
);
part2
(
    link BIGINT NOT NULL,
    third BIGINT NOT NULL,
    FOREIGN KEY (link) REFERENCES part1(id)
);

Aqui estão minhas perguntas:

SELECT first, second, third FROM original;
SELECT part1.first, part1.second, part2.third FROM part1, part2 WHERE part2.link = part1.id;

Existe alguma maneira de reduzir a sobrecarga de desempenho do design dividido?

Se você quiser reproduzir esse teste do seu lado, pode usar o seguinte aplicativo Java para gerar o script SQL para preencher o banco de dados:

import java.io.FileNotFoundException;
import java.io.PrintWriter;

public class Main
{
    public static void main(String[] args) throws FileNotFoundException
    {
        final int COUNT = 1_000_000;
        try (PrintWriter out = new PrintWriter("/import.sql"))
        {
            for (int i = 0; i < COUNT; ++i)
                out.println("INSERT INTO original VALUES (" + i + ", " + i + ", 0);");
            out.println("INSERT INTO original VALUES (" + (COUNT - 2) + ", " + (COUNT - 1) +
                ", 1);");
            out.println();
            for (int i = 0; i < COUNT; ++i)
            {
                out.println("INSERT INTO part1 (first, second) VALUES (" + i + ", " + i + ");");
                out.println("INSERT INTO part2 VALUES (LAST_INSERT_ID(), 0);");
            }
            out.println("INSERT INTO part1 (first, second) VALUES (" + (COUNT - 2) + ", " +
                (COUNT - 1) + ");");
            out.println("INSERT INTO part2 VALUES (LAST_INSERT_ID(), 1);");
            out.println();
        }
    }
}

Se bem entendi, quando um usuário atualiza um registro de banco de dados e confirma a transação, a chamada commit() será bloqueada até que as seguintes etapas sejam concluídas:

• O banco de dados documenta a mudança no log write-ahead (WAL) .
• O banco de dados atualiza o registro real.
• O banco de dados remove a entrada WAL.

Tenho as seguintes dúvidas sobre o WAL:

1. Quando o banco de dados sabe que é seguro remover a entrada do WAL?
2. Um WAL implica gravar o registro no disco duas vezes (uma vez no WAL, uma vez no arquivo de banco de dados)?
3. O Postgres afirma que o WAL "reduziu significativamente o número de gravações em disco, porque apenas o arquivo de log precisa ser liberado para o disco", mas (voltando à pergunta nº 1) você não precisa liberar o arquivo principal do banco de dados antes de remover uma entrada do WAL?