All perguntas(dba)

Eu queria criar uma visão que somasse uma tabela comum a vários esquemas, conforme expresso aqui , e incluísse o nome do esquema ou uma expressão derivada como uma coluna, então criei uma visão que inclui o nome do esquema como um valor constante, por usando o quote_literalpara colocar o nome do esquema entre aspas simples.

Pode quote_literalnão ser a função correta aqui, mas tudo o que eu faço fica entre aspas duplas.

CREATE OR REPLACE FUNCTION create_summary_view_quoted(
   created_view text,
   common_view text,
   VARIADIC schemas text[]
) RETURNS text
   LANGUAGE sql AS
$$SELECT format('CREATE VIEW %I AS ', created_view) ||
         string_agg(
            format('SELECT %I as source_schema, * FROM %I.%I ', quote_literal(schemas[i]) , schemas[i], common_view),
            ' UNION ALL '
         )
FROM generate_series(1, cardinality(schemas)) AS i$$;
")

Aplicando-o à consulta

SELECT create_summary_view_quoted('newv', 'oldv', 's1', 's2', 's3');

produz

create view newv as
select
    "'s1'" as source_schema,
    *
from
    s1.oldv
union all
select
    "'s2'" as source_schema,
    *
from
    s2.oldv
union all
select
    "'s3'" as source_schema,
    *
from
    s3.oldv

O problema são as aspas duplas em torno da constante entre aspas simples que faz com que o Postgres trate como um nome de coluna e vomite.

Corrigi o problema de geração de código usando aspas simples duplas no primeiro espaço reservado e descartando a quote_literalfunção,

ou seja, substituir

format('SELECT %I as source_schema, * FROM %I.%I ', quote_literal(schemas[i]) , schemas[i], common_view),

com

format('SELECT ''%I'' as source_schema, * FROM %I.%I ', schemas[i] , schemas[i], common_view),

Meu problema não é tanto, quote_literalmas como a função sempre envolve a saída de qualquer função que envolve uma string entre aspas simples e aspas duplas.

Mesmo quando usei uma função para retirar um sufixo inicial nos nomes dos esquemas porque eles são redundantes, a saída entre aspas simples é colocada entre aspas duplas.

por exemplo, quote_nullable(right(schemas[i],-10))a string resultante fica entre aspas duplas.

Eu poderia aplicar o ||operador em algum lugar, mas prefiro uma função de cotação.

O Postgres não possui uma função integrada de aspas simples?

Há alguma sintaxe SQL necessária que estou faltando?

É uma peculiaridade do Postgres?

Esta é mais uma pergunta do tipo "por favor, confirme/corrija meu entendimento".

Histórico Eu me conecto ao MySQL usando JDBC, o pool de conexões tem cerca de 250 conexões, a maioria das quais parece conectada persistentemente mesmo após o encerramento da consulta - todas usam a mesma conta de usuário. O procedimento armazenado para o qual desejo usar uma tabela temporária é chamado pelo código Java e executa uma transformação em uma coluna JSON para convertê-la em uma linha para uma tabela diferente. A transformação é relativamente cara, executada potencialmente em milhares de linhas, por isso não desejo realizar a transformação mais de uma vez. Para contornar isso, minha solução proposta é transformar os dados em uma tabela temporária, realizar minhas consultas estatísticas em torno dos dados agora transformados e, em seguida, inserir esse atacado na tabela de destino.

Pergunta Eu li que as tabelas temporárias estão vinculadas ao User . Estou certo ao teorizar que, como as conexões aparentemente nunca são encerradas e todas usam a mesma conta de usuário, a tabela temporária criada no procedimento seria compartilhada por todas as conexões e consultas (e assim os dados poderiam ser acessados ​​por outras conexões executando o mesmo procedimento ao mesmo tempo)?

Limitações O que eu gostaria de fazer é transmitir os dados originais ou realizar a transformação e transmitir esses dados de volta para Java. No entanto, estamos lidando com cerca de algumas centenas de megabytes de dados de cada vez e a transferência de dados de volta para Java parece bloquear o banco de dados até que a transmissão seja concluída. (Não sei por que, se alguém tiver uma recomendação para corrigir isso, por favor me avise)

Então, fico com a única outra solução que consegui pensar: não transmitir os dados de origem, mas realizar a transformação, coletar os dados agregados necessários e, em seguida, inserir os dados transformados no destino sem que eles saiam do banco de dados servidor.

desde já, obrigado

Eu crio spfile a partir do pfie, mas depois de reiniciar o valor do spfile fica vazio

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string


select value from v$parameter where name='spfile';

VALUE
NULL

Em seguida, crie spfile a partir do pfile

create spfile from pfile

comece

spfile vazio novamente

Estou preparando um banco de dados PostgreSQL 9.5 legado para atualização.

Preciso remover OIDs de tabelas sem bloqueá-las por longos períodos de tempo. Desenvolvi uma estratégia que parece funcionar, mas gostaria que os especialistas avaliassem, já que estou planejando fazer coisas nas tabelas do sistema que geralmente são desaprovadas.

Antes de executar o pg_repack eu realizo estas modificações:

mydata=# update pg_class set relhasoids = false where oid = 'some_schema.a_very_large_table_with_oids'::regclass;
UPDATE 1
mydata=# delete from pg_attribute where attrelid = 'some_schema.a_very_large_table_with_oids'::regclass and attname = 'oid';
DELETE 1
mydata=# \d+ some_schema.a_very_large_table_with_oids;
        Table "some_schema.a_very_large_table_with_oids"
 Column | Type | Modifiers | Storage  | Stats target | Description 
--------+------+-----------+----------+--------------+-------------
 k      | text | not null  | extended |              | 
 v      | text |           | extended |              | 
Indexes:
    "a_very_large_table_with_oids_pkey" PRIMARY KEY, btree (k)

mydata=# select oid,* from some_schema.a_very_large_table_with_oids;
ERROR:  column "oid" does not exist

Até agora tudo bem. Posso inserir atualizações e excluir linhas, mas a estrutura da tabela no disco permanece inalterada. Vou precisar consertar isso.

Então, depois que essas modificações forem feitas, eu reempacotei a tabela com vanilla pg_repack. Isso copia os dados para novas tabelas no disco, sem oids.

Antes de começar a realizar essas operações on-line nos dados de produção, eu queria alguns especialistas nesse processo, já que se trata de algo de missão crítica.

Nas tabelas muito grandes, ficaremos neste estado de limbo por um longo período de tempo, onde pg_class e pg_attribute terão essas modificações forçadas enquanto pg_repack faz sua mágica.

Há algo com que se preocupar se inserir/atualizar/excluir parece estar funcionando?

Minhas inserções/atualizações/exclusões nas tabelas enquanto o pg_repack está em execução parecem funcionar bem.

Também tentei isso em tabelas que possuem tabelas de brinde anexadas. À primeira vista tudo parece estar em ordem.

Esta é uma operação destrutiva e não estou usando o canonical ALTER TABLE ... SET WITHOUT OIDS. Quero ter certeza de que não estou me preparando para um problema no futuro se houver algum detalhe que perdi (como durante a restauração de backup ou replicação).

Por favor, avise.

ATUALIZAR:

A excelente resposta de Laurenz me deu duas coisas adicionais nas quais não pensei: a verificação de dependência e a invalidação de cache/plano. Trabalhá-los no plano será importante. Executar pequenos vácuos para limpar as colunas obsoletas é uma boa posição de reserva, mas com base no que vejo acontecendo com o pg_repack, acho que isso resultará na mesma coisa... Os logs do pg_repack expõem todo o SQL envolvido na cópia do tabela e trocando definições:

mydata=# begin;
BEGIN
mydata=# ALTER TABLE perm.permission_cache SET WITHOUT OIDS;
^CCancel request sent
ERROR:  canceling statement due to user request
mydata=# rollback;
ROLLBACK
mydata=# \d+ perm.permission_cache
                                                       Table "perm.permission_cache"
    Column    |           Type           |                        Modifiers                        | Storage  | Stats target | Description 
--------------+--------------------------+---------------------------------------------------------+----------+--------------+-------------
 id           | integer                  | not null default nextval('perm.cache_id_seq'::regclass) | plain    |              | 
 company_uuid | uniqueidentifier         | not null                                                | plain    |              | 
 user_uuid    | uniqueidentifier         | not null                                                | plain    |              | 
 value        | boolean                  | not null                                                | plain    |              | 
 cache_date   | timestamp with time zone | not null default now()                                  | plain    |              | 
 token_name   | character varying(255)   |                                                         | extended |              | 
Indexes:
    "cache_id_pkey" PRIMARY KEY, btree (id)
    "permission_cache_user_token_idx" UNIQUE, btree (user_uuid, token_name)
Foreign-key constraints:
    "company_uuid_fkey" FOREIGN KEY (company_uuid) REFERENCES company_table(company_uuid) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
    "user_uuid_fkey" FOREIGN KEY (user_uuid) REFERENCES user_table(user_uuid) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
Has OIDs: yes

mydata=# \e
BEGIN
ALTER TABLE
UPDATE 1
DELETE 1
COMMIT
mydata=# \q

$ pg_repack -h 127.0.0.1 -d mydata -Upostgres -t 'perm.permission_cache' -e
LOG: (query) SET search_path TO pg_catalog, pg_temp, public
LOG: (query) SET search_path TO pg_catalog, pg_temp, public
LOG: (query) select repack.version(), repack.version_sql()
LOG: (query) SET statement_timeout = 0
LOG: (query) SET search_path = pg_catalog, pg_temp, public
LOG: (query) SET client_min_messages = warning
LOG: (query) SELECT t.*, coalesce(v.tablespace, t.tablespace_orig) as tablespace_dest FROM repack.tables t,  (VALUES (quote_ident($1::text))) as v (tablespace) WHERE (relid = $2::regclass) ORDER BY t.relname, t.schemaname
LOG:    (param:0) = (null)
LOG:    (param:1) = perm.permission_cache
INFO: repacking table "perm.permission_cache"
LOG: (query) SELECT pg_try_advisory_lock($1, CAST(-2147483648 + $2::bigint AS integer))
LOG:    (param:0) = 16185446
LOG:    (param:1) = 1046889774
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE perm.permission_cache IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = $1 AND NOT indisvalid
LOG:    (param:0) = 1046889774
LOG: (query) SELECT indexrelid, repack.repack_indexdef(indexrelid, indrelid, $2, FALSE)  FROM pg_index WHERE indrelid = $1 AND indisvalid
LOG:    (param:0) = 1046889774
LOG:    (param:1) = (null)
LOG: (query) SELECT repack.conflicted_triggers($1)
LOG:    (param:0) = 1046889774
LOG: (query) CREATE TYPE repack.pk_1046889774 AS (id integer)
LOG: (query) CREATE TABLE repack.log_1046889774 (id bigserial PRIMARY KEY, pk repack.pk_1046889774, row perm.permission_cache)
LOG: (query) CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON perm.permission_cache FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('INSERT INTO repack.log_1046889774(pk, row) VALUES( CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.id)::repack.pk_1046889774) END, $2)')
LOG: (query) ALTER TABLE perm.permission_cache ENABLE ALWAYS TRIGGER repack_trigger
LOG: (query) SELECT repack.disable_autovacuum('repack.log_1046889774')
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SELECT pg_backend_pid()
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'relation' AND granted = false AND relation = 1046889774 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL SERIALIZABLE
LOG: (query) SELECT set_config('work_mem', current_setting('maintenance_work_mem'), true)
LOG: (query) SELECT coalesce(array_agg(l.virtualtransaction), '{}')   FROM pg_locks AS l   LEFT JOIN pg_stat_activity AS a     ON l.pid = a.pid   LEFT JOIN pg_database AS d     ON a.datid = d.oid   WHERE l.locktype = 'virtualxid'   AND l.pid NOT IN (pg_backend_pid(), $1)   AND (l.virtualxid, l.virtualtransaction) <> ('1/1', '-1/0')   AND (a.application_name IS NULL OR a.application_name <> $2)  AND a.query !~* E'^\\s*vacuum\\s+'   AND a.query !~ E'^autovacuum: '   AND ((d.datname IS NULL OR d.datname = current_database()) OR l.database = 0)
LOG:    (param:0) = 11918
LOG:    (param:1) = pg_repack
LOG: (query) DELETE FROM repack.log_1046889774
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'relation' AND granted = false AND relation = 1046889774 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE perm.permission_cache IN ACCESS SHARE MODE
LOG: (query) RESET statement_timeout
LOG: (query) CREATE TABLE repack.table_1046889774 WITH (oids = false) TABLESPACE pg_default AS SELECT id,company_uuid,user_uuid,NULL::integer AS "........pg.dropped.4........",value,cache_date,token_name FROM ONLY perm.permission_cache WITH NO DATA
LOG: (query) INSERT INTO repack.table_1046889774 SELECT id,company_uuid,user_uuid,NULL::integer AS "........pg.dropped.4........",value,cache_date,token_name FROM ONLY perm.permission_cache
LOG: (query) ALTER TABLE repack.table_1046889774 DROP COLUMN "........pg.dropped.4........"
LOG: (query) SELECT repack.disable_autovacuum('repack.table_1046889774')
LOG: (query) COMMIT
LOG: (query) CREATE UNIQUE INDEX index_1046889779 ON repack.table_1046889774 USING btree (id)
LOG: (query) CREATE UNIQUE INDEX index_1050932923 ON repack.table_1046889774 USING btree (user_uuid, token_name)
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG:    (param:0) = SELECT * FROM repack.log_1046889774 ORDER BY id LIMIT $1
LOG:    (param:1) = INSERT INTO repack.table_1046889774 VALUES ($1.*)
LOG:    (param:2) = DELETE FROM repack.table_1046889774 WHERE (id) = ($1.id)
LOG:    (param:3) = UPDATE repack.table_1046889774 SET (id, company_uuid, user_uuid, value, cache_date, token_name) = ($2.id, $2.company_uuid, $2.user_uuid, $2.value, $2.cache_date, $2.token_name) WHERE (id) = ($1.id)
LOG:    (param:4) = DELETE FROM repack.log_1046889774 WHERE id IN (
LOG:    (param:5) = 1000
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG:    (param:0) = SELECT * FROM repack.log_1046889774 ORDER BY id LIMIT $1
LOG:    (param:1) = INSERT INTO repack.table_1046889774 VALUES ($1.*)
LOG:    (param:2) = DELETE FROM repack.table_1046889774 WHERE (id) = ($1.id)
LOG:    (param:3) = UPDATE repack.table_1046889774 SET (id, company_uuid, user_uuid, value, cache_date, token_name) = ($2.id, $2.company_uuid, $2.user_uuid, $2.value, $2.cache_date, $2.token_name) WHERE (id) = ($1.id)
LOG:    (param:4) = DELETE FROM repack.log_1046889774 WHERE id IN (
LOG:    (param:5) = 1000
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'virtualxid' AND pid <> pg_backend_pid() AND virtualtransaction = ANY($1)
LOG:    (param:0) = {}
LOG: (query) SAVEPOINT repack_sp1
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE perm.permission_cache IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG:    (param:0) = SELECT * FROM repack.log_1046889774 ORDER BY id LIMIT $1
LOG:    (param:1) = INSERT INTO repack.table_1046889774 VALUES ($1.*)
LOG:    (param:2) = DELETE FROM repack.table_1046889774 WHERE (id) = ($1.id)
LOG:    (param:3) = UPDATE repack.table_1046889774 SET (id, company_uuid, user_uuid, value, cache_date, token_name) = ($2.id, $2.company_uuid, $2.user_uuid, $2.value, $2.cache_date, $2.token_name) WHERE (id) = ($1.id)
LOG:    (param:4) = DELETE FROM repack.log_1046889774 WHERE id IN (
LOG:    (param:5) = 0
LOG: (query) SELECT repack.repack_swap($1)
LOG:    (param:0) = 1046889774
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SAVEPOINT repack_sp1
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE perm.permission_cache IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT repack.repack_drop($1, $2)
LOG:    (param:0) = 1046889774
LOG:    (param:1) = 4
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) ANALYZE perm.permission_cache
LOG: (query) COMMIT
LOG: (query) SELECT pg_advisory_unlock($1, CAST(-2147483648 + $2::bigint AS integer))
LOG:    (param:0) = 16185446
LOG:    (param:1) = 1046889774

Não parece que os OIDs estejam sobrevivendo à cópia como Laurenz supôs. Para mim, seria preferível usar pg_repack, pois ele pode ser executado sem supervisão. Isso não seria tão bom quanto VACUUMs incrementais para reescrever a tabela no disco sem OIDs? Ainda posso estar faltando um detalhe.

Considerando uma consulta com CTEs:

WITH cte_0 AS (
          SELECT SUM(col_0)
          FROM table_0
          WHERE condition_00
          AND condition_01
    ), 
    cte_1 AS (
          SELECT SUM(col_1)
          FROM table_0
          WHERE condition_10
          AND condition_11
    )
    SELECT (
          ((SELECT * FROM ct_0) * 100) / (SELECT * FROM ct_1),
          ((SELECT * FROM ct_0) + 100) * (SELECT * FORM ct_1)
    ) FROM cte_0, cte_1

Seria correto deixar o último FROMde fora? E recebo uma tupla de dois números com uma consulta como essa? Ou preciso de uma nova tabela da qual possa SELECT *obter dois números?

Acabei de iniciar meu primeiro projeto PostgreSQL e vi diferentes estilos de escrevê-lo. Todos os comentários sobre o estilo são calorosamente bem-vindos. Não encontrei nenhuma plataforma PostgreSQL online adequada para testar consultas. Há alguns?

Pergunta editada para incluir condições e um resultado mais complicado.

Eu tenho uma consulta semelhante à seguinte:

FROM example_table
WHERE 
    `date` BETWEEN '2023-11-26' AND '2023-11-28'
    AND location_id IN (3, 4, 6, 7, 8, 10, 11, 12, 14, 18, 19, 22, 23, 24, 28, 29, 30, 31, 32, 36, 39, 40, 41, 43, 45, 46, 48, 49, 50, 51, 52, 54, 55, 56, 57, 59, 60, 61, 62, 68, 69, 75, 121)
    AND ( `type` IS NULL OR ( `type` IN ('type1', 'type2', 'type3') ) )
GROUP BY location_id;

Meu entendimento é que, ao criar um índice multicoluna, a coluna com maior cardinalidade/seletividade vai primeiro. Tentei testar o desempenho com duas chaves de índice:

1. (data, location_id, tipo, valor)
2. (location_id, data, tipo, valor)

Na minha tabela real, tenho 11.833 valores exclusivos na coluna de data e apenas 99 em location_id. Atualmente, existem mais de 63 milhões de linhas.

No entanto, o MySQL 8 prefere usar aquele que começa com location_id. Mesmo quando tento FORCE INDEXe EXPLAIN ANALYZE, ele mostra um custo/tempo maior daquele que começa com date.

O que poderia estar acontecendo?

EDITAR:

EXPLICAR ANÁLISE:

1. data primeiro índice

    -> Group aggregate: sum(ledger_entries.amount_cents)  (cost=1897 rows=6236) (actual time=0.167..4.67 rows=43 loops=1)
        -> Filter: ((ledger_entries.`date` = DATE'2023-11-28') and (ledger_entries.location_id in (3,4,6,7,8,10,11,12,14,18,19,22,23,24,28,29,30,31,32,36,39,40,41,43,45,46,48,49,50,51,52,54,55,56,57,59,60,61,62,68,69,75,121)) and ((ledger_entries.`type` is null) or (ledger_entries.`type` in ('Procedure','Adjustment','AncillarySale'))))  (cost=1273 rows=6236) (actual time=0.0221..4.09 rows=6192 loops=1)
            -> Covering index range scan on ledger_entries using index_le_date_location_type_amount_cents over (date = '2023-11-28' AND location_id = 3 AND type = NULL) OR (date = '2023-11-28' AND location_id = 3 AND type = 'Adjustment') OR (170 more)  (cost=1273 rows=6236) (actual time=0.02..2.83 rows=6192 loops=1)

2. primeiro índice de localização

    -> Group aggregate: sum(ledger_entries.amount_cents)  (cost=1888 rows=6236) (actual time=0.171..4.74 rows=43 loops=1)
        -> Filter: ((ledger_entries.`date` = DATE'2023-11-28') and (ledger_entries.location_id in (3,4,6,7,8,10,11,12,14,18,19,22,23,24,28,29,30,31,32,36,39,40,41,43,45,46,48,49,50,51,52,54,55,56,57,59,60,61,62,68,69,75,121)) and ((ledger_entries.`type` is null) or (ledger_entries.`type` in ('Procedure','Adjustment','AncillarySale'))))  (cost=1265 rows=6236) (actual time=0.0244..4.15 rows=6192 loops=1)
            -> Covering index range scan on ledger_entries using ledger_entries_location_date_type_amount_cents over (location_id = 3 AND date = '2023-11-28' AND type = NULL) OR (location_id = 3 AND date = '2023-11-28' AND type = 'Adjustment') OR (170 more)  (cost=1265 rows=6236) (actual time=0.022..2.91 rows=6192 loops=1)

Estou tentando selecionar agg_costum determinado ide subtrair isso de todas as outras linhas. Em seguida, divida o resultado em duas colunas: downstreampara resultados negativos, upstreampara resultados positivos. Gostaria de gerar uma tabela separada para cada subtração.

Dados da tabela de amostra:

Exemplo de cálculo para dado id = 2.5:

• origem = 2,5 (29523,723) - origem (0) = 29523,723
• 2,1 = 2,5 (29523,723) - 2,1 (351,226) = 29172,49658
• ... etc.

Resultado desejado:

Não sou especialista em DBMS e estou tentando construir um sistema de alerta que possa se anexar a muitas tabelas , então imaginei que fazer a pergunta ajudaria minha arquitetura.

O que pode me confundir um pouco é que você pode ter vários alertas anexados a cada modelo (sessões, eventos, organização) que estão representados em tabelas do meu banco de dados. Um registro de cada modelo pode ter vários alertas diferentes sendo emitidos.

A maneira mais simples de resolver isso é criar uma tabela como

CREATE TABLE alerts (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    alert_type character varying(50) NOT NULL,
    metadata jsonb,
    session_id uuid REFERENCES sessions(id) ON DELETE DO NOTHING ON UPDATE CASCADE,
    event_id uuid REFERENCES events(id) ON DELETE DO NOTHING ON UPDATE CASCADE,
    organization_id uuid REFERENCES organizations(id) ON DELETE DO NOTHING ON UPDATE CASCADE,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);

Mas parece ser uma má estratégia aumentarmos isso para muito mais tabelas anexadas ao sistema de alerta. Obviamente não escala bem. Estou hesitando entre duas estratégias.

Você tem uma tabela alertse representa uma many-to-manysegunda tabela alerts_relationsque possui table_namee table_iddá sentido a cada outro modelo/tabela ao qual está anexado.

CREATE TABLE alerts (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    alert_type character varying(50) NOT NULL,
    metadata jsonb,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);

CREATE TABLE alerts_relations (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    alert_id uuid REFERENCES alerts(id) ON DELETE CASCADE ON UPDATE CASCADE,
    table_name character varying(50) NOT NULL,
    table_id character varying(50) NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);

A outra estratégia remove table_namee table_idsubstitui-a pela seguinte

CREATE TABLE session_alerts (
    session_id uuid REFERENCES sessions(id) ON DELETE CASCADE ON UPDATE CASCADE
) INHERITS (alert_relations);

CREATE TABLE event_alerts (
    event_id uuid REFERENCES events(id) ON DELETE CASCADE ON UPDATE CASCADE
) INHERITS (alert_relations);

CREATE TABLE organization_alerts (
    organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE ON UPDATE CASCADE
) INHERITS (alert_relations);

Estou me perguntando qual estratégia produz melhor desempenho e qual é a mais fácil de manter. Se isso ajudar, minha base de código está em Golang, então tenho que trabalhar manualmente no SQL e fazer pouco trabalho de ORM.

A última estratégia parece ser a mais conveniente para minha lógica de negócios porque cada relacionamento pode ser representado com uma tabela e um nome diferentes. Parece mais fácil de manter, mas não tenho certeza de nada neste momento; pode haver desvantagens que não vejo.

Qualquer outra solução é bem-vinda, claro! Obrigado por ler.

Eu tenho um cenário em que estou consultando "clusters correspondentes" para registros mestre de um banco de dados MDM. Cada "cluster" possui um número específico e dentro desse cluster, você terá uma linha/registro para o "mestre" e todos os registros filhos associados.

Operamos em diferentes regiões do mundo e temos uma coluna chamada 'domínio' com valores de referência que variam de '1001' a 1010' que representam uma região/domínio diferente. O que consegui fazer até agora foi dizer: "Quero o registro mestre de todos os clusters de correspondência onde o domínio '1008' está no cluster. Isso se parece com o seguinte (o" tipo de parceiro de negócios "é estritamente chamando 1001 = cliente e 1002 = fornecedor) - esta consulta apenas captura qualquer cluster que tenha '1008', mas não exclui outros valores de domínio de estarem lá:

select * from data.vTest 
where [Match Cluster] in (select distinct [Match Cluster] from data.vTest 
where [Domain] = 1008 and [Match Cluster] is not null and [Business Partner Type] = 1001)
and [Business Partner Type] is null;

O texto acima me mostra qualquer cluster de correspondência onde exista 1008 (apenas um exemplo - mesmo conceito para 1001, 1002, etc., etc.). O que estou procurando agora é capturar todos os clusters onde existe apenas um valor de domínio. Seguem exemplos:

Então você tem o seguinte, que é o que não quero que apareça - quaisquer clusters de correspondência que tenham mais de 1 valor de domínio desses "registros filhos":

O que tentei até agora não funcionou - o mais próximo que cheguei foi o seguinte, que ainda me retorna aqueles "mestres" com registros filhos contendo mais de um valor de domínio:

select * from data.vTest 
where [Match Cluster] in (select [Match Cluster] from data.vTest
where [Domain] not in (1001,1002,1003,1004,1005,1006,1007,1009,1010) and [Match Cluster] is not null and [Business Partner Type] = 1001)
and [Business Partner Type] is null;

Alguma idéia sobre a melhor maneira de escrever esta consulta? Espero que as fotos ajudem a voltar ao panorama geral do que estou tentando realizar. Observe que estou tirando um instantâneo de um exemplo, mas a ideia é procurar TODOS os clusters de correspondência nessa coluna e retornar todos os resultados que estou procurando. Obrigado!

Eu tenho uma sequência como esta (no PostgreSQL 16.0):

CREATE SEQUENCE public.seq_1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 999999999
    START 1
    CACHE 1
    CYCLE;

e uma tabela contendo tudo integersde 0 a 1.000.000

Quando eu faço:

select *, nextval('seq_1') as n2 
from (
   select i,n1,n1-i as d, sum(i) over (order by i) as s
   from (
      select i,nextval('seq_1') as n1
      from integers
   )
)
where i=0 or i=1000000

a saída é:

Por que o valor n2na primeira linha é igual a 50331653?

Eu teria esperado 47137349+1, não me importo com pequenos intervalos, mas neste caso o intervalo é 3194304 ( select 50331653 - 47137349), que é grande (ou grande?)

Eu encontrei:

• números de salto de sequência do postgres 9.0.4 , então eu usoCACHE 1