Perguntas[postgresql](coding)

Depois de pesquisar sobre esse assunto, não tenho certeza se é realmente possível fazer isso. Obrigado por qualquer dica.

Minha visão é criada assim:

create or replace view OutputView as
select * from view1
natural full outer join view2;

Usando:

update OutputView set colA = 'undefined' where colA is null;

dá:

ERROR:  cannot update view "OutputView"
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

Sem sorte até agora com esse gatilho e regra. O problema é que algumas colunas na visualização de saída têm valores nulos porque essas colunas não existem nas visualizações originais, por exemplo:

view1
-----
colA colB
1    a
2    b

view2
-----
colB colC
100  x
200  y

OutputView
----------
colA  colB  colC
1     a     null
2     b     null
null  100   x
null  200   y

Estou com problemas para criar novas entidades com relacionamentos um-para-um. O EF Core não está atribuindo as chaves estrangeiras corretamente.

Como exemplo, vamos pegar duas entidades:

public class User
{
    public int Id { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

public class Blog
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public User User { get; set; }
}

Defina o relacionamento deles de modo que fique claro qual depende de qual:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<User>()
        .HasOne(u => u.Blog)
        .WithOne(b => b.User)
        .HasForeignKey<Blog>()
        .IsRequired();
}

Então, quando tento criá-los assim:

var user = new User 
               {
                   Blog = new Blog(),
               };

await DbContext.Set<User>().AddAsync(user);
await DbContext.SaveChangesAsync();

O Blogfoi criado e sua UserIdpropriedade está definida corretamente. No entanto, a BlogIdpropriedade de Useré 0. De acordo com a documentação , deve ser possível criar entidades relacionadas como esta.

Isso não se aplica a relacionamentos individuais? Ou há algo mais que não estou percebendo?

A propósito, estou usando o PostgreSQL, caso o provedor do banco de dados tenha algo a ver com isso. Também estou usando o EF Core versão 9.

Meu banco de dados Postgres 17 tem uma tabela grande com 245 colunas contendo uma coluna indexada ribakood:

CREATE TABLE firma2.toode
(
...
    ribakood character(20) COLLATE pg_catalog."default",
...
);

CREATE INDEX toode_ribakood_idx
    ON firma2.toode 
    (ribakood COLLATE pg_catalog."default" ASC NULLS LAST);

A consulta com trim():

explain analyze select toode,ostuhind, nimetus, pangateen
  from firma2.toode where ribakood=TRIM('TESTTOODE/H ')

não usa o índice:

Gather  (cost=1000.00..575155.04 rows=4927 width=114) (actual time=101.341..2257.639 rows=1 loops=1)
  Workers Planned: 3
  Workers Launched: 0
  ->  Parallel Seq Scan on toode (cost=0.00..573662.34 rows=1589 width=114) (actual time=101.186..2257.436 rows=1 loops=1)
        Filter: ((ribakood)::text = 'TESTTOODE/H'::text)
        Rows Removed by Filter: 986481
Planning Time: 0.098 ms
Execution Time: 2257.653 ms

A mesma consulta sem trim():

explain analyze select toode,ostuhind, nimetus, pangateen
  from firma2.toode where ribakood='TESTTOODE/H'

usa o índice:

Index Scan using toode_ribakood_idx on toode  (cost=0.42..12.45 rows=2 width=114) (actual time=0.475..0.477 rows=1 loops=1)
  Index Cond: (ribakood = 'TESTTOODE/H'::bpchar)
Planning Time: 0.147 ms
Execution Time: 0.490 ms

O Postgres converte a ribakoodcoluna para o tipo texte não consegue encontrar o índice. Como forçar a primeira consulta trim()a usar o índice?

Usando: PostgreSQL 17.0 em x86_64-windows, compilado por msvc-19.41.34120, 64 bits

id        timestamp              value
--------+---------------------+-----------
225     |2018-07-24 13:00:00  |0
226     |2018-07-24 18:33:32  |196
227     |2018-07-25 5:59:14   |290
173     |2018-07-05 8:16:14   |258
228     |2018-07-25 7:00:00   |469
175     |2018-07-07 6:00:00   |0
176     |2018-07-07 9:25:35   |236
177     |2018-07-07 17:19:13  |300
178     |2018-07-08 5:47:13   |323
179     |2018-07-08 6:49:14   |230
180     |2018-07-08 17:45:36  |270
181     |2018-07-09 5:41:13   |250
182     |2018-07-09 9:43:13   |213
183     |2018-07-09 18:37:44  |226
184     |2018-07-10 5:22:44   |380
185     |2018-07-10 7:37:14   |200
186     |2018-07-10 15:54:44  |260

SELECT max(value)  
  FROM table 
 WHERE timestamp BETWEEN '2018-07-22 00:00:00' 
                     AND '2025-04-22 23:59:59'

Esta é minha consulta para um período específico. Preciso obter o maior valor por dia ou o maior valor por dia de todos os dados na tabela.

Esta é minha consulta para um período específico. Preciso obter o maior valor por dia ou o maior valor por dia de todos os dados na tabela.

Usuários com privilégios EXECUTE para funções receberão um erro ao tentar executar a função, se não tiverem privilégios nos objetos referenciados na função.

Criei uma função my_function. Dentro dela, seleciono/insiro/atualizo/excluo dados de uma tabela my_table. Em seguida, concedo o privilégio EXECUTE a outro usuário, "user1".

Entretanto, quando o usuário1 tenta executar a função, ela falha com a mensagem "permissão negada para a tabela my_table".

Não quero dar permissão ao usuário user1 para acessar a tabela my_table diretamente. Esse é o propósito de criar a função my_function, pois quero controlar quais dados o user1 pode ver/modificar.

Isso é uma limitação (defeito) do Postgres? Para mim, isso é um grande problema de segurança/privacidade. Existe uma maneira de superar isso? Ou temos que conviver com isso?

Minha versão do Postgres é 15.x

Preciso migrar duas tabelas muito grandes continuamente. Minha primeira solução foi comparar coluna por coluna para descobrir o que havia mudado, mas isso é muito caro de executar.

A segunda ideia foi criar Colunas Computacionais PERSISTIDAS para armazenar valores com hash de colunas definidas. Isso poderia acelerar o processo. Mas o problema está no hash de colunas com timestamp e fuso horário. Estou recebendo este erro: ERROR: generation expression is not immutable.

Tentei várias maneiras de converter o valor, mas nada está funcionando ainda.

Código (valores comentados, não funcionou):

ALTER TABLE schema.table ADD COLUMN hashed_columns UUID GENERATED ALWAYS AS (
    md5(
        id::text ||
        type_id::text ||
        --EXTRACT(EPOCH FROM date)::text ||
        -- to_char(date::timestamp without time zone , 'YYYY-MM-DD"T"HH24:MI:SS') ||
        -- date::text ||
        -- to_char(date, 'YYYYMMDD')::integer ||
        -- md5(date::text) ||
        to_char(date, 'YYYYMMDD') ||
        value1::text ||
        value2::text
    )::uuid
) STORED;

Parece que ele detecta uma coluna imutável e simplesmente gera um erro, sem tentar convertê-la primeiro. O que estou tentando fazer é fundamentalmente errado ou é possível?

Quando removo a coluna de data do script, funciona, então o problema está no registro de data e hora.

O servidor está executando o Postgre 16.

Estou estudando travessia de grafos usando CTEs recursivas (curso learnSQL). A questão pergunta sobre visitar cada cidade a partir de uma cidade inicial. Eles usam uma string "path" construída a partir de nomes de cidades anexados e, em seguida, usam where position(city.name IN travel.path) = 0para verificar se a cidade já foi visitada. Isso funciona, mas costumo procurar alternativas e poderia usar um array de cidades visitadas.

Como um desenvolvedor experiente (por exemplo, Java, mas qualquer linguagem imperativa), eu representaria visitedcomo um SET para testes de contenção eficientes, mas o postgresql parece oferecer apenas ARRAY.

1. Existe um tipo SET (algo simples, não uma tabela separada ou conjunto JSON) que eu possa usar?
2. O ARRAY é eficiente em termos de pesquisa?

Os dados de exemplo são, claro, minúsculos e não representam um problema, mas gostaria de saber o que é mais eficiente/melhor prática. Na minha opinião, a consulta por string positionnão é muito semântica .

Obrigado!

with recursive
travel(path, visited, visited_count) AS (
  select
    name::text,
    array[name::text],
    1
  
  from city
  where
    name = 'Bristol'
  
  union all
  
  select
    travel.path || '->' || city.name,
    visited || name::text,
    visited_count + 1
  
  from city, travel
  
  where
    city.name <> all(visited)
    --position(city.name IN travel.path) = 0
)
  
select
  path,
  visited

from travel

where
  visited_count = 6

Problema e Contexto:

Estou tentando configurar o Apache Ignite (3.0.0) como uma grade de dados na memória. A ideia é usá-lo como um cache e carregar dados de um RDBMS externo, como o Postgres. Tenho um caso de uso específico em que tenho alguns arquivos de dados com tipo XML e tipo TXT no Postgres. Quero poder carregar os dados no Apache Ignite (configurando-o como um cache) e usá-los em meus aplicativos Java e CPP.

O problema que estou enfrentando é que não há instruções claras nem material útil on-line para esta versão específica do Apache Ignite para configurar isso.

O que eu fiz até agora:

Baixei os binários do Apache Ignite 3 na minha máquina (subsistema Linux). Consegui configurá-lo com sucesso e iniciar um cluster com um nó. Consegui criar uma tabela com dados de exemplo usando a interface do usuário do gridGain e também por meio da API do IgniteTables.

Até agora, li a documentação oficial em: https://ignite.apache.org/docs/ignite3/latest/index

Também analisei os exemplos no repositório github: https://github.com/apache/ignite-3/tree/main/examples

Encontrei muitas informações sobre como fazer isso na versão 2.x do Apache Ignite, onde a configuração é feita por meio de configurações XML/Java (referência específica: https://ignite.apache.org/docs/latest/persistence/external-storage#rdbms-integration ).
Também tentei, mas o pacote/classes CacheConfiguration não parecem estar disponíveis na versão mais recente (3.0.0).
Outra atualização da GridGain é que eles migraram para configurações no estilo HOCON, então a configuração de cache via XML não parece mais aplicável.

• Configuração baseada em HOCON : Human-Optimized Config Object Notation (HOCON) é agora a linguagem de configuração padrão, oferecendo uma alternativa mais legível e flexível ao XML.

Também verifiquei cursos/vídeos na Universidade Gridgain e no canal oficial do Youtube.

Estou trabalhando com um banco de dados PostgreSQL e preciso alterar a configuração de agrupamento de colunas de texto específicas. No entanto, não quero alterar o agrupamento de todo o banco de dados.

Atualmente, o banco de dados possui indexação que não diferencia maiúsculas de minúsculas em colunas inteiras (especificamente em colunas de ID) e não há índices nas colunas de texto que desejo modificar. Presumo que alterar a ordenação apenas das colunas de texto não deva causar problemas, visto que não há índices nessas colunas.

Minha pergunta é: alterar a ordenação nas colunas de texto causará algum problema com dados ou consultas existentes, mesmo que a ordenação do banco de dados não seja alterada e nenhum índice seja afetado?

Há alguma consideração de desempenho ou possíveis problemas dos quais eu deva estar ciente neste cenário?

A ordenação atual do banco de dados está definida como Turkish_Turkey.1254, conforme mostrado por esta consulta:

SELECT datname, datcollate, datctype 
FROM pg_database 
WHERE datname = '';

Para alterar a ordenação de todas as colunas de tipo de texto no banco de dados para uma ordenação que não diferencia maiúsculas de minúsculas, estou executando o seguinte bloco PL/pgSQL:

DO $$
DECLARE
    t record;
BEGIN
    FOR t IN 
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns
        WHERE table_catalog = '' 
          AND data_type = 'text' 
          AND table_schema NOT LIKE 'pg_%'
        ORDER BY table_schema
    LOOP
        EXECUTE format('ALTER TABLE %I.%I ALTER COLUMN %I SET DATA TYPE text COLLATE "tr-x-icu"', 
                       t.table_schema, t.table_name, t.column_name);
    END LOOP;
END
$$;

CREATE FUNCTION create_user_if_not_exists(_name text, _pass text)
  RETURNS void AS
$func$
DECLARE
   _dbname TEXT := concat(_name, '_db');
BEGIN
    IF EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = _name) THEN
       RAISE NOTICE 'Role already exists. Skipping.';
    ELSE
     CREATE USER _name WITH encrypted password '_pass';
     GRANT ALL PRIVILEGES ON DATABASE _dbname TO _name;
    END IF;
END
$func$ LANGUAGE plpgsql;

Quando eu corro:

psql --username "$POSTGRES_USER" -c "SELECT create_user_if_not_exists('Foo', 'BAR');"

Eu entendo:

ERROR:  database "_dbname" does not exist

Se eu comentar a GRANT ALLparte, acabo com um usuário chamado _nameem pg_catalog.pg_roles. Suspeitei _que fosse o responsável por isso, mas o mesmo acontece com várias sintaxes. Adicionar _namee _passnão DECLAREmuda nada.

Por que _name, _passe são _dbnameinterpretados como valores em vez de variáveis ​​próprias neste contexto? É uma questão de definição de função, algumas restrições em GRANTe CREATEou por causa da maneira como chamo esta função com psql?

Depois de ler isto e isto , não consigo entender a diferença entre explicar o que está errado.

Ambiente:psql (PostgreSQL) 17.4 (Debian 17.4-1.pgdg120+2)