All perguntas(dba)

mysql Ver 8.0.20-0ubuntu0.19.10.1 para Linux em armv7l ((Ubuntu))

MySQL continua travando. Receio que o banco de dados tenha sido corrompido.

Ele trava após uma consulta simples.

mysql> select count(*) from all_values;
ERROR 2013 (HY000): Lost connection to MySQL server during query

registro de sistema:

ubuntu kernel: [240423.997644] audit: type=1400 audit(1707221926.282:9988): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/19377/task/19441/mem" pid=19377 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=111 ouid=111
ubuntu systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
ubuntu systemd[1]: mysql.service: Failed with result 'exit-code'.
ubuntu systemd[1]: mysql.service: Service RestartSec=100ms expired, scheduling restart.
ubuntu systemd[1]: mysql.service: Scheduled restart job, restart counter is at 1.
ubuntu systemd[1]: Stopped MySQL Community Server.

mysql/error.log:

2024-02-06T12:18:46.282748Z 8 [ERROR] [MY-012153] [InnoDB] Trying to access page number 16964 in space 11, space name tsl/all_values, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2024-02-06T12:18:46.282848Z 8 [ERROR] [MY-012154] [InnoDB] Server exits.
2024-02-06T12:18:46.282890Z 8 [ERROR] [MY-013183] [InnoDB] Assertion failure: fil0fil.cc:7192 thread 463119312
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
12:18:46 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0xeb04900
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 1b9a9d68 thread_stack 0x36000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2d) [0x1a93e7a]
/usr/sbin/mysqld(handle_fatal_signal+0x1c5) [0xfdb706]
/lib/arm-linux-gnueabihf/libc.so.6(+0x2aaa0) [0xb6972aa0]
/lib/arm-linux-gnueabihf/libc.so.6(+0x1ad56) [0xb6962d56]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (b997900): is an invalid pointer
Connection ID (thread ID): 8
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Ao tentar fazer backup do meu banco de dados, ele falha:

$ mysqldump -u root -p tsl > tsl-bu4.sql
Enter password:
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `all_values` at row: 3835117

Isso é consistente, falha sempre na linha 3835117.

Configuração atual:

[mysqld]
log_error = /var/log/mysql/error.log
general_log = on
general_log_file=/var/log/mysql/general.log

innodb_force_recovery = 0
innodb_buffer_pool_size=1G

Eu tentei de tudo com innodb_force_recovery definido para todos os valores (1-6) e também tentei vários valores para innodb_buffer_pool_size, mas sem diferença alguma.

Questões:

Presumo que isso significa que meu banco de dados está de alguma forma corrompido. Essa suposição está correta?

Como parece falhar sempre na mesma linha, isso significa que identifiquei a linha que está corrompida? Em caso afirmativo, existe uma maneira de remover apenas esta linha (sem usar uma consulta SQL que obviamente fará o MySQL travar)?

Desde já, obrigado. E sim, começarei a fazer backup do meu banco de dados regularmente a partir de agora.

Editar: informações solicitadas:

mysql> SHOW CREATE TABLE all_values;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                         |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| all_values | CREATE TABLE `all_values` (
  `sensor_id` int DEFAULT NULL,
  `value` double DEFAULT NULL,
  `timestamp` int DEFAULT NULL,
  KEY `sensor_id` (`sensor_id`,`timestamp`),
  KEY `value_index` (`value`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS WHERE name LIKE "all_values";
+------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| all_values | InnoDB |      10 | Dynamic    | 41503787 |             44 |  1866465280 |               0 |   2297348096 |         0 |           NULL | 2021-12-13 08:12:06 | NULL        | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.01 sec)

Como posso excluir com eficiência milhares de arquivos WAL (Write-Ahead Logging) no diretório /wal em um banco de dados PostgreSQL para liberar espaço em disco e garantir a integridade do banco de dados?

-rw------- 1 postgres postgres 16777216 5 de fevereiro 14:11 000000020000000B0000004D

-rw------- 1 postgres postgres 16777216 6 de fevereiro 03:13 000000020000000B0000004E

-rw------- 1 postgres postgres 16777216 6 de fevereiro 07:57 000000020000000B0000004F

Eu tenho um backup quebrado porque a estrofe continua me mostrando esse erro.

ERRO: [082]: O segmento WAL 000000020000000B00000093 não foi arquivado antes do tempo limite de 60000 ms

Eu tenho uma visualização de banco de dados PostgreSQL (v15) que acumula um monte de dados por usuário para uma única organização, para exibir um relatório de dados como taxas devidas/pagas por usuário, etc. insere e executa em menos de um segundo, o que é perfeitamente rápido para este caso de uso (um relatório de UI). Até agora tudo bem.

Para algumas organizações, também preciso produzir um resumo DESSES resumos, ou seja, um rollup para grupos de organizações que resuma os mesmos dados por usuário, por organização, para uma organização mãe. Alto nível, estou tentando selecionar primeiro as organizações de destino (que devem produzir um conjunto de <150 linhas) e, em seguida, unir minha visualização de organização única existente a esse conjunto para adicionar os dados agregados da visualização original, mas por coleta organização.

Embora a consulta real lide com muito mais colunas e agregações na saída, esta versão reduzida resume a lógica principal da consulta:

WITH member_orgs AS (
  -- CTE returns 133 rows in ~30ms
  SELECT
    bp.id AS billing_period_id,
    bp.started_on AS billing_period_started_on,
    bp.ended_on AS billing_period_ended_on,
    org.name AS organization_name,
    bp.organization_id
  FROM billing_periods bp
    JOIN organizations org
      ON org.id = bp.organization_id
    WHERE
      bp.paid_by_organization_id = 123
      AND (
        bp.started_on >= '2023-07-01'
          AND bp.ended_on <= '2024-06-30'
        )
      AND bp.organization_id != 123
)
SELECT
  member_orgs.billing_period_id,
  member_orgs.billing_period_started_on,
  member_orgs.billing_period_ended_on,
  member_orgs.organization_name,
  -- this is one example aggregation, the real query has more of these:
  SUM(CASE WHEN details.received_amount > 0 THEN 1 ELSE 0 END) AS payments_received_count
  FROM member_orgs 
      LEFT JOIN per_athlete_fee_details_view details
        -- SLOW (~40 SECONDS):
          -- ON details.billing_period_id = member_orgs.billing_period_id
          --   AND details.organization_id = member_orgs.organization_id
        -- FAST (~150ms):
          ON details.billing_period_id = 1234
            AND details.organization_id = 3456
  GROUP BY
    member_orgs.billing_period_id,
    member_orgs.billing_period_started_on,
    member_orgs.billing_period_ended_on,
    member_orgs.organization_name;

A visualização que está sendo unida é bastante complexa e também depende de algumas subvisualizações, mas quando executada isoladamente é muito rápida. O member_orgsCTE também é muito rápido por si só (~30ms) e sempre resulta em <150 registros. Conforme mostrado acima, se eu juntar os dois em IDs específicos (como teste), a consulta geral será extremamente rápida (~150ms). No entanto, ao unir as colunas entre o CTE e a visualização (o que preciso fazer), o desempenho geral cai para mais de 40 segundos .

Sinto que devo estar perdendo alguma coisa boba, pois não entendo como juntar a visualização a um conjunto de 133 registros (no caso real, estou depurando) poderia explodir o tempo de forma tão dramática. Meu entendimento era que o CTE materializaria sua saída, permitindo que a junção externa funcionasse apenas naquele conjunto de resultados, o que considero muito eficiente. Eu poderia escrever o código do aplicativo para executar o CTE, depois iterar sobre os IDs e executar a consulta externa individualmente 133 vezes em muito menos tempo do que esta consulta está demorando.

Por favor, perdoe os enormes planos de consulta, pois as consultas reais (com visualizações subjacentes) são bastante complicadas, mas foram criadas com uma versão um pouco mais complexa do exemplo de consulta reduzida mostrado acima (embora a lógica seja a mesma). A única diferença entre as duas execuções foi o uso de IDs específicos, em vez da união em colunas, exatamente como mostrado no código de exemplo acima.

• Versão rápida (por IDs específicos) - executada em 44ms
• Versão lenta (junção em colunas de ID) - executada em mais de 41 segundos

Agradecemos antecipadamente e deixe-me saber se posso fornecer mais detalhes.

Estou usando o MS SQL Server Management Studio v18.10. A coluna CandidateID é uma chave [estrangeira], mas não aparece na pasta Chaves.

EDIT: Essa chave também não está na pasta Constraints

Se eu quiser descobrir o que está acontecendo agora , uso o de Adam Machanic sp_whoisactive. Se eu quiser descobrir o que aconteceu recentemente em meu servidor , usarei o Query Store.

Os Eventos Estendidos foram projetados para substituir o Profiler, mas eu diria que a combinação do Query Store sp_whoisactivejá fez isso em qualquer SQL Server em uma versão 2016 ou posterior. Com isso em mente, quando eu usaria Eventos Estendidos?

Para me inspirar, verifiquei os eventos estendidos que acompanham o dbatools . Eu não fiquei impressionado. Eles só parecem úteis para fazer monitoramento de longo prazo de tudo o que o Query Store não armazena. Por exemplo: o que certos logins estão fazendo, bloqueios, tipos extremamente específicos de E/S, uso de parâmetros de procedimento armazenado e uso de recursos obsoletos. Tudo isso é muito bom, mas francamente não consigo me imaginar tendo que fazer esse ajuste cirúrgico em um servidor. Há algum uso comum que eu perdi?

Estou executando uma consulta como

explain (analyze, buffers) select col1, col2, count(col3) as c from table1 group by 2, 1 order by 2, 1

Quando work_memestá definido como 4 MB, o plano fica assim:

"GroupAggregate  (cost=0.70..211944.54 rows=573788 width=26) (actual time=5.146..2601.133 rows=1867574 loops=1)"
"  Group Key: col2, col1"
"  Buffers: shared hit=1844356 read=9682"
"  ->  Incremental Sort  (cost=0.70..191999.45 rows=1894295 width=21) (actual time=5.131..1848.190 rows=1894295 loops=1)"
"        Sort Key: col2, col1"
"        Presorted Key: col2"
"        Full-sort Groups: 58831  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB"
"        Buffers: shared hit=1844356 read=9682"
"        ->  Index Scan using table1_pkey on table1  (cost=0.43..121686.41 rows=1894295 width=21) (actual time=5.071..923.512 rows=1894295 loops=1)"
"              Buffers: shared hit=1844356 read=9682"
"Planning:"
"  Buffers: shared hit=2"
"Planning Time: 0.127 ms"
"JIT:"
"  Functions: 7"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.614 ms, Inlining 0.000 ms, Optimization 0.346 ms, Emission 4.648 ms, Total 5.609 ms"
"Execution Time: 2725.164 ms"

Quando eu aumento o work_mem para 1GB, de repente fica muito diferente

"Sort  (cost=107700.32..109134.79 rows=573788 width=26) (actual time=6461.310..6821.930 rows=1867574 loops=1)"
"  Sort Key: col2, col1"
"  Sort Method: quicksort  Memory: 195057kB"
"  Buffers: shared hit=13813 read=116"
"  ->  HashAggregate  (cost=47079.16..52817.04 rows=573788 width=26) (actual time=1194.218..1777.794 rows=1867574 loops=1)"
"        Group Key: col2, col1"
"        Batches: 1  Memory Usage: 303121kB"
"        Buffers: shared hit=13813 read=116"
"        ->  Seq Scan on table1  (cost=0.00..32871.95 rows=1894295 width=21) (actual time=0.016..214.794 rows=1894295 loops=1)"
"              Buffers: shared hit=13813 read=116"
"Planning:"
"  Buffers: shared read=2"
"Planning Time: 0.122 ms"
"JIT:"
"  Functions: 7"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.477 ms, Inlining 0.000 ms, Optimization 0.216 ms, Emission 4.722 ms, Total 5.416 ms"
"Execution Time: 6967.294 ms"

Observações confusas -

1. Ele mudou para uma varredura sequencial em vez de uma varredura de índice, onde há mais memória
2. Ele abandonou a classificação incremental eficiente (seguida por GroupAggregate) e fez um HashAggregate seguido por classificação rápida
3. O custo do novo plano com 1 GB de memória se mostra menor, mas o tempo de execução é bem maior.

O que está acontecendo?

Eu tenho um servidor Plesk Ubuntu 22.04 com 120 GB de RAM e mariadb 10.6.16 que uso para executar o banco de dados de um único site. O banco de dados sozinho tem cerca de 8 GB, mas apesar de todas as otimizações que fiz, o mariadb ainda está rodando com mais de 140% da CPU. Isso resulta em tempos de resposta lentos do site.

Já fiz algumas pesquisas no Google e otimizei PHP FPM e MySQL mas apesar de tudo isso a velocidade do site ainda é lenta e estou apontando o dedo para o mariadb cujo processo está em mais de 140% do processador. Vou deixar para vocês a captura de tela abaixo.

processo mariadb

Configuração PHP FPM

Eu configurei:

pm.max_children = 90
pm.max_requests = 200
pm = static
memory_limit = 10240M
max_execution_time = 300
max_input_time = 600
post_max_size = 1024M
upload_max_filesize = 512M

Configuração MariaDB

No /etc/mysql/my.cnfarquivo, configurei esses valores

[mysqld] sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = 127.0.0.1
local-infile=0
loose-local-infile=1
innodb_buffer_pool_size = 96G
innodb_log_file_size = 12G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 512M
query_cache_min_res_unit = 4k
innodb_lock_wait_timeout = 6000

Também é curioso mas na imagem da memória abaixo a parte em cache (cor bege) é muito pequena. Captura de tela da memória

O servidor em si estava funcionando bem em um servidor Ubuntu 20.04 de 64 Gb sem problemas. Mas desde esta migração é lenta. Mesmo que tenhamos passado a RAM de 64 Gb para 120 Go no servidor Ubuntu 22.04.

Alguém já enfrentou esse tipo de problema?

Agradeço antecipadamente por sua ajuda.

Cumprimentos

Aqui estão informações adicionais baseadas em comentários: A CPU do servidor é 24 x Processador AMD EPYC 7282 de 16 núcleos O disco rígido é NVMe

MariaDB [(none)]> SHOW ENGINE INNODB STATUS;

| InnoDB |      |
=====================================
2024-02-06 09:18:33 0x7fd0721fe640 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 55 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 386065 srv_idle
srv_master_thread log flush and writes: 386059
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 5295351
Purge done for trx's n:o < 5295349 undo n:o < 0 state: running
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (0x7fe8c01d0880), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cdc80), not started
mysql tables in use 1, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cb080), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c8480), not started
mysql tables in use 6, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c4d80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cbb80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c4280), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01d1380), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cd180), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01ce780), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cc680), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c3780), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c0b80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01d1e80), not started
mysql tables in use 2, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c9a80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cfd80), not started
mysql tables in use 1, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cf280), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01ca580), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c7980), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c2c80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c6e80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c2180), not started
mysql tables in use 5, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c8f80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c6380), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c5880), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c1680), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync) log: 0; buffer pool: 0
406980 OS file reads, 1500489 OS file writes, 309011 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.07 writes/s, 0.56 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 10381178784
Log flushed up to   10381178282
Pages flushed up to 9758411800
Last checkpoint at  9758411788
0 pending log flushes, 0 pending chkp writes
1500490 log i/o's done, 1.07 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 103213432832
Dictionary memory allocated 613451784
Buffer pool size   6230016
Free buffers       5820886
Database pages     409130
Old database pages 151006
Modified db pages  21938
Percent of dirty pages(LRU & free pages): 0.352
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 1300160, not young 5227092
0.04 youngs/s, 0.00 non-youngs/s
Pages read 406341, created 12733, written 0
0.00 reads/s, 0.02 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 409130, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 774791, updated 948664, deleted 367, read 777328684815
0.85 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1854176.96 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Os demais comandos são resultados muito longos para serem inseridos na descrição com base no limite fixado pelo Stackexchange.

Outras informações:

MOSTRAR STATUS GLOBAL; https://justpaste.it/4iip0

MOSTRAR VARIÁVEIS GLOBAIS; https://justpaste.it/d9k81

STATUS; https://justpaste.it/fk95g

ulimit -a https://justpaste.it/bc50r

Eu tenho uma tabela geográfica que contém

• países
• localidades (cidade, vila, vila, ilha, arquipélago)
• locais (local/empresa + bairros/distrito/área), por exemplo - Big Ben ou Southwark Borough.

Para detalhes adicionais de cada tipo de local, tenho tabelas relacionadas.
Tabela 'country_details' para locais do tipo 'country' e da mesma forma para locais.

Para um local como 'Big Ben', tem referência ao id da sua localidade (ou seja, Londres), e também referência ao país (que pode ser simplesmente pelo iso_code do país)

Exemplo:

 id |     title      |  locality_id  |  country_iso_code |
---------------------------------------------------------|
 1  | United Kingdom |     null      |     UK            |
 2  | London         |     null      |     UK            |
 3  | Big Ben        |      2        |     UK            |
 4  | XYZ District   |      2        |     UK            |

Cenário

Agora, como para enviar ao cliente informações sobre o Big Ben eu também gostaria de obter o nome da localidade (Londres) e do país (Reino Unido), parece que minhas únicas 2 opções são:

1. CTE recursivo
2. JOIN na mesma mesa.

No entanto, uma vez que temos uma tabela de dezenas de milhares de registros, que pode potencialmente crescer para muito mais (alguns milhões), além da complexidade da consulta, isso também afetará o desempenho, suponho.

Pergunta

Qual é a melhor opção para "juntar" detalhes como "Londres" e "Reino Unido"?
Ambas as opções são ruins e é melhor repensar o design do esquema?

Tabelas:

CREATE TABLE places (
    id              int,
    type            smallint, -- ['country', 'locality', 'location']
    sub_type        smallint, -- nullable (city, village, etc.)

    -- names
    title           text,

    -- locality
    locality_name   text,
    locality_id     

    -- country
    country_iso_alpha2 text, -- 'GB'
    country_name       text, -- 'United Kingdom'
    admin_region       text, -- 'England', 'Texas', .. (null for Country)
    
    ...
);

CREATE TABLE country_details(
    place_id      int,
    place_type    smallint NOT NULL CHECK (item_type=1),

    iso_alpha2    text,
    iso_alpha3    text,
    ...

    PRIMARY KEY (place_id, place_type),
    FOREIGN KEY (place_id, place_type) references places (place_id, place_type) ON DELETE CASCADE
);

CREATE TABLE location_details(
    place_id      int,
    place_type    smallint NOT NULL CHECK (item_type=3),

    website            text,
    neighborhood       text,
    formatted_address  text,
    ...

    PRIMARY KEY (place_id, place_type),
    FOREIGN KEY (place_id, place_type) references places (place_id, place_type) ON DELETE CASCADE
);

para isolar tabelas internas do acesso a bancos de dados de terceiros, criamos esquemas especificamente para esse caso de uso. Logins/usuários de banco de dados de terceiros só têm acesso a esses esquemas "públicos".
O problema é que o usuário tinha mais permissões do que deveria e muitas consultas de terceiros acessam as tabelas internas.
Como não posso remover as permissões abruptamente, gostaria de fazer isso aos poucos. Identificar consultas problemáticas e alterá-las de acordo.

Para isso tentei utilizar os recursos de Auditoria de Banco de Dados do Sql Server, mas parece que estou faltando informações para o que preciso:
Digamos que tenho a tabela [Internal].[Machine]e a View [Public].[Machine]que para simplificar é apenas select * from [Internal].[Machine]. O usuário viewersó tem permissão para selecionar [Public].[Machine].

Adicionar uma "especificação de auditoria de banco de dados" para (Type=SELECT, ObjectClass=SCHEMA, ObjectName=Internal, PrincipalName=viewer) produz uma entrada para select * from [Internal].[Machine]e select * from [Public].[Machine].
Meu objetivo é ter apenas uma entrada para select * from [Internal].[Machine], ou seja, quando o objeto for acessado diretamente pela instrução de consulta. E até onde vi, as linhas produzidas não contêm as informações necessárias para diferenciar as duas.

Isso é de alguma forma possível com o recurso Auditoria/Eventos Estendidos do Sql Server (ou algo completamente diferente)?

Estamos administrando banco de dados em kubernetes para nossos clientes. Para cada novo cluster, criamos um novo usuário com CREATE ROLEfunções CREATE DBatribuídas a eles, para que possam criar seus próprios bancos de dados e novos usuários com funções mais restritas.

O problema é que, com CREATE ROLEpermissão, o usuário pode CONCEDER a si mesmo pg_execute_server_programo papel e, em seguida, usar o ataque reverso do shell e, em seguida, obter o shell do nosso pod e ler as variáveis ​​de ambiente, o que não é desejado. Por exemplo, temos vários segredos em ambientes que o cliente pode aproveitar e então aumentar seu alcance de ataque e assumir mais coisas.

Resumindo, quero ter um usuário que possa criar novos usuários, mas não possa conceder a si mesmo funções predefinidas específicas.

Atualização: procurei e descobri que ADMIN_OPTIONfoi adicionado no PostgreSQL 16 para resolver esse problema. Meu problema é que estamos usando o PostgreSQL versão 13,14,15 e não podemos simplesmente forçar a atualização de todos os clusters.