C C's questions

IBM DB2 on Cloud, versão 11.5.9.0

Tentando criar um gatilho em uma tabela ( os nomes dos objetos são inventados, para simplificar ):

CREATE OR REPLACE TRIGGER MY_TRIGGER
AFTER UPDATE OR INSERT ON MY_TABLE
REFERENCING NEW TABLE AS NEW_STUFF
FOR EACH STATEMENT
BEGIN ATOMIC
    DELETE FROM TABLE_A WHERE P_N IN (SELECT DISTINCT P_N FROM NEW_STUFF);
    INSERT INTO TABLE_A SELECT * FROM VIEW_B WHERE P_N IN (SELECT DISTINCT P_N FROM NEW_STUFF);
END;

A visão VIEW_B tem TABLE_A como referência em sua definição.

Estou recebendo este erro:

SQL Error [42601]: An unexpected token "OR INSERT" was found following "".  
Expected tokens may include:  "".. 
SQLCODE=-104, SQLSTATE=42601

Se eu criar o mesmo gatilho apenas com AFTER UPDATE ON MY_TABLE ou apenas com AFTER INSERT ON MY_TABLE, ele funcionará bem. Ele falha quando eu especifico AFTER UPDATE OR INSERT .

Se eu tentar BEGIN em vez de BEGIN ATOMIC, recebo um erro diferente:

SQL Error [42898]: The definition of trigger "MY_SCHEMA.MY_TRIGGER" includes an 
invalid use of correlation name or transition table name "NEW_STUFF".
Reason code="4".. SQLCODE=-696, SQLSTATE=42898

Estudei a documentação e os poucos exemplos que consegui encontrar. Se alguém souber por que a combinação de UPDATE ou INSERT está causando falha, agradeceria qualquer ajuda.

Eu sou um novato para SQL e PostgreSQL. Estou tentando descobrir como obter esse tipo de consulta para utilizar um índice para a operação do trigrama. UsandoPostgreSQL 12.7 on x86_64-pc-linux-gnu

A ideia básica é pegar uma frase de pesquisa, dividi-la em palavras distintas e então ver quantas correspondências de 'semelhança' encontramos na coluna do banco de dados de nomes prontos para pesquisa. Quanto mais palavras tiverem semelhança com palavras encontradas no nome pronto para pesquisa, maior será a pontuação. Também consideramos a frase de pesquisa geral em relação ao nome original, como um multiplicador de um 'aumento' ao peso.

A tabela dpl_base tem 71.000 linhas e se parece com isso:

A tabela dpl_codes tem 100 linhas e se parece com isso:

Até agora tentei os dois:

create index trgm_idx_gist_dpl_base on dpl_base using gist (denied_name_searchable, denied_name_original gist_trgm_ops);    
create index trgm_idx_gin_dpl_base on dpl_base using gin (denied_name_searchable, denied_name_original gin_trgm_ops);

Junto com vários outros índices 'padrão'. Com ou sem os índices, a consulta EXPLAIN ANALYZE fornece o mesmo plano exato. Assim, os índices parecem não fazer diferença. As consultas são executadas rapidamente, geralmente em menos de 3 segundos. Talvez eu esteja perseguindo algo que não preciso... estou apenas tentando aprender como indexar corretamente para uma consulta deste design:

SET pg_trgm.similarity_threshold = 0.35;
SELECT
/* create weighting value for the distinct-word hits within the SEARCHABLE column */
/* multiply by the similarity value for the original search phrase, against the ORIGINAL column */
(
  ('BAD' % ANY(STRING_TO_ARRAY(UPPER(DPLB.DENIED_NAME_SEARCHABLE),' ')))::int + 
  ('ACTOR' % ANY(STRING_TO_ARRAY(UPPER(DPLB.DENIED_NAME_SEARCHABLE),' ')))::int
) 
* (-(DPLB.DENIED_NAME_ORIGINAL <-> 'Bad Actor') + 1) AS WEIGHT,
/* add in the remaining columns from our two tables */
DPLB.DENIED_NAME_ORIGINAL, DPLC.DENIAL_REASON
FROM DPL_BASE DPLB 
INNER JOIN DPL_CODES DPLC ON DPLB.DENIAL_CODE = DPLC.DENIAL_CODE 
WHERE
/* must have at least one hit from our distinct words, in the SEARCHABLE column */
( 
  ('Bad' % ANY(STRING_TO_ARRAY(UPPER(DPLB.DENIED_NAME_SEARCHABLE),' ')))::int + 
  ('Actor' % ANY(STRING_TO_ARRAY(UPPER(DPLB.DENIED_NAME_SEARCHABLE),' ')))::int
) > 0 
ORDER BY WEIGHT DESC, DPLB.DENIED_NAME_ORIGINAL ASC;

Aqui está um exemplo de um plano de consulta. Quaisquer dicas ou sugestões sobre (a) maneira correta de indexar e/ou (b) melhor design ou otimização de consulta - seriam muito apreciadas.

|QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                       |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Gather Merge  (cost=9448.31..11733.27 rows=19584 width=62) (actual time=525.228..529.633 rows=204 loops=1)                                                                                                                                                                                                                                                                                       |
|  Workers Planned: 2                                                                                                                                                                                                                                                                                                                                                                             |
|  Workers Launched: 2                                                                                                                                                                                                                                                                                                                                                                            |
|  ->  Sort  (cost=8448.29..8472.77 rows=9792 width=62) (actual time=519.954..520.104 rows=68 loops=3)                                                                                                                                                                                                                                                                                            |
|        Sort Key: (((((('YOUTH'::text % ANY (string_to_array(upper((dplb.denied_name_searchable)::text), ' '::text))))::integer + (('SOCIETY'::text % ANY (string_to_array(upper((dplb.denied_name_searchable)::text), ' '::text))))::integer))::double precision * ((- ((dplb.denied_name_original)::text <-> 'Youth Society'::text)) + '1'::double precision))) DESC, dplb.denied_name_original|
|        Sort Method: quicksort  Memory: 34kB                                                                                                                                                                                                                                                                                                                                                     |
|        Worker 0:  Sort Method: quicksort  Memory: 34kB                                                                                                                                                                                                                                                                                                                                          |
|        Worker 1:  Sort Method: quicksort  Memory: 34kB                                                                                                                                                                                                                                                                                                                                          |
|        ->  Hash Join  (cost=4.25..7799.21 rows=9792 width=62) (actual time=23.524..519.630 rows=68 loops=3)                                                                                                                                                                                                                                                                                     |
|              Hash Cond: (dplb.denial_code = dplc.denial_code)                                                                                                                                                                                                                                                                                                                                   |
|              ->  Parallel Seq Scan on dpl_base dplb  (cost=0.00..7229.60 rows=9792 width=70) (actual time=22.937..516.516 rows=68 loops=3)                                                                                                                                                                                                                                                      |
|                    Filter: (((('YOUTH'::text % ANY (string_to_array(upper((denied_name_searchable)::text), ' '::text))))::integer + (('SOCIETY'::text % ANY (string_to_array(upper((denied_name_searchable)::text), ' '::text))))::integer) > 0)                                                                                                                                                |
|                    Rows Removed by Filter: 23432                                                                                                                                                                                                                                                                                                                                                |
|              ->  Hash  (cost=3.00..3.00 rows=100 width=38) (actual time=0.401..0.407 rows=100 loops=3)                                                                                                                                                                                                                                                                                          |
|                    Buckets: 1024  Batches: 1  Memory Usage: 15kB                                                                                                                                                                                                                                                                                                                                |
|                    ->  Seq Scan on dpl_codes dplc  (cost=0.00..3.00 rows=100 width=38) (actual time=0.044..0.216 rows=100 loops=3)                                                                                                                                                                                                                                                              |
|Planning Time: 0.399 ms                                                                                                                                                                                                                                                                                                                                                                          |
|Execution Time: 530.078 ms