user606521's questions

Eu tenho o seguinte gatilho na tabela "Posts":

create
or replace function update_last_post_series () returns trigger as $ $ begin
update
  "Series"
set
  "lastPostAdded" = now()
where
  id = NEW."SeriesId"
  and NEW."SeriesId" is not null
  and (
    TG_OP = 'INSERT'
    or OLD."SeriesId" is null
    or (
      OLD.status != 'published'
      and NEW.status = 'published'
    )
  )
  and NEW."isPublished" = true
  and NEW.status = 'published';return NEW;end;$ $ language plpgsql;

create trigger update_last_post_series_trg
after
  insert
  or
update
  on "Posts" for each row execute procedure update_last_post_series();

Desde alguns dias (parece que o banco de dados pg foi atualizado para a versão 10.21, mas não tenho certeza de qual versão) o gatilho acima começou a lançar exceções:

POST_CREATE_ERROR record "old" is not assigned yet { error: record "old" is not assigned yet
Jun 28 11:57:26     name: 'error',
Jun 28 11:57:26     length: 547,
Jun 28 11:57:26     severity: 'ERROR',
Jun 28 11:57:26     code: '55000',
Jun 28 11:57:26     detail: 'The tuple structure of a not-yet-assigned record is indeterminate.',
Jun 28 11:57:26     hint: undefined,
Jun 28 11:57:26     position: undefined,
Jun 28 11:57:26     internalPosition: undefined,
Jun 28 11:57:26     internalQuery: undefined,
Jun 28 11:57:26     where: 'SQL statement "update "Series"\n        set "lastPostAdded" = now()\n        where id = NEW."SeriesId" and NEW."SeriesId" is not null\n        and (TG_OP = \'INSERT\' or OLD."SeriesId" is null or (OLD.status != \'published\' and NEW.status = \'published\'))\n        and NEW."isPublished" = true and NEW.status = \'published\'"\nPL/pgSQL function update_last_post_series() line 3 at SQL statement',
Jun 28 11:57:26     schema: undefined,
Jun 28 11:57:26     table: undefined,
Jun 28 11:57:26     column: undefined,
Jun 28 11:57:26     dataType: undefined,
Jun 28 11:57:26     constraint: undefined,
Jun 28 11:57:26     file: 'pl_exec.c',
Jun 28 11:57:26     line: '4932',
Jun 28 11:57:26     routine: 'exec_eval_datum',
Jun 28 11:57:26     sql: 'INSERT INTO "Posts" ..... 

Não faço ideia de por que isso está acontecendo porque parece que a condição antes de acessar o registro OLD não deve permitir que isso aconteça:

TG_OP = 'INSERT'

or OLD."SeriesId" is null -- should never be called if TG_OP = 'INSERT' because first condition is true in this case

or (OLD.status != 'published' and NEW.status = 'published') -- should never be called if TG_OP = 'INSERT' because first condition is true in this case

O que está acontecendo aqui, é possível que o executor da consulta avalie as condições separadas por OR em paralelo? Por que estava funcionando por vários anos até agora?

Usando PG 10.21.

Aqui está o exemplo:

drop table if exists tst;

create table tst (
num integer not null
);

insert into tst values (1), (2), (3);

-- window functions WITH order by clause
select *, max(num) over (partition by true order by num asc), array_agg(num) over (partition by true order by num asc) as test
from tst;

-- window functions WITHOUT order by clause
select *, max(num) over (partition by true), array_agg(num) over (partition by true) as test
from tst;

O resultado é o seguinte:

Por que a ordem por cláusula tem impacto na função de agregação?

Eu tenho dois índices exclusivos em colunas ae b. Eu preciso de algo assim:

insert into my_table (a, b) values (1, 2), (1, 2)
on conflict (a) do update set c = 'a_violation'
on conflict (b) do update set c = 'b_violation'

Então, geralmente, eu quero fazer uma atualização diferente com base no alvo do conflito - a sintaxe acima não é suportada (apenas uma on conflictinstrução é suportada). Existe alguma outra maneira de fazer isso?

Vendo um valor diferente com <@>eearth_distance

with points as (
  select 
    '(1,1)'::point as p1
    ,'(2,2)'::point as p2
)
select 
  ((p1 <@> p2) * 1609.34::double precision) as d1
  ,(earth_distance(ll_to_earth(p1[1], p1[0]), ll_to_earth(p2[1], p2[0]))) as d2
from points

Resultado é:

157224.717583288 157402.326073052

Quase 200m de diferença - por que isso?

Tenho a seguinte tabela:

create table test (
  company_id integer not null, 
  client_id integer not null, 
  client_status text,
  unique (company_id, client_id)
);

insert into test values
  (1, 1, 'y'),    -- company1

  (2, 2, null),   -- company2

  (3, 3, 'n'),    -- company3

  (4, 4, 'y'),    -- company4
  (4, 5, 'n'),

  (5, 6, null),   -- company5
  (5, 7, 'n')
;

Basicamente são 5 empresas diferentes, cada uma tem um ou mais clientes e cada cliente tem status: 'y' ou 'n' (pode ser nulo também).

O que tenho que fazer é selecionar todos os pares (company_id, client_id)para todas as empresas para as quais há pelo menos um cliente cujo status não seja 'n' ('y' ou nulo). Portanto, para os dados de exemplo acima, a saída deve ser:

company_id;client_id
1;1
2;2
4;4
4;5
5;6
5;7

Eu tentei algo com funções de janela, mas não consigo descobrir como comparar o número de TODOS os clientes com o número de clientes com STATUS = 'n'.

select company_id,
count(*) over (partition by company_id) as all_clients_count
from test
-- where all_clients_count != ... ?

Eu descobri como fazer isso, mas não tenho certeza se é o caminho certo:

select sub.company_id, unnest(sub.client_ids)
from (
  select company_id, array_agg(client_id) as client_ids
  from test
  group by company_id
  having count(*) != count( (case when client_status = 'n' then 1 else null end) )
) sub

Tenho a seguinte consulta:

select 
  vec.vec, 
  vec @@ to_tsquery($$'fabr':*A$$),   -- true
  to_tsquery($$'fabr':*A$$),          -- fabr
  vec @@ to_tsquery($$'fabry':*A$$),  -- false
  to_tsquery($$'fabry':*A$$),         -- fabri <-- "i" instead of "y"
  vec @@ to_tsquery($$'fabryc':*A$$), -- true
  to_tsquery($$'fabryc':*A$$)         -- fabryc
from (
  select setweight(to_tsvector('english', coalesce('fabryczna')), 'A') as vec
) vec

É mais detalhada esta consulta converte "fabry" para "fabri":

select to_tsquery($$'fabry':*A$$) -- will return "fabri" instead of "fabry"

1. Isso está acontecendo porque estou usando o ditado "inglês" ao consultar a palavra "polonês"?

2. É possível evitar essa situação sem instalar o dict polonês? Existe alguma opção para apenas forçar pg a corresponder a caracteres EXATOS? (não quero usar like/ilike)

create or replace function test()
returns void as $$
begin
  update tbl set col1 = true where col2 = false;
  -- now I want to raise exception if update query affected more than 2 rows to rollback the update
end;
$$ language plpgsql;

Como posso selecionar o número de linhas afetadas para uma variável na função?

Eu tenho um '9.999999999'::double precisionnúmero e quero convertê-lo decimal(8,2)sem arredondamento. O resultado que estou esperando é 9.99. Até agora tentei:

select trunc('9.999'::double precision::numeric(8,3), 2)

e funciona, mas eu tenho que saber o número de dígitos de precisão para o valor de precisão dupla, porque se houver mais 9, ::numeric(8,3)o arredondará automaticamente para 10.000.

O que posso fazer sobre isso?

Estou tentando colar caracteres chineses no terminal psql:

$ psql
=> \encoding
=> UTF8
# Ctrl+v (艺术与设计)
=> ?????设计

Ao colar no terminal normal (Max OS), funciona bem:

$ # Ctrl+v (艺术与设计)
$ 艺术与设计

O que pode estar errado?

Eu tenho o seguinte código:

create table users (id serial primary key, comments_counter integer default 0);
create table comments (id serial primary key, user_id integer references users(id) not null)

create or replace function users_comments_counter()
returns trigger as $$
begin
  update users set comments_counter = comments_counter + 1
  where id = NEW.user_id;
  return null;
end;
$$ language plpgsql;

create trigger users_comments_counter_trg
after insert on comments
for each row execute procedure users_comments_counter();

É possível que a inserção de uma linha na commentstabela seja lançada could not serialize access due to concurrent update?

Ou talvez em outras palavras: é possível usar tal contador (com QUALQUER um dos níveis de isolamento da transação), evitando situações em que "nova tentativa" (devido ao erro de serialização da transação) é necessária e a consistência dos dados é garantida (o contador se comportará conforme o esperado - irá ser incrementado somente se a linha de comentário for inserida com sucesso).

Eu quero ler a opção de configuração como TEXT, mas a instrução SHOW é estranha porque não retorna nenhuma linha.

create or replace function get_param()
returns text as $$  
begin
  return query show custom.param; -- does not work, because show does not return setof 
end;
$$ language plpgsql;

set custom.param = 'custom value';
select get_param() as param;

Existe alguma outra maneira de fazer isso?

Na verdade, encontrei a resposta em uma pergunta feita por mim :) definir sessão - variável personalizada para armazenar o ID do usuário :

SELECT current_setting('custom.param');

Estou tentando conseguir algo como LEFT JOINcom a função de matriz unnest(). Eu quero que a consulta retorne uma linha com um valor nulo se a matriz estiver vazia. Portanto, usando uma CASEconstrução, eu queria passar um array falso com um único elemento nulo se o array de origem estiver vazio, mas não funciona como esperado:

Consulta 1

select element 
from (
  select array['a']::text[] as arr --< single non-null element
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[] 
    else sub.arr 
    end
  )
) element
-- returns 1 row with element = "a"

Consulta 2

select element 
from (
  select array[]::text[] as arr --< empty array
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[] 
    else sub.arr 
    end
  )
) element
-- returns 0 rows (should return 1 row with element = NULL?)

Consulta 3

select element 
from (
  select array[null]::text[] as arr --< single null element
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[] 
    else sub.arr 
    end
  )
) element
-- return single row with element = NULL

Acabei de descobrir que select array_length(array[]::text[], 1)retorna NULL- minha pergunta é por quê?

Estou tentando encontrar uma representação de string do tstzrange[]tipo (matriz de intervalos de carimbo de data/hora):

select '{[2014-01-01,2015-01-01]}'::tstzrange[];
select '{''[2014-01-01,2015-01-01]''}'::tstzrange[];
select '{[''2014-01-01'',''2015-01-01'']}'::tstzrange[];

Mas cada uma das consultas acima gera um malformed range literalerro.

Eu sei que posso usar, array[tstzrange('2014-01-01','2015-01-01','[]')]mas o que eu preciso é uma representação de string.

create function test(a integer, b integer)
returns table (a integer, b integer) as $$
  begin
    return query select 1, 2;
  end;
$$ language plpgsql;

lança exceção:

ERROR:  parameter name "a" used more than once
CONTEXT:  compilation of PL/pgSQL function "test" near line 1

Porque isso está acontecendo? Achei que o nome do argumento da função não tem nada a ver com o nome da coluna da tabela retornada. Existe uma solução alternativa para ter o argumento da função chamado "a" e também ter a coluna chamada "a" em uma tabela retornada da função?

Eu sei que posso retornar setof record, mas tenho que "definir" explicitamente as colunas retornadas da função na consulta selecionada:

create function test(a integer, b integer)
returns setof record as $$
  begin
    return query select 1, 2;
  end;
$$ language plpgsql;

select * from test(1, 2) as (a integer, b integer)

Inicializar dados de teste:

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE docs (data JSONB NOT NULL DEFAULT '{}');
-- generate 200k documents, ~half with type: "type1" and another half with type: "type2", unique incremented index and random uuid per each row
INSERT INTO docs (data)
SELECT json_build_object('id', gen_random_uuid(), 'type', (CASE WHEN random() > 0.5 THEN 'type1' ELSE 'type2' END) ,'index', n)::JSONB
FROM generate_series(1, 200000) n;
-- inset one more row with explicit uuid to query by it later
INSERT INTO docs (data) VALUES (json_build_object('id', '30e84646-c5c5-492d-b7f7-c884d77d1e0a', 'type', 'type1' ,'index', 200001)::JSONB);

Primeira consulta - filtrar por dados->tipo e limite:

-- FAST ~19ms
EXPLAIN ANALYZE
SELECT * FROM docs
WHERE data @> '{"type": "type1"}'::JSONB
LIMIT 25;
/* "Limit  (cost=0.00..697.12 rows=25 width=90) (actual time=0.029..0.070 rows=25 loops=1)"
   "  ->  Seq Scan on docs  (cost=0.00..5577.00 rows=200 width=90) (actual time=0.028..0.061 rows=25 loops=1)"
   "        Filter: (data @> '{"type": "type1"}'::jsonb)"
   "        Rows Removed by Filter: 17"
   "Planning time: 0.069 ms"
   "Execution time: 0.098 ms" 
*/

Segunda consulta - filtrar por dados->tipo, ordenar por dados->índice e limite

-- SLOW ~250ms
EXPLAIN ANALYZE
SELECT * FROM docs
WHERE data @> '{"type": "type1"}'::JSONB
ORDER BY data->'index' -- added ORDER BY
LIMIT 25;

/* "Limit  (cost=5583.14..5583.21 rows=25 width=90) (actual time=236.750..236.754 rows=25 loops=1)"
   "  ->  Sort  (cost=5583.14..5583.64 rows=200 width=90) (actual time=236.750..236.750 rows=25 loops=1)"
   "        Sort Key: ((data -> 'index'::text))"
   "        Sort Method: top-N heapsort  Memory: 28kB"
   "        ->  Seq Scan on docs  (cost=0.00..5577.50 rows=200 width=90) (actual time=0.020..170.797 rows=100158 loops=1)"
   "              Filter: (data @> '{"type": "type1"}'::jsonb)"
   "              Rows Removed by Filter: 99842"
   "Planning time: 0.075 ms"
   "Execution time: 236.785 ms"
*/

Terceira consulta - igual à Segunda (anterior), mas com índice btree em dados->índice:

CREATE INDEX docs_data_index_idx ON docs ((data->'index'));

-- FAST ~19ms
EXPLAIN ANALYZE
SELECT * FROM docs
WHERE data @> '{"type": "type1"}'::JSONB
ORDER BY data->'index' -- added BTREE index on this field
LIMIT 25;
/* "Limit  (cost=0.42..2473.98 rows=25 width=90) (actual time=0.040..0.125 rows=25 loops=1)"
   "  ->  Index Scan using docs_data_index_idx on docs  (cost=0.42..19788.92 rows=200 width=90) (actual time=0.038..0.119 rows=25 loops=1)"
   "        Filter: (data @> '{"type": "type1"}'::jsonb)"
   "        Rows Removed by Filter: 17"
   "Planning time: 0.127 ms"
   "Execution time: 0.159 ms"
*/

Quarta consulta - agora filtre por data->id e limite = 1:

-- SLOW ~116ms
EXPLAIN ANALYZE
SELECT * FROM docs
WHERE data @> ('{"id": "30e84646-c5c5-492d-b7f7-c884d77d1e0a"}')::JSONB -- querying by "id" field now
LIMIT 1;
/* "Limit  (cost=0.00..27.89 rows=1 width=90) (actual time=97.990..97.990 rows=1 loops=1)"
   "  ->  Seq Scan on docs  (cost=0.00..5577.00 rows=200 width=90) (actual time=97.989..97.989 rows=1 loops=1)"
   "        Filter: (data @> '{"id": "30e84646-c5c5-492d-b7f7-c884d77d1e0a"}'::jsonb)"
   "        Rows Removed by Filter: 189999"
   "Planning time: 0.064 ms"
   "Execution time: 98.012 ms"
*/ 

Quinta consulta - igual à quarta, mas com índice gin (json_path_ops) nos dados:

CREATE INDEX docs_data_idx ON docs USING GIN (data jsonb_path_ops);

-- FAST ~17ms
EXPLAIN ANALYZE
SELECT * FROM docs
WHERE data @> '{"id": "30e84646-c5c5-492d-b7f7-c884d77d1e0a"}'::JSONB -- added gin index with json_path_ops
LIMIT 1;
/* "Limit  (cost=17.55..20.71 rows=1 width=90) (actual time=0.027..0.027 rows=1 loops=1)"
   "  ->  Bitmap Heap Scan on docs  (cost=17.55..649.91 rows=200 width=90) (actual time=0.026..0.026 rows=1 loops=1)"
   "        Recheck Cond: (data @> '{"id": "30e84646-c5c5-492d-b7f7-c884d77d1e0a"}'::jsonb)"
   "        Heap Blocks: exact=1"
   "        ->  Bitmap Index Scan on docs_data_idx  (cost=0.00..17.50 rows=200 width=0) (actual time=0.016..0.016 rows=1 loops=1)"
   "              Index Cond: (data @> '{"id": "30e84646-c5c5-492d-b7f7-c884d77d1e0a"}'::jsonb)"
   "Planning time: 0.095 ms"
   "Execution time: 0.055 ms"
*/

Sexta (e última) consulta - igual à Terceira consulta (consulta por dados->tipo, ordem por dados->índice, limite):

-- SLOW AGAIN! ~224ms
EXPLAIN ANALYZE
SELECT * FROM docs
WHERE data @> '{"type": "type1"}'::JSONB
ORDER BY data->'index'
LIMIT 25;
/* "Limit  (cost=656.06..656.12 rows=25 width=90) (actual time=215.927..215.932 rows=25 loops=1)"
   "  ->  Sort  (cost=656.06..656.56 rows=200 width=90) (actual time=215.925..215.925 rows=25 loops=1)"
   "        Sort Key: ((data -> 'index'::text))"
   "        Sort Method: top-N heapsort  Memory: 28kB"
   "        ->  Bitmap Heap Scan on docs  (cost=17.55..650.41 rows=200 width=90) (actual time=33.134..152.618 rows=100158 loops=1)"
   "              Recheck Cond: (data @> '{"type": "type1"}'::jsonb)"
   "              Heap Blocks: exact=3077"
   "              ->  Bitmap Index Scan on docs_data_idx  (cost=0.00..17.50 rows=200 width=0) (actual time=32.468..32.468 rows=100158 loops=1)"
   "                    Index Cond: (data @> '{"type": "type1"}'::jsonb)"
   "Planning time: 0.157 ms"
   "Execution time: 215.992 ms"
*/

Portanto, parece que a sexta consulta (igual à terceira) é muito mais lenta quando há índice gin na coluna de dados. É provavelmente porque não há muitos valores distintos para o campo data->type (apenas "type1" ou "type2")? O que posso fazer sobre isso? Preciso do gin index para fazer outras consultas que tirem proveito dele...

Eu quero fazer algo assim:

SELECT * 
FROM 
(
  SELECT 1, 2, 3
) AS subquery
WHERE subquery[0] = 1 -- I want to reference cloumn by its index in row

Vamos supor que eu tenha o seguinte pg_trgmíndice na mydata JSONBcoluna:

CREATE TABLE mytable (mydata JSONB);
CREATE INDEX trgm_index_1 ON mytable
USING gin ((mydata#>>'{myfield}') gin_trgm_ops);

E vamos supor que vou inserir os seguintes dados em mytable:

500k rows with mydata = { "myfield": "some_text" }
500k rows with mydata = { "another_field": "some_text" }

Portanto 1M, linhas no total, mas metade delas contém valor que é usado na expressão de índice ( mydata#>>'{myfield}'). Isso significa que o índice usará ~ 50% menos memória no disco do que usaria se todas as linhas contivessem myfieldo campo json?

Desejo armazenar o ID do usuário na variável de sessão personalizada e usá-lo (ler) em procedimentos de gatilho para autorizar ações do usuário. Eu encontrei algo assim:

set session "myapp.user" = '12345';
...
SELECT current_setting('myapp.user');

e parece funcionar - pensei que "myapp.user" deveria ser declarado no arquivo .conf, mas parece que posso criar variáveis ​​de sessão na hora (não alterei o arquivo .conf).

Existe alguma desvantagem em fazer assim?

Eu tenho que tabelas:

users (id serial, coins integer not null default 0)
purchases (id serial, price integer not null, user_id)

Quando uma nova linha é inserida na purchasestabela, quero subtrair amountda user.coinscoluna e garantir que user.coins >= 0(para que o usuário tenha fundos suficientes para fazer uma compra).

Eu quero fazer isso no AFTER INSERTgatilho:

UPDATE users SET coins = coins - NEW.price WHERE id = NEW.user_id;
RETURN NULL;

Minha pergunta é como garantir que o usuário não gaste mais moedas do que possui? Minha primeira ideia é apenas fazer CHECKrestrições na userstabela como esta CHECK (coins >= 0)- isso funcionará com várias inserções simultâneas na userstabela?

Lá, existem dois tipos de consultas que são executadas no meu aplicativo:

DELETE FROM tbl1 WHERE user_id = 1;
INSERT INTO tbl1 VALUES (...), (...), ...;

e

UPDATE tbl2 WHERE id = 1 SET ...;
DELETE FROM tbl3 WHERE tbl2_id = 1;
INSERT INTO tbl3 VALUES (...), (...), ...;
DELETE FROM tbl4 WHERE tbl2_id = 1;
INSERT INTO tbl4 VALUES (...), (...), ...;

O ORM que estou usando define automaticamente o nível de isolamento da transação REPEATABLE READe gostaria de saber se é a escolha certa para obter o melhor desempenho. Meu objetivo é apenas executar tudo ou nada (de 2 transações acima) e não me importo com leituras...

Então, qual nível de isolamento será melhor para melhor desempenho no meu caso?