dezso's questions

Em um pool SQL dedicado do Azure Synapse, tenho a seguinte configuração:

-- a custom DB role to manage privileges
CREATE ROLE [owner];

-- there is a schema owned by this role
CREATE SCHEMA [myschema] AUTHORIZATION [owner];

-- an Azure AD group to allow its members to log in
CREATE USER [radish] FROM EXTERNAL PROVIDER;

-- the AAD group is a member of the owner role
EXEC sp_addrolemember 'owner', 'radish';

-- privileges are assigned exclusively through custom DB roles
GRANT ALTER, CONTROL on SCHEMA::[myschema] TO [owner];

Se eu fizer login, sendo membro do radishgrupo AAD, não consigo criar uma tabela em myschema:

CREATE TABLE [myschema].[test] (id int);

Msg 6004, Level 14, State 9, Line 1
User does not have permission to perform this action.

Depois de obter a função de administrador do Synapse, isso funciona. (Quando um membro, SESSION_USERé dbo.) Isso não é surpresa, pois essa função pode fazer basicamente tudo dentro dos pools SQL de um determinado espaço de trabalho.

No entanto, quando eu me removo da função de administrador do Synapse, acontece o seguinte:

DROP TABLE [myschema].[test];
-- completes successfully, but then:

CREATE TABLE [myschema].[test] (id int);

Msg 6004, Level 14, State 9, Line 1
User does not have permission to perform this action.

Provavelmente meu entendimento sobre como os privilégios necessários CREATE TABLEe DROP TABLEse relacionam entre si está totalmente errado, mas até agora eu achava que eram os mesmos privilégios necessários para ambos. Alguém pode me mostrar onde meu pensamento está errado?

Até agora vejo as seguintes possibilidades:

• a ownerfunção não se aplica a usuários que são apenas indiretamente membros dela (ou seja, por meio de associação ao grupo AAD) - isso parece contradizer https://learn.microsoft.com/en-us/azure/synapse-analytics/security /how-to-set-up-access-control#step-8-add-users-to-security-groups
• a tabela criada ao ter privilégios suficientes é de minha propriedade e não a função de proprietário do esquema. A seção 'Cuidado' contradiz: 'Um usuário com permissão ALTER em um esquema pode criar procedimentos, sinônimos e visualizações que pertencem ao proprietário do esquema.' Além disso, sp_tablesrelatórios ownercomo sendo table_owner.

Além disso, curiosamente, posso fazer um GRANT ALTER, CONTROL on SCHEMA::[myschema] TO [owner];com sucesso, enquanto a criação da tabela falha.

Estou com uma situação estranha, visto no log:

Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807. 
Process 53807 waits for ExclusiveLock on advisory lock [16421,999999,12864395,2]; blocked by process 37278. 
Process 37278: SELECT * FROM zel_api.insert_event ($1 ,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24) 
Process 53807: SELECT * FROM zel_api.insert_event ($1 ,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24)",
"See server log for query details.",,,"SQL statement ""SELECT pg_advisory_xact_lock(999999, format('zel_event.%I', p_table_name)::regclass::oid::integer)""

Isso já é estranho por si só, pois parece que dois processos bloqueiam o mesmo bloqueio consultivo, mas nenhum deles pode agarrá-lo.

A função que tenta adquirir o bloqueio é a seguinte:

CREATE OR REPLACE FUNCTION zel_event.create_new_partition(
    p_table_name text
)
RETURNS void AS
$BODY$
DECLARE
    _schema text;
BEGIN
    IF NOT EXISTS (table from catalog)
    THEN
        PERFORM pg_advisory_xact_lock(999999, format('zel_event.%I', p_table_name)::regclass::oid::integer);

        IF NOT EXISTS (table from catalog)
        THEN
            EXECUTE format('
                CREATE TABLE %1$I.%2$I (
                    LIKE zel_event.%2$I INCLUDING ALL
                ) INHERITS (zel_event.%2$I)', _schema, p_table_name);
        END IF;
    END IF;
    RETURN;

END;
$BODY$
LANGUAGE plpgsql
SECURITY DEFINER;

A meu ver, nenhuma das causas de impasse 'clássico' está aqui, pois há apenas uma lógica ...
A ideia por trás disso é criar a nova tabela sob demanda onde a demanda pode vir em uma frequência muito alta de várias conexões. É chamado quando uma função de inserção faz o insert destinado à tabela pai, sendo desviado por uma trigger do despachante para a tabela filha apropriada.

É assim que a transação se parece:

INSERT INTO parent (zel_api.insert_event())
        |
     trigger
        |
        +----> child exists? ---no---> CREATE TABLE child
                      |                         |
                     yes                        |
                      |                         |
                      V                         V
              INSERT INTO child (zel_event.create_new_partition())   

A versão do PostgreSQL é 9.2

Seria muito interessante ter alguns insights de como isso pode acontecer (e mitigado, claro).

Eu tenho que coletar algumas informações das funções do PostgreSQL. Uma informação é se a função tem configurações diferentes de 'usual', usual significando aqui valores padrão para apenas alguns parâmetros (por exemplo, log_statement).

Até agora só tenho uma ideia: fazer um loop em todos os usuários e emitir os comandos

\c postgres username

SHOW log_statement;

e, em seguida, processar a saída.

Estou me perguntando se existe um método semelhante, como consultar a pg_settingsexibição, mostrando as configurações por usuário? Não preciso saber as diferenças específicas da sessão, apenas aquelas que persistem.

Eu estava me perguntando sobre o desempenho relativo das soluções fornecidas nas respostas a uma pergunta no stackoverflow , resolvi fazer alguns testes.

O OP queria obter a primeira linha correspondente, dado um conjunto de condições com precedência decrescente. Ambas as soluções envolviam uma pseudocoluna, mas uma (a minha) envolvia várias SELECTdeclarações UNION ALLunidas, enquanto a outra construía uma CASEexpressão.

Compartilho meus resultados na esperança de que alguém ache isso útil.

Eu tenho o seguinte problema.

SELECT * FROM tgvbn();
ERROR:  function tgvbn() does not exist
LINE 1: SELECT * FROM tgvbn();
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Como o Apêndice A da documentação oficial indica apenas códigos de erro e nomes condicionais, mas não as mensagens reais, pode-se supor que "Nenhuma função corresponde a..." refere-se a 42883 / undefined_function. Posso definir log_error_verbosity = verbose- postgresql.confmas isso afetará os logs, não as mensagens retornadas ao cliente:

Controla a quantidade de detalhes gravados no log do servidor para cada mensagem registrada.

Bem, posso consultar os logs para obter essa informação, mas há algum meio de incluí-la nas mensagens de erro?

Vamos considerar o seguinte exemplo (do início de um script psql):

\c :db_to_run_on

TRUNCATE the_most_important_table;
-- tried to avoid similarities to anything that exists out there

Agora, se for executado pelo comando

psql [connection details] -v db_to_run_on=\'dev_database\'

então ele simplesmente roda e o usuário fica feliz. Mas e se ele decidir especificar -v db_to_run_on=production_database? (Vamos supor que isso possa acontecer, assim como as pessoas executam rm -rf / # don't try this at home!!!ocasionalmente.) Espero que haja um novo backup dessa tabela ...

Então surge a pergunta: como verificar as variáveis ​​passadas para um script e interromper o processamento posterior com base em seu valor?

Já estou pensando nisso há muito tempo.

A questão básica é: como testar unitariamente procedimentos armazenados?

Vejo que posso configurar testes de unidade com relativa facilidade para funções no sentido clássico (quero dizer, elas obtêm zero ou mais argumentos e retornam um valor). Mas se eu considerar um exemplo da vida real de um procedimento aparentemente simples inserindo uma linha em algum lugar, com alguns gatilhos fazendo isso e aquilo antes ou depois da inserção, até mesmo definir os limites de uma 'unidade' é bastante difícil. Devo testar apenas o INSERTpróprio? Isso é bastante simples, eu acho – com valor relativamente baixo. Devo testar o resultado de toda a cadeia de eventos? Além da questão de saber se este é um teste de unidade ou não, projetar um teste adequado pode ser um trabalho bastante árduo, com muitos pontos de interrogação adicionais surgindo no caminho.

E então vem o problema de dados em constante mudança. No caso de UPDATEafetar mais do que apenas algumas linhas, cada linha potencialmente afetada deve ser incluída de alguma forma nos casos de teste. Outras dificuldades com DELETEs e assim por diante e assim por diante.

Então, como você testa seus procedimentos armazenados? Existe um limite na complexidade onde fica completamente sem esperança? Quais recursos são necessários para a manutenção?

EDITAR Mais uma pequena pergunta, baseada na resposta de AlexKuznetsov: Ou existe um limite sob o qual é completamente inútil?

Eu tenho as seguintes definições de tabela e índice:

CREATE TABLE munkalap (
    munkalap_id serial PRIMARY KEY,
    ...
);

CREATE TABLE munkalap_lepes (
    munkalap_lepes_id serial PRIMARY KEY,
    munkalap_id integer REFERENCES munkalap (munkalap_id),
    ...
);

CREATE INDEX idx_munkalap_lepes_munkalap_id ON munkalap_lepes (munkalap_id);

Por que nenhum dos índices em munkalap_id é usado na consulta a seguir?

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id);

QUERY PLAN
Hash Join  (cost=119.17..2050.88 rows=38046 width=214) (actual time=0.824..18.011 rows=38046 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.005..4.574 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=3252 width=4) (actual time=0.810..0.810 rows=3253 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 115kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=3252 width=4) (actual time=0.003..0.398 rows=3253 loops=1)
Total runtime: 19.786 ms

É o mesmo, mesmo se eu adicionar um filtro:

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id) WHERE NOT lezarva;

QUERY PLAN
Hash Join  (cost=79.60..1545.79 rows=1006 width=214) (actual time=0.616..10.824 rows=964 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.007..5.061 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=86 width=4) (actual time=0.587..0.587 rows=87 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 4kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=86 width=4) (actual time=0.014..0.560 rows=87 loops=1)
              Filter: (NOT lezarva)
Total runtime: 10.911 ms

Eu tenho uma visão que é incrivelmente lenta :) Ela junta apenas duas tabelas, tendo algumas condições WHERE simples e uma na forma de uma subconsulta (veja SubPlan1 abaixo). A condição mais simples é verificada como uma varredura de índice, que é de alguma forma terrivelmente lenta:

QUERY PLAN
Result  (cost=19.41..570789.79 rows=18 width=4) (actual time=18724.919..18724.919 rows=0 loops=1)
  One-Time Filter: ((hashed SubPlan 2) AND (('now'::text)::time with time zone >= ($2)::time with time zone) AND (('now'::text)::time with time zone <= ($3)::time with time zone))
  InitPlan 3 (returns $2)
    ->  Function Scan on f_konstansok_select f  (cost=0.26..0.27 rows=1 width=146) (actual time=0.147..0.147 rows=1 loops=1)
  InitPlan 4 (returns $3)
    ->  Function Scan on f_konstansok_select f  (cost=0.26..0.27 rows=1 width=146) (actual time=0.103..0.103 rows=1 loops=1)
  ->  Nested Loop  (cost=18.87..570789.25 rows=18 width=4) (actual time=18724.357..18724.357 rows=0 loops=1)
        Join Filter: (((ml.idopont_tervezett)::timestamp without time zone < now()) OR ((ml.idopont_tervezett IS NULL) AND ((ml.idopont + (((ms.hatarido)::text || ' minutes'::text))::interval) < now())))
        ->  Nested Loop Semi Join  (cost=0.00..570746.76 rows=42 width=45) (actual time=6.393..18711.516 rows=61 loops=1)
              ->  Index Scan using idx_munkalap_lepes_aktualis on munkalap_lepes ml  (cost=0.00..570080.42 rows=1634 width=45) (actual time=6.379..18678.306 rows=3256 loops=1)
                    Index Cond: (aktualis = true)
                    Filter: (NOT (SubPlan 1))
                    SubPlan 1
                      ->  Nested Loop  (cost=681.75..2041.93 rows=1 width=0) (actual time=5.712..5.712 rows=0 loops=3268)
                            ->  Hash Join  (cost=681.75..2033.65 rows=1 width=4) (actual time=5.711..5.711 rows=0 loops=3268)
                                  Hash Cond: (ls.munkalap_statusz_figyelmeztetes_id = msf.munkalap_statusz_figyelmeztetes_id)
                                  Join Filter: (ls.idopont > (now() - (((msf.figyelmeztetes_periodus)::text || ' minutes'::text))::interval))
                                  ->  Bitmap Heap Scan on lepes_sorrend ls  (cost=680.26..2032.12 rows=1 width=16) (actual time=5.706..5.707 rows=0 loops=3268)
                                        Recheck Cond: (munkalap_statusz_figyelmeztetes_id IS NOT NULL)
                                        Filter: (munkalap_lepes_id = ml.munkalap_lepes_id)
                                        ->  Bitmap Index Scan on lepes_sorrend_idx2  (cost=0.00..680.26 rows=36389 width=0) (actual time=1.814..1.814 rows=36337 loops=3268)
                                              Index Cond: (munkalap_statusz_figyelmeztetes_id IS NOT NULL)
                                  ->  Hash  (cost=1.22..1.22 rows=22 width=8) (actual time=0.009..0.009 rows=22 loops=1)
                                        Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                        ->  Seq Scan on munkalap_statusz_figyelmeztetes msf  (cost=0.00..1.22 rows=22 width=8) (actual time=0.003..0.004 rows=22 loops=1)
                            ->  Index Scan using pk_munkalap_lepes on munkalap_lepes ml2  (cost=0.00..8.27 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=12)
                                  Index Cond: (munkalap_lepes_id = ml.munkalap_lepes_id)
                                  Filter: aktualis
              ->  Index Scan using pk_munkalap on munkalap m  (cost=0.00..0.41 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=3256)
                    Index Cond: (munkalap_id = ml.munkalap_id)
                    Filter: ((NOT lezarva) AND (NOT torolve))
        ->  Index Scan using pk_munkalap_statusz on munkalap_statusz ms  (cost=0.00..0.27 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=61)
              Index Cond: (munkalap_statusz_id = ml.munkalap_statusz_id)
  SubPlan 2
    ->  Seq Scan on munkanapok  (cost=0.00..16.27 rows=1027 width=4) (actual time=0.008..0.113 rows=1027 loops=1)
Total runtime: 18725.001 ms

A definição do índice é:

CREATE INDEX idx_munkalap_lepes_aktualis ON hibabejelentes.munkalap_lepes
  USING btree (aktualis)
  WHERE aktualis;

'aktualis' é um sinalizador booleano, o nome significa 'atual' (para aqueles que se perguntaram :)

Depois de jogá-lo por um tempo, ainda não tenho ideia de como torná-lo rápido ... omitir as outras condições torna a consulta razoavelmente rápida:

QUERY PLAN
Hash Join  (cost=2.01..428.98 rows=3271 width=4) (actual time=0.449..3.780 rows=3268 loops=1)
  Hash Cond: (ml.munkalap_statusz_id = ms.munkalap_statusz_id)
  ->  Index Scan using idx_munkalap_lepes_aktualis on munkalap_lepes ml  (cost=0.00..381.99 rows=3271 width=8) (actual time=0.410..2.846 rows=3268 loops=1)
        Index Cond: (aktualis = true)
  ->  Hash  (cost=1.45..1.45 rows=45 width=4) (actual time=0.025..0.025 rows=45 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 2kB
        ->  Seq Scan on munkalap_statusz ms  (cost=0.00..1.45 rows=45 width=4) (actual time=0.007..0.012 rows=45 loops=1)
Total runtime: 3.971 ms

Todas as mesas são autovacuumed.

Agradecemos antecipadamente por qualquer idéia.

EDIT: Instrução SELECT incluída

CREATE OR REPLACE VIEW hibabejelentes.v_munkalap_figyelmeztetes_kuldes(
    munkalap_id)
AS
  SELECT ml.munkalap_id
  FROM munkalap_lepes ml
       JOIN munkalap_statusz ms USING (munkalap_statusz_id)
  WHERE ml.aktualis AND
        (ml.idopont_tervezett::timestamp without time zone < now() OR
        ml.idopont_tervezett IS NULL AND
        (ml.idopont +((ms.hatarido || ' minutes' ::text) ::interval)) < now()) AND

        NOT (EXISTS (
                      SELECT ls.munkalap_statusz_figyelmeztetes_id
                      FROM lepes_sorrend ls
                           JOIN munkalap_lepes ml2 USING (munkalap_lepes_id)
                           JOIN munkalap_statusz_figyelmeztetes msf USING (
                           munkalap_statusz_figyelmeztetes_id)
                      WHERE ml2.aktualis AND
                            ml2.munkalap_lepes_id = ml.munkalap_lepes_id AND
                            ls.munkalap_statusz_figyelmeztetes_id IS NOT NULL AND
                            ls.idopont >(now() -((msf.figyelmeztetes_periodus ||
                             ' minutes' ::text) ::interval))
        )) AND
        ('now' ::text::date IN (
                                 SELECT munkanapok.datum
                                 FROM munkanapok
        )) AND
        'now'        ::text::time with time zone >=((
                                                      SELECT f.ertek::time
                                                       without time zone AS
                                                        ertek
                                                      FROM f_konstansok_select(
                                                      'MUNKAIDO_ELEJE' ::text,
                                                       'now' ::text::date,
                                                        NULL::integer) f(
                                                        konstans_id,
                                                         konstans_nev, datum,
                                                          ertek, torolve,
                                                           bolthalozat_id)
        )) ::time with time zone AND
        'now'        ::text::time with time zone <=((
                                                      SELECT f.ertek::time
                                                       without time zone AS
                                                        ertek
                                                      FROM f_konstansok_select(
                                                      'MUNKAIDO_VEGE' ::text,
                                                       'now' ::text::date,
                                                        NULL::integer) f(
                                                        konstans_id,
                                                         konstans_nev, datum,
                                                          ertek, torolve,
                                                           bolthalozat_id)
        )) ::time with time zone;