All perguntas(dba)

Eu tenho uma operação de consulta/geoprocessamento PostgreSQL + PostGIS de várias etapas que preciso alterar o nome do esquema para as tabelas de saída cada vez que a executo. As tabelas base que uso estão todas em outro esquema que não muda, mas as saídas da consulta são criadas em outro esquema de projeto a cada vez.

Por exemplo, tenho um novo projeto 'project_abc_2023' que é um novo esquema que criei. Atualmente, tenho que alterar todos os nomes dos esquemas da tabela de saída para este novo esquema 'project_abc_2023'. Gostaria de poder alterar esses nomes de esquema para o esquema do projeto que criei.

Estou usando o jooq para gerar consulta SQL e estou com um pouco de preguiça de gerar seleção aninhada de another_select de bottom_select. Minha consulta gerada contém expressões repetidas. O postgres 13+ aplica algumas técnicas de otimização nos bastidores?

amostra simplificada de parte da consulta

SELECT
case
           when b.count is null then 100.00
           when c.count is null then -100.00
           else round(
                   cast((100.00 * ((c.count - b.count) / cast(b.count as numeric))) as numeric),
                   2
               )
           end as expression_01,

case
           when b.count is null then 100.00
           when c.count is null then -100.00
           else round(
                   cast((100.00 * ((c.count - b.count) / cast(b.count as numeric))) as numeric),
                   2
               )
           end as expression_02
from some_table

Digamos

round(
                   cast((100.00 * ((c.count - b.count) / cast(b.count as numeric))) as numeric),
                   2
               )

foi repetido várias vezes, o Postgres calculará isso uma vez ou várias vezes?

Configurar:

• db.t3.xlargeInstância RDS.
• Mecanismo MySQL 8.0.33.
• Parâmetro padrão e grupo de opções.
• Tabela de registros de aproximadamente 900 mil.
• O RDS não tem nenhum registro ativado e não podemos reiniciar a instância por enquanto para habilitá-lo.

O banco de dados está conectado a uma aplicação Laravel que roda em Lambda. Um dos processos do lambda requer a contagem do número de linhas da spintabela.

Durante a primeira iteração, descobrimos que a select count()escrita da seguinte maneira levava alguns segundos de vez em quando. Algumas solicitações foram imediatas e outras podem levar mais de 20 segundos.

select count(*) from spin;

Pesquisando na Internet encontramos algumas respostas de pessoas reclamando sobre isso . Decidimos adicionar uma condição à consulta, e isso a tornou uma consulta em menos de um segundo:

select count(*) from spin where id > 0;

Até alguns dias atrás, quando nosso serviço começou a receber mais tráfego do que o normal e o tempo de execução das consultas ficou muito instável.

+--------+-------+------------------+---------+---------+------+-----------+----------------------------------------------------------+
| ID     | USER  | HOST             | DB      | COMMAND | TIME | STATE     | INFO                                                     |
+--------+-------+------------------+---------+---------+------+-----------+----------------------------------------------------------+
| 114168 | vmx   | 10.0.2.175:43169 | fdata   | Execute |   60 | executing | select count(*) as aggregate from `spin` where `id` > 0  |
| 114171 | vmx   | 10.0.3.149:31136 | fdata   | Execute |   58 | executing | select count(*) as aggregate from `spin` where `id` > 0  |
| 114118 | vmx   | 10.0.2.175:36571 | fdata   | Execute |  109 | executing | select count(*) as aggregate from `spin` where `id` > 0  |
+--------+-------+------------------+---------+---------+------+-----------+----------------------------------------------------------+

Suspeito que isso seja devido a algum bloqueio de acesso à spinmesa. Durante o bloqueio da mesa, o select count()trava.

Qualquer contribuição é apreciada, obrigado.

Estou configurando um Grupo de Disponibilidade do SQL Server com três réplicas: node1\inst, node2\inste node3\inst. Eu configurei node1\instpara failover automático com confirmação síncrona, enquanto node2\inste node3\instestou configurado para failover manual.

Tenho algumas perguntas sobre o comportamento de failover:

1. Quando node1\insta réplica primária encontra um problema, ela fará failover automaticamente node2ou node3mesmo se estiver configurada para failover manual? Além disso, o failover automático ocorre apenas entre réplicas de failover automático e confirmação síncrona?

2. Se node2se tornar a réplica primária e ocorrer um problema, ela ainda seguirá sua configuração de failover manual ou haverá um failover automático para node1?

Estou tentando entender os meandros da mecânica de failover nos grupos de disponibilidade do SQL Server para garantir uma configuração confiável de alta disponibilidade. Insights sobre como as decisões automáticas de failover são determinadas nessas configurações seriam extremamente úteis.

Agradecemos antecipadamente pela sua ajuda!

create table test  (
id int identity,
id_int int default 1
)

insert test default values
go 1000

begin transaction

update test 
set id_int = id_int
where id = 1000

waitfor delay  '00:00:10' 

commit

Outra sessão sem esperar obtenha o resultado:

set transaction isolation  level read committed

select * from test 
where id =1000 


id    | id_int
---------------
1000  |1 

Alguém poderia explicar por que obtenho o resultado imediatamente?

No entanto, quando uso o nível de isolamento de leitura repetível, devo esperar:

set transaction isolation  level repeatable read

select * from test 
where id =1000

No canto inferior direito da tela principal do SSMS e à esquerda de onde mostra meu nome de usuário e o nome do banco de dados em que estou, aparece o nome do servidor em que estou. Essa string é a mesma que coloquei no campo "Nome do servidor" ao me conectar ao servidor quando abro o SSMS pela primeira vez e também é mostrada no Pesquisador de Objetos. Como o SSMS determina esse nome? eu tentei

SELECT 
  HOST_NAME(),
  @@SERVICENAME,
  DB_NAME,
  @@SERVERNAME,
  SERVERPROPERTY('InstanceName'),
  SERVERPROPERTY('MachineName')

mas SERVERPROPERTY('MachineName')retorna apenas uma substring do que o SSMS mostra. O resto não tem nada a ver com a string em questão.

Quando estava lidando com fragmentação, tive uma dúvida sobre um índice não clusterizado. No índice não clusterizado, os dados reais da tabela (normalmente armazenados em um índice clusterizado, caso contrário, um heap) são armazenados separadamente do índice não clusterizado. Como isso afeta a fragmentação da tabela e do índice quando crio um índice não clusterizado?

Agradeço uma resposta detalhada.

Estou tentando atualizar um cluster Postgres de PG 12 para PG 15 usando este comando:

sudo pg_upgradecluster 12 main --method=link

Meu cluster usa alguns tablespaces:

postgres=# SELECT oid, spcname, pg_tablespace_location(oid) FROM pg_tablespace;
    oid     |      spcname       |      pg_tablespace_location      
------------+--------------------+----------------------------------
       1664 | pg_global          | 
       1663 | default_old        | 
 1905123481 | ts1                | /mnt/postgres/ts1
 1905522771 | ts2                | /mnt/postgres/ts2
 1905595436 | ts3                | /mnt/postgres/ts3

Quando executo o script pg_upgradecluster, ele falha imediatamente com este erro:

tablespace directory "" does not exist

Todos os tablespaces estão localizados em discos diferentes

Já atualizei outro cluster que não possui tablespaces do PG 12 para o PG 15 sem erros.

Não entendo o que fazer agora :(

alguém já enfrentou esse problema antes e sabe como corrigi-lo ??

desde já, obrigado

Estou tentando construir uma função armazenada em C, que pegará um array de bigint como argumento e retornará outro array que é uma cópia do array de parâmetros, mas com todos os elementos incrementados em 1. Meu problema é que quando executo esse código como um programa independente , ele é executado muito rápido: 0,1s para elementos de 10M, mas leva 6s quando executado como procedimento armazenado Postgresql C.

Gostaria de saber se alguém pode identificar se estou fazendo algo obviamente errado?

Meu procedimento armazenado C:

#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"
#include "utils/array.h"
#include "catalog/pg_type.h"

PG_MODULE_MAGIC;

#define ARRPTR(x)  ( (int64 *) ARR_DATA_PTR(x) )
#define ARRNELEMS(x)  ArrayGetNItems(ARR_NDIM(x), ARR_DIMS(x))

PG_FUNCTION_INFO_V1(test_inc);
Datum
test_inc(PG_FUNCTION_ARGS)
{
    ArrayType  *a = PG_GETARG_ARRAYTYPE_P(0);
    int n = ARRNELEMS(a);
    int nbytes = ARR_OVERHEAD_NONULLS(1) + sizeof(int64) * n;

    ArrayType  *r = (ArrayType *) palloc0(nbytes);
    SET_VARSIZE(r, nbytes);
    ARR_NDIM(r) = 1;
    r->dataoffset = 0; // marker for no null bitmap
    ARR_ELEMTYPE(r) = INT8OID;
    ARR_DIMS(r)[0] = n;
    ARR_LBOUND(r)[0] = 1;

    int64 *ad = ARRPTR(a);
    int64 *rd = ARRPTR(r);

    ereport(WARNING,
        errcode(ERRCODE_WARNING),
        errmsg("Before loop"));
    for (int i = 0; i < n; i++) {
        rd[i] = ad[i] + 1;
    }
    ereport(WARNING,
        errcode(ERRCODE_WARNING),
        errmsg("After loop"));

    PG_RETURN_POINTER(r);
}

Eu compilo e instalo desta forma:

gcc -fPIC -O2 -I/usr/include/postgresql/15/server -I/usr/include/postgresql/internal -c test.c
gcc -shared -o test.so test.o
/usr/bin/install -c -m 755  test.so '/usr/lib/postgresql/15/lib/'

Código SQL que uso para testá-lo:

CREATE FUNCTION test_inc(_int8) RETURNS _int8 AS '/usr/lib/postgresql/15/lib/test.so'
LANGUAGE C IMMUTABLE PARALLEL SAFE;

create table t as select generate_series(0, 10000000) n;
create table t2 as select array_agg(n order by n) n from t;
create table t3 as select test_inc(n) n from t2;

A última consulta é executada em 6s. A remoção rd[i] = ad[i] + 1;da linha faz com que o código seja executado em 0,6s. Além disso, olhando as mensagens de aviso, vejo que a execução travou em algum lugar após o loop, e não dentro do loop.

Meu código C autônomo se parece com o seguinte e é executado em 0,1s:

#include <stdio.h>
#include <stdlib.h>

int main(int argc, char ** argv) {
    long n = 10000000;
    long *a = (long*)malloc(n * sizeof(long));
    long *r = (long*)malloc(n * sizeof(long));
    for (int i = 0; i < n; i++) {
        a[i] = i;
    }
    for (int i = 0; i < n; i++) {
        r[i] = a[i] + 1;
    }
    // To make sure compiler does not remove previous loop because result is unused.
    long res = 0;
    for (int i = 0; i < n; i++) {
        res += r[i];
    }

    printf("%ld", res);
}

Em um plano de consulta, vejo o tipo de linha sendo lançado. Existe um índice que deveria ser usado e não é. O typecast é uma bandeira vermelha?

A tabela teve um ANALYZE e possui 110 milhões de linhas. Destes, 10% correspondem à consulta.

Aqui está a explicação completa:

explain select * from item_info where subtype = 'Dataset';

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Gather  (cost=1000.00..1592021.24 rows=1320650 width=27)
   Workers Planned: 2
   ->  Parallel Seq Scan on item_info  (cost=0.00..1458956.24 rows=550271 width=27)
         Filter: ((subtype)::text = 'Dataset'::text)
(4 rows)

Time: 0.652 ms=> \d item_info
                     Table "public.item_info"
    Column    |       Type        | Collation | Nullable | Default
--------------+-------------------+-----------+----------+---------
 root_item_pk | bigint            |           | not null |
 type         | character varying |           |          |
 subtype      | character varying |           |          |
Indexes:
    "item_info_pkey" PRIMARY KEY, btree (root_item_pk)
    "item_info_subtype_idx" btree (subtype)
    "item_info_type_idx" btree (type)