Nathan Long's questions

Os documentos em INSERT dizem:

Se an index_predicatefor especificado, ele deverá, como requisito adicional para inferência, satisfazer os índices de árbitro. Observe que isso significa que um índice único não parcial (um índice único sem predicado) será inferido (e, portanto, usado por ON CONFLICT) se tal índice que satisfaça todos os outros critérios estiver disponível.

Não tenho certeza se entendi isso. Acho que estou dizendo que se eu tiver um índice não parcial como este:

CREATE UNIQUE INDEX ON managers (department_id);

e eu escrevo um insert com ON CONFLICT (department_id), ele inferirá que deveria usar o índice acima.

Mas se eu tiver um índice parcial como este:

CREATE UNIQUE INDEX ON managers (department_id) WHERE active = true;

então preciso usar o index_predicatepara direcioná-lo para usar o índice parcial, como ON CONFLICT (department_id) WHERE active = true.

Isso está correto?

Em https://www.timescale.com/blog/best-practices-for-time-series-metadata-tables/ há uma declaração de função que procura sobreposições de intervalo de tempo com:

SELECT NOT isempty(
        tstzrange(rangeStart, rangeEnd) *
        tstzrange(series.ts_start, series.ts_end, '[]')
    );

Quando procuro tstzrange nos documentos do PostgreSQL, encontro apenas uma menção a ele como um tipo de dados e nenhuma como uma função.

O que esse []argumento está fazendo e está documentado em algum lugar?

Quero implementar a paginação baseada em cursor para um grande conjunto de dados.

Com OFFSETpaginação baseada, quando o usuário quiser page N, você apenas OFFSET N * page_size. A consulta termina assim:

SELECT *
FROM books
ORDER BY id
OFFSET 100000
LIMIT 10;

Mas OFFSETfica mais lento quanto maior o valor dado porque o PostgreSQL precisa carregar e descartar as linhas anteriores.

Uma abordagem baseada em cursor é onde dizemos ao usuário "aqui está a página N, e como o último registro nela tem , idpara Xobter a próxima página, você deve me pedir registros com id > X." A consulta termina assim:

SELECT *
FROM books
WHERE id > 100000
ORDER BY id
LIMIT 10;

Nesse caso, o PostgreSQL pode carregar apenas as linhas necessárias.

Isso funciona muito bem ao classificar por arquivos id. Mas eu gostaria de poder classificar por outras colunas - por exemplo, por title- e ainda paginar.

O problema é que titlenão é único. Portanto, se o registro final na página Ntiver o título "Sobre Doninhas" e houver vários livros com esse título, solicitar a próxima página WHERE title > 'About Weasels'pode pular alguns deles.

Eu posso obter valores exclusivos tendo a solicitação do usuário WHERE (title, id) > ('About Weasels', 100000), mas isso funciona mal porque o PostgreSQL precisa calcular (title, id)para cada linha.

Eu tentei adicionar um índice para pré-computar essa 2-tupla:CREATE INDEX books_title_and_id ON books (title, id);

...mas esse índice não faz diferença para o plano de consulta.

Existe um índice que eu poderia criar para acelerar essa consulta?

A documentação do PostgreSQL sobre WITH mostra o seguinte exemplo:

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

Também observa:

Uma propriedade útil das consultas WITH é que elas são avaliadas apenas uma vez por execução da consulta pai, mesmo que sejam referidas mais de uma vez pela consulta pai ou pelas consultas irmãs WITH.

Vejo que WITHpode ser usado para outras coisas, como avaliação recursiva. Mas no exemplo acima, existe alguma diferença importante entre usar WITHe criar tabelas temporárias?

Estou tentando escrever uma consulta para detectar dados possivelmente inválidos em uma tabela PostgreSQL. Temos uma tabela de nomes de cidades como esta:

# `city_names`
id    | name     |   language | dialect | city_id
------------------------------------------------
01    | London   |   A        | A1      | 1
02    | London   |   A        | A2      | 1
03    | London   |   B        | B1      | 2
04    | London   |   B        | B2      | 3

Em nosso domínio:

• É bom que as linhas 01 e 02 mapeiem "Londres" para a cidade 1; os dialetos não são diferentes
• Tudo bem que a linha 03 mapeia "Londres" para a cidade 2; nesse idioma, o nome pode se referir a uma cidade diferente
• É suspeito que a linha 04 mapeie "Londres" para a cidade 3, porque já temos um mapeamento para a cidade 2 no mesmo idioma

Quero escrever uma consulta que selecione apenas as linhas 03 e 04 para que um humano possa decidir se uma delas aponta para a cidade errada.

Eu posso resolver esse problema processualmente, mas estou tendo problemas para fazer isso no SQL. Por exemplo, se eu GROUP BYidioma e nomeio, perco os city_idvalores das linhas individuais.

Basicamente, meu objetivo é: "Se houver mais de um city_id para o mesmo nome e idioma, liste esses city_ids."

Como posso fazer isso?

Este artigo aponta o seguinte problema de segurança com o MySQL:

USERS
id | email            | password_reset_token
1  | [email protected]  | QTlXww)uV!Hdg3U6aGwKV2FPvAqzVgPx
2  | [email protected]  | CZor5t7WbX#LTeqiG3v@6f3@z#)BfK*n

Aqui temos bons tokens aleatórios que um usuário deve provar que possui para redefinir sua conta. Mas o usuário consegue enviar um token de redefinição de 0, então executamos esta consulta:

SELECT * FROM `users` WHERE `email` = '[email protected]' AND `password_reset_token` = 0

O MySQL converte o token VARCHARem um INTpara fazer a comparação . Ele considera a VARCHARque não começa com um número igual a 0. Portanto, um invasor pode corresponder a qualquer string não numérica e assumir o controle da conta .

Mesmo que o token de reinicialização inicie alguns números, a situação não é muito melhor. O MySQL ignora todos os caracteres, exceto os números iniciais, ao fazer essa comparação, portanto, considera 12blahblah3blah4igual a 12, o que torna a adivinhação exponencialmente mais fácil.

Posso configurar o MySQL para não fazer esse tipo de typecasting? Se, por exemplo, fosse lançado INTem VARCHARvez de vice-versa, esse ataque não funcionaria.

Observação

Se a consulta for executada com '0'em vez de 0, isso não funcionará. O artigo discute essa vulnerabilidade em termos da aceitação de XML do Ruby on Rails, onde uma type=integerpropriedade convenceu o Rails a enviar um inteiro real na consulta.

Esse bug foi corrigido no Rails; agora ele converte todos os parâmetros de solicitação em strings, portanto, nunca criaria uma consulta com um número inteiro. Mas ainda acho que o MySQL deve ser configurável para evitar isso.

Quanto um índice precisa para restringir os resultados de uma pesquisa para ser útil na aceleração de consultas?

Alguns exemplos em todo o espectro:

• Uma coluna para armazenar valores verdadeiro/falso obviamente possui apenas dois valores exclusivos.
• Uma coluna de 'sobrenome' provavelmente tem muitos valores únicos (embora possa não ter).
• Uma coluna de chave primária tem todos os valores exclusivos.

Acho que o objetivo de um índice é restringir rapidamente uma pesquisa a algumas linhas e, portanto, o último caso é o melhor, o segundo está OK e o primeiro é inútil.

Estou correcto? Em caso afirmativo, aproximadamente onde está a linha de utilidade? Por exemplo, se um índice pode restringir os resultados a 1% das linhas, isso é útil? Que tal 10% ou 25%?