Don Draper's questions

Digamos que eu tenha essas duas tabelas: autor(id, nome, país) e publicação(id, nome, ano, autor_id).

O que eu quero chegar no final é:

autor_id | nome_autor | autor_país | autor_publicações

Onde autor_publicações é uma matriz JSON com todas as publicações do autor correspondente.

SELECT author.id AS author_id,
       author.name AS author_name,
       author.country as author_country,
       JSONB_AGG(JSONB_BUILD_OBJECT(
                         'id',
                         publication.id,
                         'name',
                         publication.name,
                         'year',
                         publication.year
                     )) AS author_publications

FROM author
JOIN publication
ON author.id = publication.author_id
GROUP BY author.id

Esta consulta retorna exatamente o que eu quero. Dito isto, o desempenho fica muito ruim quando a instância do banco de dados está ocupada. A agregação JSON torna a consulta 5 a 6 vezes mais lenta.

Existe um índice da chave estrangeira (author_id) na tabela de publicação.

Existe uma maneira melhor de obter o resultado que desejo?

+-----------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
|HashAggregate  (cost=107.31..108.35 rows=83 width=116) (actual time=25.764..38.070 rows=83 loops=1)                                            |
|  Group Key: author.id                                                                                                                      |
|  Batches: 5  Memory Usage: 4400kB  Disk Usage: 240kB                                                                                          |
|  ->  Hash Join  (cost=4.87..88.64 rows=2490 width=197) (actual time=0.077..2.250 rows=2448 loops=1)                                           |
|        Hash Cond: (publication.author_id = author.id)                                                                                     |
|        ->  Seq Scan on publication  (cost=0.00..76.90 rows=2490 width=113) (actual time=0.008..1.115 rows=2448 loops=1)|
|        ->  Hash  (cost=3.83..3.83 rows=83 width=84) (actual time=0.062..0.063 rows=83 loops=1)                                                |
|              Buckets: 1024  Batches: 1  Memory Usage: 18kB                                                                                    |
|              ->  Seq Scan on author  (cost=0.00..3.83 rows=83 width=84) (actual time=0.006..0.040 rows=83 loops=1)  |
|Planning Time: 0.287 ms                                                                                                                        |
|Execution Time: 38.494 ms                                                                                                                      |
+-----------------------------------------------------------------------------------------------------------------------------------------------+

[
  {
    "id": 1,
    "columns": [
      {
        "id": 16,
        "expression": "foo"
      },
      {
        "id": 12,
        "expression": "bar"
      }
    ]
  },
  {
    "id": 2,
    "columns": [
      {
        "id": 11,
        "expression": "baz"
      },
      {
        "id": 8,
        "expression": "foobar"
      }
    ]
  }
]

Eu tenho uma tabela onde uma das colunas é uma matriz JSON de elementos. Acima, você pode ver como pode ser o valor desse campo.

A ideia é filtrar as linhas das tabelas com base no valor do expressioncampo. Por exemplo, posso querer deixar apenas as linhas onde há uma coluna onde expressionequals foo.

Minha primeira ideia foi usar json_array_elements()várias vezes e, em seguida, escrever uma WHERE EXISTS(SELECT true FROM ...)cláusula, mas isso resulta em duplicatas de linha. Além disso, minhas tentativas de combinar vários json_array_elementsnem sempre funcionam da maneira que eu esperava.

Funcionaria se eu pudesse, de alguma forma, nivelar todos os itens para que houvesse uma grande lista de expressionvalores para cada linha. Infelizmente, não tenho certeza se existe uma maneira conveniente de fazer isso com essa estrutura aninhada.

ATUALIZAÇÃO: corrigido o objeto JSON

Minha tabela está assim:

id | description |      | tables
1  | sample_description | <sample_above>

O resultado desejado é manter as linhas onde existe um item no columnscampo entre os itens deste tablesarray json de forma que o expressionvalor seja igual<some_value>

Minha tabela, para simplificar, ficou assim:

id | foreign_key_1 | foreign_key_2 | value

Como fica claro pelos nomes, os campos foreign_key_1e foreign_key_2referem-se a PKs em duas outras tabelas.

O complicado aqui é que apenas um deles pode ser não nulo ao mesmo tempo, então também tenho as seguintes restrições:

UNIQUE (foreign_key_1),
UNIQUE (foreign_key_2),
CHECK ((foreign_key_1 IS NOT NULL AND foreign_key_2 IS NULL) 
    OR (foreign_key_1 IS NULL AND foreign_key_2 IS NOT NULL))

foreign_key_1Essas verificações garantem a integridade que eu queria, mas agora também quero excluir a entrada existente foreign_key_2antes de inserir uma nova entrada.

DELETE FROM my_table
WHERE ($1::INTEGER IS NULL OR foreign_key_1 = $1::INTEGER)
    AND ($2::INTEGER is NULL OR foreign_key_2 = $2::INTEGER)

Inicialmente, a ideia era ter apenas uma consulta que pudesse tratar ambas (foreign_key_1, NULL)e (NULL, foreign_key_2)passar como argumentos.

A principal preocupação é que a execução da instrução delete pode ser bem-sucedida, enquanto a inserção pode falhar e, então, a tabela ficará em um estado inválido.

Parece que usar uma transação aqui é o caminho a percorrer, mas da perspectiva da organização do código, será necessária muita refatoração agora para agrupar ambas as chamadas para repositórios na mesma transação.

Minha próxima ideia foi usar um UPSERT eON CONFLICT(target) DO UPDATE...

Não tenho uma única restrição aqui para usar como destino e a versão que estou usando (Postgres 14) ainda não possui a cláusula NULLS NOT DISTINCT.

Para reiterar, gostaria de ter uma interseção de:

• UNIQUE(foreign_key_1)
• UNIQUE(foreign_key_2)
• CHECK ((foreign_key_1 IS NOT NULL AND foreign_key_2 IS NULL) OR (foreign_key_1 IS NULL AND foreign_key_2 IS NOT NULL))

... para que possa ser usado como alvo ao ON CONFLICTfazer um UPSERT. Isso é possível de alguma forma?

No geral, meu entendimento é que, nessa situação, as opções são:

• Refatorando e executando as duas instruções em uma transação [muita refatoração]
• Encontrar uma maneira de ter um único alvo para a ON CONFLICTcláusula
• Criando um CTE inteligente que excluirá a linha existente primeiro
• algo mais

Quais são suas sugestões?

Atualizar

Depois de pensar um pouco, provavelmente posso usar:

CREATE UNIQUE INDEX one_null_idx ON my_table(COALESCE(foreign_key_1, -1), COALESCE(foreign_key_2, -1));

A restrição de verificação para garantir apenas uma chave estrangeira não nula também é necessária aqui.

Dado que as chaves estrangeiras são seriais e nunca podem ser -1, parece que isso pode funcionar. O que você acha?

Pode ser que meu projeto inicial seja falho, mas vamos começar a partir daí e ver se há uma abordagem boa o suficiente.

Eu tenho duas entidades, digamos, Ae Bque são muito semelhantes, mas não idênticas, então os conjuntos de colunas são diferentes. Por esses motivos, existem duas tabelas distintas, Ae B.

Dito isso, gostaria de criar outra tabela que descreva ambos Ae Bcom um conjunto de campos aplicáveis ​​a Ae B. Vamos chamar esta tabela entity_settings.

Seu esquema não é intuitivo para descobrir:

1. Minha primeira ideia é usar algum tipo de entity_typesinalizador e entity_idde A ou B dependendo do tipo com uma restrição UNIQUE em (entity_type, entity_id). Tal definição torna impossível ter uma restrição externa normal porque ela entity_idconsiste em ids de duas tabelas.

2. Outra opção é ter A_ide B_idque pode ser nulo mas referenciar os campos de ID das tabelas correspondentes. Isso faz sentido e fornece integridade referencial, mas as inserções nesta tabela serão bastante desajeitadas.

3. A única opção que estou considerando também é ter duas tabelas separadas para Ae Bpara que cada tabela tenha seu próprio filho. Isso parece um pouco inútil em termos de número de tabelas, mas é provavelmente a opção mais limpa em termos de design.

Como quase sempre, tenho a sensação de que está faltando algo e agradeceria muito se alguém pudesse sugerir outras opções.

EDITAR:

Na segunda opção, também faz sentido adicionar uma restrição CHECK comoCHECK((A_id IS NOT NULL AND B_id IS NULL)) OR (A_id IS NULL AND B IS NOT NULL))