Estou escrevendo uma coleção de funções armazenadas no Postgres 11.5 e quero fazer isso RAISE EXCEPTIONquando várias pré-condições não forem atendidas. Por exemplo, uma nullstring ou vazia com uma string é necessária, ou um parâmetro inteiro que está fora do intervalo, etc.

Posso RAISE EXCEPTIONfornecer detalhes, dicas e uma mensagem... mas qual intervalo devo usar para o código de erro? Eu verifiquei os documentos, mas não encontrei nenhuma orientação aqui:

https://www.postgresql.org/docs/11/errcodes-appendix.html

Pesquisei no StackOverflow, encontrei uma pergunta semelhante de anos atrás ... mas não uma resposta clara.

Existe algum bloco ou prefixo que seja seguro ou convencional para usar para códigos de erro personalizados retornando de funções/procedimentos armazenados?

Fiz uma pergunta sobre o design da tabela de histórico para exclusões no PG 11.5 e recebi uma sugestão para particionar a tabela. Essa é uma excelente ideia, pois a tabela pode ficar enorme e o conteúdo de informações é baixo. Ou seja, vou acabar querendo limpar os dados.

Quando fui reimplementar a tabela com partição, descobri que o PG (11 e 12) não suporta BEFORE ROWtriggers na tabela de partição master, apenas na partição individual. O que leva a uma tonelada de código de ligação. Existe uma maneira melhor? Tudo que eu tenho o gatilho neste caso é subtrair dois timestamps e armazenar os segundos. 11.5, portanto, nenhuma coluna gerada.

Estou incluído no código, embora seja bastante longo, pois esse é o ponto.

Column order tweaked a bit with Column Tetris search from 
https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/
Totally geeky, but this table could get big, so its worth saving some room.
Note that we can also roll up data and discard a lot of the details in this
table, if we want to save room.
 */
BEGIN;

DROP TABLE IF EXISTS data.need_history CASCADE;

CREATE TABLE IF NOT EXISTS data.need_history (
    id uuid NOT NULL DEFAULT NULL,
    item_id uuid NOT NULL DEFAULT NULL,
    facility_id uuid NOT NULL DEFAULT NULL,
    hsys_id uuid NOT NULL DEFAULT NULL,
    perc_down double precision NOT NULL DEFAULT 0,
    created_dts timestamptz NOT NULL DEFAULT NULL,
    deleted_dts timestamptz NOT NULL DEFAULT NOW(),
    total_qty integer NOT NULL DEFAULT 0,
    sterile_qty integer NOT NULL DEFAULT 0,
    available_qty integer NOT NULL DEFAULT 0,
    still_need_qty integer NOT NULL DEFAULT 0,
    usage_ integer NOT NULL DEFAULT 0,
    duration_seconds int4 NOT NULL DEFAULT 0,
    need_for_case citext NOT NULL DEFAULT NULL,
    status citext NOT NULL DEFAULT NULL,

CONSTRAINT need_history_id_pkey
    PRIMARY KEY (id,deleted_dts)
) PARTITION BY RANGE (deleted_dts);

ALTER TABLE data.need_history OWNER TO user_change_structure;

/* It's a big confusingly documented, but ranges are *inclusive* FROM and *exclusive* TO.
  So, to get January, you want 01-01 to 02-01, not 01-01 to 01-31. In practice,
  this makes the range descriptions a bit nicer, I'd say. */

CREATE TABLE ascendco.need_history_2019_11 PARTITION OF need_history 
    FOR VALUES FROM ('2019-11-01') TO ('2019-12-01');

CREATE TABLE ascendco.need_history_2019_12 PARTITION OF need_history 
    FOR VALUES FROM ('2019-12-01') TO ('2020-01-01');

CREATE TABLE ascendco.need_history_2020_01 PARTITION OF need_history 
    FOR VALUES FROM ('2020-01-01') TO ('2020-02-01');

CREATE TABLE ascendco.need_history_2020_02 PARTITION OF need_history 
    FOR VALUES FROM ('2020-02-01') TO ('2020-03-01');

CREATE TABLE ascendco.need_history_2020_03 PARTITION OF need_history 
    FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');

CREATE TABLE ascendco.need_history_2020_04 PARTITION OF need_history 
    FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');

CREATE TABLE ascendco.need_history_2020_05 PARTITION OF need_history 
    FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');

CREATE TABLE ascendco.need_history_2020_06 PARTITION OF need_history 
    FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');

CREATE TABLE ascendco.need_history_2020_07 PARTITION OF need_history 
    FOR VALUES FROM ('2020-07-01') TO ('2020-08-01');

CREATE TABLE ascendco.need_history_2020_08 PARTITION OF need_history 
    FOR VALUES FROM ('2020-08-01') TO ('2020-09-01');

CREATE TABLE ascendco.need_history_2020_09 PARTITION OF need_history 
    FOR VALUES FROM ('2020-09-01') TO ('2020-10-01');

CREATE TABLE ascendco.need_history_2020_10 PARTITION OF need_history 
    FOR VALUES FROM ('2020-10-01') TO ('2020-11-01');

CREATE TABLE ascendco.need_history_2020_11 PARTITION OF need_history 
    FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');

CREATE TABLE ascendco.need_history_2020_12 PARTITION OF need_history 
    FOR VALUES FROM ('2020-12-01') TO ('2021-01-01');

CREATE TABLE ascendco.need_history_default PARTITION OF need_history DEFAULT;       


COMMIT;

/* Define the trigger function to update the duration count.
  In PG 12 well be able to do this with a generated column...easier. */

CREATE OR REPLACE FUNCTION data.need_history_insert_trigger() 
  RETURNS trigger AS
$BODY$
BEGIN
/* Use DATE_TRUNC seconds to get just the whole seconds part of the timestamps. */
NEW.duration_seconds =
      EXTRACT(EPOCH FROM (
        DATE_TRUNC('second', NEW.deleted_dts) - 
        DATE_TRUNC('second', NEW.created_dts)
        ));
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;


/* 
Bind a trigger event to the function. 
Note: In PG 11 & 12, BEFORE ROW triggers must be applied to the individual partitions, not the partition table.
*/

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2019_11 ON data.need_history_2019_11;
CREATE TRIGGER trigger_need_history_before_insert_2019_11 
    BEFORE INSERT ON data.need_history_2019_11
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2019_12 ON data.need_history_2019_12;
CREATE TRIGGER trigger_need_history_before_insert_2019_12 
    BEFORE INSERT ON data.need_history_2019_12
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();   

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_01 ON data.need_history_2020_01;
CREATE TRIGGER trigger_need_history_before_insert_2020_01 
    BEFORE INSERT ON data.need_history_2020_01
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_02 ON data.need_history_2020_02;
CREATE TRIGGER trigger_need_history_before_insert_2020_02 
    BEFORE INSERT ON data.need_history_2020_02
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_03 ON data.need_history_2020_03;
CREATE TRIGGER trigger_need_history_before_insert_2020_03 
    BEFORE INSERT ON data.need_history_2020_03
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_04 ON data.need_history_2020_04;
CREATE TRIGGER trigger_need_history_before_insert_2020_04 
    BEFORE INSERT ON data.need_history_2020_04
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_05 ON data.need_history_2020_05;
CREATE TRIGGER trigger_need_history_before_insert_2020_05 
    BEFORE INSERT ON data.need_history_2020_05
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_06 ON data.need_history_2020_06;
CREATE TRIGGER trigger_need_history_before_insert_2020_06 
    BEFORE INSERT ON data.need_history_2020_06
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_07 ON data.need_history_2020_07;
CREATE TRIGGER trigger_need_history_before_insert_2020_07 
    BEFORE INSERT ON data.need_history_2020_07
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_08 ON data.need_history_2020_08;
CREATE TRIGGER trigger_need_history_before_insert_2020_08 
    BEFORE INSERT ON data.need_history_2020_08
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_09 ON data.need_history_2020_09;
CREATE TRIGGER trigger_need_history_before_insert_2020_09 
    BEFORE INSERT ON data.need_history_2020_09
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_10 ON data.need_history_2020_10;
CREATE TRIGGER trigger_need_history_before_insert_2020_10 
    BEFORE INSERT ON data.need_history_2020_10
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_11 ON data.need_history_2020_11;
CREATE TRIGGER trigger_need_history_before_insert_2020_11 
    BEFORE INSERT ON data.need_history_2020_11
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_2020_12 ON data.need_history_2020_12;
CREATE TRIGGER trigger_need_history_before_insert_2020_12 
    BEFORE INSERT ON data.need_history_2020_12
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();

DROP TRIGGER IF EXISTS trigger_need_history_before_insert_default ON data.need_history_default;
CREATE TRIGGER trigger_need_history_before_insert_default 
    BEFORE INSERT ON data.need_history_default
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();```


  [1]: https://dba.stackexchange.com/questions/253891/history-table-design-for-deletions-in-pg-11-5

Tenho uma dúvida sobre o design de uma tabela de histórico no Postgres.

A configuração é que eu tenho uma tabela que contém uma lista de necessidades. Um local recalcula os itens sob demanda a cada cinco minutos e envia essa lista para o Postgres. A lista "quente" atual é então acessível a vários aplicativos cliente para puxar. Portanto, a cada cinco minutos, as linhas relacionadas a um local específico são excluídas e repovoadas com o que está quente. Imagine uma tela na parede de um armazém onde as pessoas olham para cima para ver tarefas urgentes, esse tipo de coisa. Esta é mais ou menos uma tabela de filas/avisos, não uma tabela de armazenamento real.

O que estamos rastreando na lista de itens sob demanda são peças específicas, com IDs. É valioso para nós coletar dados (ou pelo menos estatísticas) ao longo do tempo. Podemos descobrir que itens específicos aparecem na lista todos os dias, enquanto outros aparecem apenas raramente. Isso pode ajudar a orientar as escolhas de compra e tal.

Esse é o plano de fundo, estou no Postgres 11.5, então sem colunas geradas. A estratégia descrita abaixo parece correta ou pode ser melhorada? A tabela base é chamada neede a tabela de histórico é chamadaneed_history

need
-- Armazena os dados de interesse
-- Tem um NOW()atribuído como parte created_dtsda INSERTconfiguração da tabela.
-- Tem um PER STATEMENTacionador after para obter a 'tabela de transição' de linhas excluídas.
-- O gatilho de instrução INSERTS INTO need_historypara preservar os dados.

need_history -- É quase um clone de necessidade, mas com alguns campos extras. Especificamente, deleted_dts, atribuído NOW()como padrão quando os dados são inseridos, e duration_secondsque armazena o número ~ de segundos que o registro existiu na tabela de necessidade.
-- Como este é o PG 11.5, não há colunas geradas, então precisarei de um EACH ROWgatilho para calcular duration_seconds.

Mais curto:
need com um gatilho de exclusão de nível de instrução que envia para need_history.

need_history com um gatilho de nível de linha para calcular duration_seconds, pois não tenho colunas geradas disponíveis no PG 11.x.

E, para responder à pergunta óbvia, não, não preciso armazenar o duration_secondsvalor derivado, pois ele pode ser gerado dinamicamente, mas, neste caso, quero desnormalizar para simplificar uma variedade de consultas, classificações e resumos .

Meu cérebro também está dizendo "pergunte sobre fatores de preenchimento ", e não sei por quê.

Abaixo está o código de configuração inicial, caso o resumo acima não esteja claro. Eu não enviei nenhum dado por isso ainda, então pode ter falhas.

Eu ficaria grato por qualquer conselho ou recomendação sobre a melhor forma de fazer isso no Postgres.

BEGIN;

DROP TABLE IF EXISTS data.need CASCADE;

CREATE TABLE IF NOT EXISTS data.need (
    id uuid NOT NULL DEFAULT NULL,
    item_id uuid NOT NULL DEFAULT NULL,
    facility_id uuid NOT NULL DEFAULT NULL,
    hsys_id uuid NOT NULL DEFAULT NULL,
    total_qty integer NOT NULL DEFAULT 0,
    available_qty integer NOT NULL DEFAULT 0,
    sterile_qty integer NOT NULL DEFAULT 0,
    still_need_qty integer NOT NULL DEFAULT 0,
    perc_down double precision NOT NULL DEFAULT '0',
    usage_ integer NOT NULL DEFAULT 0,
    need_for_case citext NOT NULL DEFAULT NULL,
    status citext NOT NULL DEFAULT NULL,
    created_dts timestamptz NOT NULL DEFAULT NOW(),

CONSTRAINT need_id_pkey
    PRIMARY KEY (id)
);


ALTER TABLE data.need OWNER TO user_change_structure;

COMMIT;

/* Define the trigger function to copy the deleted rows to the history table. */
CREATE FUNCTION data.need_delete_copy_to_history()  
  RETURNS trigger AS
$BODY$
BEGIN
        /* need.deleted_dts      is auto-assigned on INSERT over in need, and 
           need.duration_seconds is calculated in an INSERT trigger (PG 11.5, not PG 12, no generated columns). */

   INSERT INTO data.need_history 
            (id,
            item_id,
            facility_id,
            hsys_id,
            total_qty,
            available_qty,
            sterile_qty,
            still_need_qty,
            perc_down,
            usage_,
            need_for_case,
            status,
            created_dts)

     SELECT id,
            item_id,
            facility_id,
            hsys_id,
            total_qty,
            available_qty,
            sterile_qty,
            still_need_qty,
            perc_down,
            usage_,
            need_for_case,
            status,
            created_dts

       FROM deleted_rows;

    RETURN NULL; -- result is ignored since this is an AFTER trigger       
END;
$BODY$
LANGUAGE plpgsql;

 /* Bind a trigger event to the function. */
DROP TRIGGER IF EXISTS trigger_need_after_delete ON data.need;
CREATE TRIGGER trigger_need_after_delete 
    AFTER DELETE ON data.need
    REFERENCING OLD TABLE AS deleted_rows
    FOR EACH STATEMENT EXECUTE FUNCTION data.need_delete_copy_to_history();

/* Define the table. */
BEGIN;

DROP TABLE IF EXISTS data.need_history CASCADE;

CREATE TABLE IF NOT EXISTS data.need_history (
    id uuid NOT NULL DEFAULT NULL,
    item_id uuid NOT NULL DEFAULT NULL,
    facility_id uuid NOT NULL DEFAULT NULL,
    hsys_id uuid NOT NULL DEFAULT NULL,
    total_qty integer NOT NULL DEFAULT 0,
    available_qty integer NOT NULL DEFAULT 0,
    sterile_qty integer NOT NULL DEFAULT 0,
    still_need_qty integer NOT NULL DEFAULT 0,
    perc_down double precision NOT NULL DEFAULT '0',
    usage_ integer NOT NULL DEFAULT 0,
    need_for_case citext NOT NULL DEFAULT NULL,
    status citext NOT NULL DEFAULT NULL,
    created_dts timestamptz NOT NULL DEFAULT NULL,
    deleted_dts timestamptz NOT NULL DEFAULT NOW(),
    duration_seconds int4 NOT NULL DEFAULT 0,

CONSTRAINT need_history_id_pkey
    PRIMARY KEY (id)
);


ALTER TABLE data.need_history OWNER TO user_change_structure;

COMMIT;

/* Define the trigger function to update the duration count.
  In PG 12 we'll be able to do this with a generated column...easier. */

CREATE OR REPLACE FUNCTION data.need_history_insert_trigger() 
  RETURNS trigger AS
$BODY$
BEGIN
/* Use DATE_TRUNC seconds to get just the whole seconds part of the timestamps. */
NEW.duration_seconds =
      EXTRACT(EPOCH FROM (
        DATE_TRUNC('second', NEW.deleted_dts) - 
        DATE_TRUNC('second', NEW.created_dts)
        ));
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;


/* Bind a trigger event to the function. */
DROP TRIGGER IF EXISTS trigger_need_history_before_insert ON data.need_history;
CREATE TRIGGER trigger_need_history_before_insert 
    BEFORE INSERT ON data.need_history
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();```

Eu tenho experimentado índices tsvector para pesquisa de texto completo e vejo que é uma prática comum gerar um vetor de armazenamento em uma coluna do tipo tsvector. Estamos no Postgres 11.4, mas já vi essa mesma prática usada como exemplo para colunas geradas pelo PG 12. (Mais simples do que usar um gatilho para o mesmo propósito.)

Minha pergunta é, qual é o benefício? Eu tentei um índice GIN de expressão no tsvector de um campo de texto e um índice GIN no tsvector armazenado. Com cerca de 8 milhões de linhas localmente, não consegui medir nenhuma diferença de velocidade significativa. Dado que armazenar o vetor como uma coluna e um índice ocupa mais espaço, estou curioso para saber se existem casos óbvios em que o custo extra é justificado. Por exemplo, quando você tem muito mais papéis.

Nota: Estamos armazenando o texto no banco de dados, portanto, esta não é uma daquelas configurações em que você indexa uma página/documento/etc externo sem absorver o texto de origem no banco de dados.

Eu tenho uma tabela com colunas de texto que gostaria de pesquisar por palavra , não por lexema . Mais ao ponto, que eu gostaria de ter indexado por palavra em vez de lexema. Temos despejos de erros com muitas referências de código, que não funcionam bem com nenhum dicionário de linguagem natural.

Existe uma maneira no Postgres de fazer o FTS analisar por limites de palavras sem resolver palavras para lexemas? Se eu tiver que definir uma lista de caracteres de limite e um catálogo de palavras omitidas, tudo bem. Isso requer a criação de algum tipo de dicionário personalizado ou já existe algo assim disponível?

Fico pensando que estou perdendo algo óbvio, e depois não encontro.

Por enquanto, os índices de trigramas são bons, mas eu realmente preferiria o que equivale a um analisador de palavras-chave exclusivo para o texto.

Postgres 11.4 no RDS.

Contagens completas no Postgres podem ser lentas, por razões que são bem compreendidas e muito discutidas. Então, eu tenho usado técnicas de estimativa em vez disso, sempre que possível. Para linhas, pg_stats parece bom, para visualizações, extrair uma estimativa retornada por EXPLAINfunciona bem.

https://www.cybertec-postgresql.com/en/count-made-fast/

Mas e os valores distintos? Aqui, tive muito menos sorte. Às vezes as estimativas estão 100% corretas, às vezes elas estão erradas por fatores de 2 ou 20. Tabelas truncadas parecem ter estimativas muito obsoletas em particular (?).

Acabei de fazer este teste e dei alguns resultados:

analyze assembly_prods; -- Doing an ANLYZE to give pg_stats every help.

select 'count(*) distinct' as method,
        count(*) as count
from (select distinct assembly_id 
      from assembly_prods) d 
union all
select 'n_distinct from pg_stats' as method,
        n_distinct as count
from pg_stats 
where tablename  = 'assembly_prods' and
      attname    = 'assembly_id';

Os resultados:

method                      count
count(*) distinct           28088
n_distinct from pg_stats    13805

Isso é apenas um fator de 2, mas eu pareço muito pior em meus dados. A ponto de não usar estimativas. Existe algo mais que eu possa tentar? Isso é algo que o PG 12 melhora?

Acompanhamento

Eu nunca tinha experimentado SET STATISTICSantes, porque há apenas tantas horas em um dia. Inspirado pela resposta de Laurenz, dei uma olhada rápida. Aqui está um comentário útil da documentação:

https://www.postgresql.org/docs/current/planner-stats.html

A quantidade de informações armazenadas pg_statisticpor ANALYZE, em particular o número máximo de entradas nas most_common_valsmatrizes e histogram_bounds para cada coluna, pode ser definida coluna por coluna usando o ALTER TABLE SET STATISTICScomando ou globalmente definindo a default_statistics_targetvariável de configuração. O limite padrão é atualmente 100 entradas. Aumentar o limite pode permitir que estimativas de planejador mais precisas sejam feitas, principalmente para colunas com distribuições de dados irregulares, ao preço de consumir mais espaço pg_statistice um pouco mais de tempo para calcular as estimativas. Por outro lado, um limite inferior pode ser suficiente para colunas com distribuições de dados simples.

Muitas vezes tenho tabelas com alguns valores comuns e muitos valores raros. Ou o contrário, então o limite certo dependerá. Para aqueles que não usaram SET STATISTICS, ele permite definir a taxa de amostragem como um número alvo de entradas. O padrão é 100, então 1000 deve ser uma fidelidade mais alta. Aqui está o que parece:

ALTER TABLE assembly_prods 
    ALTER COLUMN assembly_id
    SET STATISTICS 1000;

Você pode usar SET STATISTICSem uma tabela ou índice. Aqui está um artigo interessante sobre índices:

https://akorotkov.github.io/blog/2017/05/31/alter-index-weird/

Observe que a documentação atual lista os SET STATISTICSíndices.

Então, experimentei limites de 1, 10, 100, 1000 e 10.000 e obtive esses resultados de uma tabela com 467.767 linhas e 28.088 valores distintos:

Target   Estimate  Difference  Missing
     1   13,657    14,431      51%
    10   13,867    14,221      51%
   100   13,759    14,329      51%
 1,000   24,746     3,342      12%
10,000   28,088         0       0%

Obviamente, você não pode tirar conclusões gerais de um caso, mas SET STATISTICSparece muito útil e ficarei feliz em tê-lo em minha mente. Estou tentado a aumentar um pouco o alvo em geral, pois suspeito que isso ajudaria em muitos dos casos em nosso sistema.

Estou prestes a embarcar em uma revisão geral do nosso código para enviar para o Postgres e quero solicitar alguns comentários antes de continuar.

A configuração aqui é que temos muitos softwares implantados com um banco de dados não Postgres, onde periodicamente enviamos linhas para um banco de dados Postgres central. (RDS 11.4) As chances de atualizarmos todas as cópias implantadas do código simultaneamente são zero . Então, sempre teremos várias versões ativas no campo... às vezes muitas versões diferentes. Tudo bem por si só, mas torna incontrolável inserir declarações ingênuas INSERTno código do cliente. O que eu fiz. Ele só nos mordeu um pouco, mas pode acabar se tornando extremamente doloroso. Qualquer e todas as seguintes alterações de DDL em nosso Postgres central podem interromper pushes de software implantado em uma versão antiga:

• Remover um campo
• Renomear um campo
• Digite novamente um campo
• Adicionar um NOT NULL DEFAULT NULLcampo

Afirmando o óbvio, tenho que mover referências fixas/codificadas do código do cliente para outra coisa . A maioria das pessoas provavelmente tem algum tipo de pilha com um ORM, etc. entre seu aplicativo coletor/campo e o Postgres. Nós não. Então, com muita ajuda das pessoas aqui no SO, eu construí a seguinte solução, usando "hsys" como uma tabela de exemplo.

1. Para cada versão de tabela, crie um tipo personalizado, como hsys_v1.

   CREATE TYPE api.hsys_v1 AS (
       id uuid,
       name_ citext,
       marked_for_deletion boolean);
   

2. Para cada tabela e versão, escreva uma função de manipulação INSERT que aceite uma matriz do tipo personalizado.

   CREATE OR REPLACE FUNCTION ascendco.insert_hsys_v1 (data_in api.hsys_v1[])
     RETURNS int
   AS $BODY$
   
   -- The CTE below is a roundabout way of returning an insertion count from a pure SQL function in Postgres.
   with inserted_rows as (
           INSERT INTO hsys (
               id,
               name_,
               marked_for_deletion)
   
           SELECT
               rows_in.id,
               rows_in.name_,
               rows_in.marked_for_deletion
   
           FROM unnest(data_in) as rows_in
   
           ON CONFLICT(id) DO UPDATE SET
               name_ = EXCLUDED.name_,
               marked_for_deletion = EXCLUDED.marked_for_deletion
   
           returning 1 as row_counter)
   
       select sum(row_counter)::integer from inserted_rows;
   
   $BODY$
   LANGUAGE sql;
   

   A ideia aqui é que quando eu mudar a tabela, eu possa criar um hsys_v2tipo e uma insert_hsys_v2 (hsys_v2[])função para corresponder. Em seguida, os clientes antigos podem continuar enviando no formato antigo, desde que eu reescreva insert_hsys_v1para mapear/converter/coagir as coisas para o novo formato de tabela.

Eu escrevi uma GUI para pegar minhas definições de tabela e colocar um gerador de código no topo algumas semanas atrás. E então eu parei. Percebo que estou me perguntando se estou perdendo algo que devo considerar e espero que alguém aponte uma falha nessa estratégia. Não estou com preguiça de entrar em contato com programadores locais... não há nenhum. (Estou na Austrália rural.)

Se não houver nada de errado com essa estratégia, farei a revisão. Como bônus, o trabalho até agora facilitou a adição de construtores de código para conversões e visualizações personalizadas. Não tenho certeza se eles serão úteis ou não, mas estou gerando-os ao mesmo tempo.

Postgres 11.4 no RDS e 11.5 em casa.

Estou analisando os índices de hash mais de perto hoje porque estou tendo problemas com um índice citext sendo ignorado . E acho que não entendo por que um índice de hash é tão grande. Está levando cerca de 50 bytes/linha quando eu esperava que levasse 10 bytes + alguma sobrecarga.

Eu tenho um banco de dados de exemplo com uma tabela chamada tabela record_changes_log_detail que tem 7.733.552 registros, então ~ 8M. Dentro dessa tabela há um campo citext chamado old_value que é a fonte do índice de hash:

CREATE INDEX record_changes_log_detail_old_value_ix_hash
    ON record_changes_log_detail
    USING hash (old_value);

Aqui está uma verificação do tamanho do índice:

select
'record_changes_log_detail_old_value_ix_hash' as index_name,
pg_relation_size ('record_changes_log_detail_old_value_ix_hash') as bytes,
pg_size_pretty(pg_relation_size ('record_changes_log_detail_old_value_ix_hash')) as pretty

Isso retorna 379.322.368 bytes, ou cerca de 362 MB. Eu cavei um pouco na fonte , e esta bela peça um pouco mais.

Parece que uma entrada de índice de hash para uma linha é um TID emparelhado com a própria chave de hash. E algum tipo de contador de índice dentro da página. São dois inteiros de 4 bytes e, suponho, um inteiro de 1 ou 2 bytes. Como um cálculo ingênuo, 10 bytes * 7.733.552 = 77.335.520. O índice real é aproximadamente 5x maior que isso. Concedido, você precisa de espaço para a própria estrutura do índice, mas não deve levar o custo aproximado por linha de ~ 10 bytes a ~ 50, não é?

Aqui estão os detalhes do índice, lidos usando a extensão pageinspect e depois girados manualmente para legibilidade.

select * 
from hash_metapage_info(get_raw_page('record_changes_log_detail_old_value_ix_hash',0));


magic   105121344
version 4
ntuples 7733552
ffactor 307
bsize   8152
bmsize  4096
bmshift 15
maxbucket   28671
highmask    32767
lowmask 16383
ovflpoint   32
firstfree   17631
nmaps   1
procid  17269
spares  {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,17631,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
mapp    {28673,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}

select *
from hash_page_stats(get_raw_page('record_changes_log_detail_old_value_ix_hash',1));

live_items  2
dead_items  0
page_size   8192
free_size   8108
hasho_prevblkno 28671
hasho_nextblkno 4294967295
hasho_bucket    0
hasho_flag  2
hasho_page_id   65408

Executando em RDS com cerca de 32 milhões de linhas.

PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Também testando localmente no macOS com cerca de 8 milhões de linhas.

PostgreSQL 11.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit

Eu tenho uma coluna chamada old_valueque é do tipo citext. Eu já perguntei sobre isso, mas postei muitos dos meus passos de descoberta ao longo do caminho . Aqui está uma versão resumida que eu espero que chegue ao ponto.

Fundo

Eu tenho uma tabela de log de alterações de campo chamada record_changes_log_detail com 32 milhões de linhas e em crescimento que inclui um campo citext chamado old_value.

Os dados são muito distorcidos. A maioria dos valores são menos de uma dúzia de caracteres, alguns são mais de 5.000.

O Postgres engasga com valores grandes com um erro sobre as entradas da árvore B serem limitadas a 2172 caracteres. Portanto, acredito que para uma árvore B, preciso substringir o valor de origem.

O interesse principal dos meus usuários é uma pesquisa =, uma pesquisa que começa com e, às vezes, uma pesquisa contém esta substring. Então = string% e %string%

Metas

Crie um índice que suporte essas pesquisas que o planejador usa.

Tentou e falhou

Uma árvore B reta falha ao construir, em alguns casos, devido a valores longos.

Uma expressão B-tree como esta é construída, mas não é usada

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON  record_changes_log_detail 
    USING btree (substring(old_value,1,1024));

Adicionar text_pattern_opts não ajuda.

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON  record_changes_log_detail 
    USING btree (substring(old_value,1,1024) text_pattern_opts);

Tentei e funciona parcialmente

Um índice de hash funciona, mas apenas para igualdade. (Como diz na lata.)

Este é o mais próximo que cheguei do sucesso:

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON record_changes_log_detail 
    USING btree (old_value citext_pattern_ops);

Isso funciona para qualidade, mas não para LIKE. As notas de lançamento do PG 11 dizem que deve funcionar para LIKE:

https://www.postgresql.org/docs/11/release-11.html

Por "trabalho" quero dizer "o índice é usado".

Não consegui substring com sucesso com essa abordagem.

O que as pessoas fazem nesta situação com campos de citexto?