joanolo's questions

Ao trabalhar com o pgAdmin 4 (e, na verdade, vários outros programas que atuam como clientes do banco de dados), a conexão com o servidor é desconectada após cerca de 10 ou 15 minutos de inatividade. Um café e um telefonema, e você recebe uma mensagem semelhante a "Desculpe, a conexão com o banco de dados foi perdida. Você quer que eu tente reconectar?"

E a reconexão do pgAdmin sempre precisa de mais de uma tentativa, e a árvore de objetos que já foi expandida é recolhida... Então, é um pouco chato.

O pgAdmin não parece ter nenhum parâmetro relacionado a esse comportamento. (Parece haver maneiras de alterar alguns tempos limite de conexão , mas eles estão relacionados a como o pgAdmin se comporta em relação ao servidor demorar muito para se conectar).

O que pode ser feito para evitar que o pgAdmin se desconecte do banco de dados?

Divulgação: Esta é realmente uma "pseudo-questão". É um spin-off de outro , que acabou por não estar relacionado com a perda de conectividade... Dado que já tinha uma resposta, acho (não muito humildemente) que vale a pena "perguntar", caso o resposta é útil para alguém.

Dados dois números ne m, quero gerar uma série da forma

1, 2, ..., (n-1), n, n, (n-1), ... 2, 1

e repita várias mvezes.

Por exemplo, para n = 3e m = 4, quero uma sequência dos seguintes 24 números:

1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1
----------------  ----------------  ----------------  ----------------

Eu sei como obter esse resultado no PostgreSQL por um dos dois métodos:

Usando a seguinte consulta, que usa a generate_seriesfunção, e alguns macetes para garantir que a ordem seja a correta:

WITH parameters (n, m) AS
(
    VALUES (3, 5)
)
SELECT 
    xi
FROM
(
    SELECT
        i, i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
    UNION ALL
    SELECT
        i + parameters.n, parameters.n + 1 - i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
) AS s0 
CROSS JOIN 
    generate_series (1, (SELECT m FROM parameters)) AS x(j)
ORDER BY
    j, i ;

... ou use uma função para o mesmo propósito, com loops adjuntos e aninhados:

CREATE FUNCTION generate_up_down_series(
    _elements    /* n */ integer,
    _repetitions /* m */ integer)
RETURNS SETOF integer AS
$BODY$
declare
    j INTEGER ;
    i INTEGER ;
begin
    for j in 1 .. _repetitions loop
        for i in         1 .. _elements loop
              return next i ;
        end loop ;
        for i in reverse _elements .. 1 loop
              return next i ;
        end loop ;
    end loop ;
end ;
$BODY$
LANGUAGE plpgsql IMMUTABLE STRICT ;

Como eu poderia fazer o equivalente no SQL padrão ou no Transact-SQL/SQL Server?

Consultas como SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b endsão possíveis, mas: Esta é uma boa prática?

É comum usar parâmetros na parte WHERE das consultas e ter algumas colunas computadas na parte SELECT, mas não tão comum para parametrizar a cláusula ORDER BY.

Digamos que temos um aplicativo que lista carros usados ​​(à la CraigsList). A lista de carros pode ser classificada por preço ou cor. temos uma função que, dada uma certa quantidade de parâmetros (digamos, faixa de preço, cor e critérios de classificação, por exemplo) retorna um conjunto de registros com os resultados.

Para torná-lo concreto, vamos supor que carsestão todos na tabela a seguir:

CREATE TABLE cars
(
  car_id serial NOT NULL PRIMARY KEY,  /* arbitrary anonymous key */
  make text NOT NULL,       /* unnormalized, for the sake of simplicity */
  model text NOT NULL,      /* unnormalized, for the sake of simplicity */
  year integer,             /* may be null, meaning unknown */
  euro_price numeric(12,2), /* may be null, meaning seller did not disclose */
  colour text               /* may be null, meaning unknown */
) ;

A tabela teria índices para a maioria das colunas...

CREATE INDEX cars_colour_idx
  ON cars (colour);
CREATE INDEX cars_price_idx
  ON cars (price);
/* etc. */

E tenha algumas enumerações de commodities:

CREATE TYPE car_sorting_criteria AS ENUM
   ('price',
    'colour');

... e alguns dados de exemplo

INSERT INTO cars.cars (make, model, year, euro_price, colour)

VALUES 
    ('Ford',   'Mondeo',   1990,  2000.00, 'green'),
    ('Audi',   'A3',       2005,  2500.00, 'golden magenta'),
    ('Seat',   'Ibiza',    2012, 12500.00, 'dark blue'),
    ('Fiat',   'Punto',    2014,     NULL, 'yellow'),
    ('Fiat',   '500',      2010,  7500.00, 'blueish'),
    ('Toyota', 'Avensis',  NULL,  9500.00, 'brown'), 
    ('Lexus',  'CT200h',   2012, 12500.00, 'dark whitish'), 
    ('Lexus',  'NX300h',   2013, 22500.00, NULL) ;

O tipo de consultas que vamos fazer são do estilo:

SELECT
    make, model, year, euro_price, colour
FROM
    cars.cars
WHERE
    euro_price between 7500 and 9500 
ORDER BY
    colour ;

Gostaríamos de ter consultas desse estilo em uma função:

CREATE or REPLACE FUNCTION get_car_list
   (IN _colour    text, 
    IN _min_price numeric, 
    IN _max_price numeric, 
    IN _sorting_criterium car_sorting_criteria) 
RETURNS record AS
$BODY$
      SELECT
          make, model, year, euro_price, colour
      FROM
          cars
      WHERE
           euro_price between _min_price and _max_price
           AND colour = _colour
      ORDER BY
          CASE WHEN _sorting_criterium = 'colour' THEN
            colour
          END,
          CASE WHEN _sorting_criterium = 'price' THEN
            euro_price
          END 
$BODY$
LANGUAGE SQL ;

Em vez dessa abordagem, o SQL nessa função poderia ser gerado dinamicamente (em PL/pgSQL) como uma string e depois EXECUTADO.

Podemos sentir algumas limitações, vantagens e desvantagens com qualquer uma das abordagens:

1. Dentro de uma função, descobrir qual é o plano de consulta para uma determinada instrução é difícil (se possível). No entanto, tendemos a usar funções principalmente quando vamos usar algo com bastante frequência.
2. Erros em SQL estático serão capturados (principalmente) quando a função for compilada ou quando for chamada pela primeira vez.
3. Erros em SQL dinâmico só serão detectados (principalmente) depois que a função for compilada e todos os caminhos de execução forem verificados (ou seja: o número de testes a serem executados na função pode ser muito alto).
4. Uma consulta paramétrica como a exposta provavelmente será menos eficiente do que uma gerada dinamicamente; no entanto, o executor terá um trabalho mais difícil de analisar / criar a árvore de consulta / decidir todas as vezes (o que pode afetar a eficiência na direção oposta).

Pergunta:

Como "obter o melhor dos dois mundos" (se possível)? [Eficiência + Verificações do compilador + Depuração fácil + Otimização fácil]

NOTA: isso deve ser executado no PostgreSQL 9.6.

Em algumas ocasiões, e depois de fazer um enorme update, insertou deletede uma tabela, iniciei um VACUUM FULL ANALYZEpara ter certeza de que o banco de dados não estava ficando muito inchado. Fazer isso em um banco de dados de produção me permitiu descobrir que isso não era uma boa ideia, porque eu poderia bloquear a tabela por um longo período de tempo. Então, eu cancelei o processo, talvez tentei apenas VACUUM(não completo) ou deixe AUTOVACUUMfazer mais tarde o que puder fazer.

A questão é: se eu parar um VACUUM ou AUTOVACUUM "no meio do caminho", todo o processamento já feito é perdido?

Por exemplo, se VACUUMjá encontrei 1 M de linhas mortas e eu paro, todas essas informações são perdidas? O VACUUM funciona de maneira totalmente transacional ("tudo ou nada", como um bom número de processos do PostgreSQL)?

Se VACUUM puder ser interrompido com segurança sem que todo o trabalho seja perdido, existe alguma maneira de fazer o vacuumtrabalho de forma incremental? [Trabalhe por 100 ms, pare, espere 10 ms para permitir o não bloqueio do resto do mundo... e assim por diante]. Eu sei que você pode fazer parte disso ajustando os parâmetros do autovacuum, mas estou pensando em poder controlar isso programaticamente, poder fazê-lo em determinados momentos / sob certas condições.

NOTA: Parar / cancelar / matar o processo significa neste contexto:

• Se estiver usando o pgAdmin, pressione o botão "Cancelar consulta".
• Se estiver trabalhando programaticamente, chame pg_cancel_backend().

Suponho que ambos sejam equivalentes. Eu não usei nenhum comando kill no nível do shell/sistema.

Sempre que preciso verificar a existência de alguma linha em uma tabela, costumo escrever sempre uma condição como:

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT *  -- This is what I normally write
          FROM another_table
         WHERE another_table.b = a_table.b
       )

Algumas outras pessoas escrevem assim:

SELECT a, b, c
  FROM a_table
 WHERE EXISTS
       (SELECT 1   --- This nice '1' is what I have seen other people use
          FROM another_table
         WHERE another_table.b = a_table.b
       )

Quando a condição é NOT EXISTSem vez de EXISTS: Em algumas ocasiões, posso escrevê-la com a LEFT JOINe uma condição extra (às vezes chamada de antijoin ):

SELECT a, b, c
  FROM a_table
       LEFT JOIN another_table ON another_table.b = a_table.b
 WHERE another_table.primary_key IS NULL

Eu tento evitá-lo porque acho que o significado é menos claro, especialmente quando o que é seu primary_keynão é tão óbvio, ou quando sua chave primária ou sua condição de junção é multi-coluna (e você pode facilmente esquecer uma das colunas). No entanto, às vezes você mantém o código escrito por outra pessoa... e ele está lá.

1. Existe alguma diferença (além do estilo) para usar SELECT 1em vez de SELECT *?
   Existe algum caso de canto em que não se comporte da mesma maneira?

2. Embora o que escrevi seja o SQL padrão (AFAIK): Existe essa diferença para diferentes bancos de dados / versões mais antigas?

3. Existe alguma vantagem em escrever explicitamente um antijoin?
   Os planejadores/otimizadores contemporâneos a tratam de forma diferente da NOT EXISTScláusula?

Vamos supor que eu tenha tabelas muito simples

CREATE TABLE a(id integer PRIMARY KEY, 
       t timestamp default now(), 
       sensor_readings real[]);
CREATE TABLE b(id integer PRIMARY KEY, 
       t timestamp default now(), 
       sensor_readings real[]);

com alguns dados sobre eles

INSERT INTO a(id) SELECT generate_series(    1,   100);
INSERT INTO b(id) SELECT generate_series(10001, 10100);

Na realidade, a tabela a pode ter cerca de 100_000 linhas e a tabela b cerca de 50_000. Na prática, também, a sequência id pode ter lacunas (da ordem de alguns %). Assim, o produto cartesiano axb tem cardinalidade de bilhões.

Eu quero pegar uma amostra aleatória de 1_000 pares classificados (a.id, b.id). Eu posso usar algo como a seguinte consulta:

SELECT  
    *
FROM
(
    SELECT
        *
    FROM
        (
        SELECT 
            a.id AS a_id, b.id AS b_id
        FROM
            a CROSS JOIN b
        ORDER BY
            random()
        ) AS s0
    LIMIT
        1000 
) AS s1
ORDER BY
    a_id, b_id ;

... mas se tornaria extremamente ineficiente assim que o número de linhas em a ou b crescesse (devido ao crescimento do CROSS JOIN).

Existe alguma maneira de fazer algo equivalente a isso de maneira ideal? Ou seja, existe uma maneira prática de obter uma amostra aleatória de linhas da a x brelação sem realmente ter que instanciá-la.

NOTA: Não há limitação quanto ao fato de que a.id ou b.id podem ser repetidos. Embora o par (a.id, b.id) não possa.

Se eu estivesse tentando programar isso em uma linguagem imperativa, provavelmente usaria um loop e faria algo como o pseudocódigo a seguir (e, em seguida, verificaria por um estatístico, para ter certeza de que realmente peguei uma amostra onde todos os pares têm a mesma probabilidade de serem escolhidos):

start with a result set equal to {} (empty set)
while size of result set < 1000
    Pick the id value from a random row from table a -> rand_id_a
    Pick the id value from a random row from table b -> rand_id_b
    If (rand_id_a, rand_id_b) not in result set
        append (rand_id_a, rand_id_b) to result set
    end if
end while
sort result set and return it

Existe uma maneira de obter um resultado equivalente sem recorrer a loops? Se não, existe uma maneira eficiente de fazer isso usando plpgSQL? (ou qualquer outro idioma)