Cerin's questions

Como você revoga todos os privilégios de um usuário no Postgres e depois descarta o usuário?

Os documentos dizem que deveria ser tão simples quanto:

REVOKE ALL PRIVILEGES ON DATABASE database FROM user;
DROP USER user;

No entanto, se eu abrir meu prompt do psql, recebo uma saída bizarra:

mydb=> REVOKE ALL PRIVILEGES ON DATABASE mydb FROM myuser;
REVOKE
mydb=> DROP USER myuser;
ERROR:  role "myuser" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema myschema

Uh o quê? Ok, então só preciso revogar explicitamente as permissões em "myschema"? Mas então eu recebo:

mydb=> REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema FROM myuser;
REVOKE
mydb=> DROP USER myuser;
ERROR:  role "myuser" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema myschema

Está dizendo que está revogando com sucesso todo o acesso ao banco de dados e ao esquema, mas ainda assim o acesso ao esquema permanece, impedindo-me de descartar o usuário.

Ainda mais bizarro, se eu tentar confirmar se o usuário tem acesso ao esquema com:

SELECT *
FROM pg_namespace
WHERE has_schema_privilege('myuser', 'myschema', 'USAGE');

não retorna nada, exceto "pg_catalog", "information_schema" e "public".

O que estou fazendo de errado aqui?

Como você aceleraria uma consulta do Postgres que está tentando filtrar em uma coluna de data entre uma data de início e término?

Estou executando uma consulta como:

SELECT * FROM record WHERE tag_id IN (1,2,3) AND person_id = 1 AND created >= '2022-1-1' AND created < '2022-6-1'
ORDER BY priority DESC LIMIT 100;

em uma tabela com milhões de linhas. No entanto, apenas alguns milhares de linhas devem ser aplicadas à minha consulta e tenho alguns índices que devem cobrir exatamente os critérios, como:

CREATE INDEX record_tag_priority_person_index
ON public.record USING btree
(tag_id ASC NULLS LAST, priority DESC NULLS LAST, person_id ASC NULLS LAST)
WHERE (tag_id = ANY (ARRAY[1, 2, 3])) AND person_id = 1;

CREATE INDEX record_created_index
ON public.record USING btree
(created ASC NULLS LAST);

No entanto, mesmo com esses índices, a consulta ainda leva cerca de 18 minutos para ser executada.

Se eu executar um EXPLAINna minha consulta, ele mostra:

"Limit  (cost=155990.12..155990.37 rows=100 width=165) (actual time=1104683.783..1104683.799 rows=100 loops=1)"
"  ->  Sort  (cost=155990.12..156078.05 rows=35170 width=165) (actual time=1104683.782..1104683.789 rows=100 loops=1)"
"        Sort Key: priority DESC"
"        Sort Method: top-N heapsort  Memory: 58kB"
"        ->  Bitmap Heap Scan on record  (cost=27359.52..154645.95 rows=35170 width=165) (actual time=556.641..1104569.771 rows=32804 loops=1)"
"              Recheck Cond: ((created >= '2022-01-01 04:00:00+00'::timestamp with time zone) AND (created < '2022-6-1 04:00:00+00'::timestamp with time zone) AND (tag_id = ANY ('{1,2,3}'::integer[])) AND (person_id = 1))"
"              Rows Removed by Index Recheck: 1103447"
"              Heap Blocks: exact=35800 lossy=99400"
"              ->  BitmapAnd  (cost=27359.47..27359.47 rows=35170 width=0) (actual time=547.819..547.821 rows=0 loops=1)"
"                    ->  Bitmap Index Scan on record_created_index  (cost=0.00..8666.93 rows=409449 width=0) (actual time=244.146..244.146 rows=309261 loops=1)"
"                          Index Cond: ((created >= '2022-01-01 04:00:00+00'::timestamp with time zone) AND (created < '2022-6-1 04:00:00+00'::timestamp with time zone))"
"                    ->  Bitmap Index Scan on record_tag_priority_person_index  (cost=0.00..18674.71 rows=2043655 width=0) (actual time=293.201..293.202 rows=2029783 loops=1)"
"Planning Time: 118.456 ms"
"Execution Time: 1104683.854 ms"

Portanto, está usando ambos os meus índices, mas ainda está demorando uma eternidade para encontrar os primeiros 100 resultados.

Como acelero isso? Meus índices são ineficientes?

Eu tentei combinar os dois índices em um índice parcial como:

CREATE INDEX record_tag_priority_person_created_index
ON public.record USING btree
(tag_id ASC NULLS LAST, priority DESC NULLS LAST, person_id ASC NULLS LAST, created DESC)
WHERE (tag_id = ANY (ARRAY[1, 2, 3])) AND person_id = 1;

mas o planejador não o pega e continua a usar os dois índices separados.

Eu tenho uma tabela com vários milhões de registros em um banco de dados PostgreSQL 12 e, após uma atualização de 11 para 12, algumas consultas começaram a ter um desempenho horrível. Eles passaram de ~ 1 segundo para ~ 5 minutos. Tentei reconstruir todos os índices, aspirar e todas as frutas comuns do Postgres, mas o desempenho ainda é terrível.

Esta é a consulta:

SELECT id, activity_count
FROM user
WHERE (search_index) @@ (to_tsquery('pg_catalog.english', '''1234567890'':*') AND active = true
ORDER BY activity_count DESC LIMIT 101

Em outras palavras, encontre todos os usuários ativos correspondentes ao número de conta fornecido e classifique do mais ativo para o menos.

Essa consulta leva cerca de 5 minutos para retornar apenas 2 registros. Alguma coisa não está certa.

A coluna search_indexé um tsvector que armazena todas as palavras-chave dos vários campos de texto da tabela (apenas coisas como account_number, name, etc).

Eu tenho um índice GIN criado para esta coluna com:

CREATE INDEX user_search_index_gin
    ON public.user USING gin
    (search_index)
    TABLESPACE pg_default;

Eu também tenho um índice para a activecoluna com:

CREATE INDEX user_active
    ON public.user USING btree
    (active ASC NULLS LAST)
    TABLESPACE pg_default;

E eu tenho um índice ordenado para o activity_countcom:

CREATE INDEX user_activity_count
    ON public.user USING btree
    (activity_count ASC NULLS LAST)
    TABLESPACE pg_default;

No entanto, quando executo EXPLAIN, recebo:

"Limit  (cost=0.56..11443.66 rows=101 width=1552)"
"  ->  Index Scan Backward using user_activity_count on user  (cost=0.56..36010185.91 rows=317836 width=1552)"
"        Filter: (active AND (search_index @@ '''1234567890'':*'::tsquery))"

Por que está usando apenas o índice user_activity_count e não o índice GIN mais eficiente? Como faço para corrigir isso?

Como você diagnostica por que uma instância de psqlestá travando no meio da execução de um script SQL?

Eu tenho um script de saneamento com várias UPDATEdeclarações como:

UPDATE table1 SET column1 = CONCAT('stuff', id);
UPDATE table2 SET column2 = CONCAT('stuff', id);
...

O script faz parte de uma rotina automatizada para preparar um banco de dados de desenvolvimento. No entanto, isso está me bloqueando porque o script trava indefinidamente.

Há cerca de uma centena de declarações como esta no arquivo, mas, mais ou menos na metade, o script trava. Quando executo novamente o processo a partir de um script bash pai, ele trava em um ponto diferente, portanto, não é uma instrução específica que está causando o problema. Além disso, quando executo o psqlcomando manualmente, ele funciona perfeitamente sem travar.

A chamada que estou executando é:

psql --user=myuser --no-password --host=localhost -d mydatabase -a -f sanitize.sql

Como ele falha de forma não determinística quando executado de dentro do Bash, mas é bem-sucedido quando o executo diretamente em um shell, isso provavelmente é parte do problema, mas não tenho certeza do que investigar, pois nenhum erro está sendo gerado. Por onde começo a investigar isso?

Estou tentando executar o mysqldump para criar um instantâneo do banco de dados e descobri que ele irá parar aleatoriamente no meio do caminho, sem relatar nenhum erro. Meu banco de dados é relativamente pequeno (cerca de 100 MB) e está usando o InnoDB.

Estou executando assim:

mysqldump --force --single-transaction --quick --user myuser --password=mypass -h mydatabasehost mydb > /tmp/snapshot.sql

Verificar os relatórios de código de saída 0.

Minha versão é: mysqldump Ver 10.13 Distrib 5.1.52, para redhat-linux-gnu (i386)

Eu vi algumas postagens semelhantes e até um relatório de bug oficial , mas nenhuma das soluções parece se aplicar.

Como faço para que o mysqldump tire um instantâneo completo do banco de dados?

EDIT: Meu banco de dados atualmente reside no RDS da Amazon.