gavenkoa's questions

Fiz a tabela de teste:

create table tmp (id BIGINT NOT NULL IDENTITY PRIMARY KEY)

Agora eu quero inserir um valor:

insert into tmp () values();

e reclama:

SQL Error [102] [S0001]: Incorrect syntax near ')'.

Não consigo inserir ID, porque falha com:

insert into tmp (id) values(1);
-- SQL Error [544] [S0001]: Cannot insert explicit value for identity column
-- in table 'tmp' when IDENTITY_INSERT is set to OFF.

DEFAULTsintaxe para VALUEStambém é proibida:

insert into tmp values(default);
-- SQL Error [8101] [S0001]: An explicit value for the identity column in table 'tmp'
-- can only be specified when a column list is used and IDENTITY_INSERT is ON.

É possível mover dados para a nova tabela originando outras tabelas sem perder o progresso no meio?

Eu penso em plpgsqller os dados por partes e confirmar após cada N inserções... Se o processo parar no meio, a condição NOT EXISTS (...)permite adicionar dados ausentes repetindo o processo novamente.

Eu acho que é um padrão importante e pode haver suporte para ele diretamente com a sintaxe:

INSERT INTO ... SELECT ... WHERE ... AND NOT EXISTS (...)

O motivo da interrupção da cópia pode ser uma restrição ou uma pausa do usuário em uma operação. Ainda queremos não perder o progresso e retomar o processo (com uma ajuda de NOT EXISTS (...)guarda).

Estou tentando entender o conceito de "autocommit".

Originalmente, eu gosto disso no contexto da API Java JDBC Connection.setAutoCommit(boolean autoCommit)

A pesquisa rápida revelou que o termo não é específico para JDBC, o PostgreSQL também se refere a "autocommit":

https://www.postgresql.org/docs/current/sql-start-transaction.html

No padrão, não é necessário emitir START TRANSACTIONpara iniciar um bloco de transação: qualquer comando SQL inicia um bloco implicitamente. O comportamento do PostgreSQL pode ser visto como a emissão implícita de um COMMIT após cada comando que não segue START TRANSACTION(ou BEGIN), e por isso é freqüentemente chamado de “autocommit” . Outros sistemas de banco de dados relacionais podem oferecer um recurso de confirmação automática como conveniência.

Se for generalizado, qual é o nível de isolamento do modo de confirmação automática ?

Conceitualmente, como temos apenas uma instrução SQL antes COMMIT dos níveis de isolamento implícito, quase não faz sentido para o autocommit . Apenas READ UNCOMMITTED pode influenciar o resultado, outras anomalias são definidas em termos de uma sequência de instruções SQL.

Posso alterar o nível de isolamento do autocommit para / longe de READ UNCOMMITTED?

Eu realmente não entendo promessas sobre esperas em caso de acesso simultâneo a dados.

Todos os manuais operam por termo lock . Ninguém explica que o bloqueio provavelmente faz com que os clientes SQL aguardem a aquisição do bloqueio no lado do servidor (e pode levar segundos/minutos/infinito)?

Existem casos em que os bloqueios não causam esperas? Eu posso imaginar:

• provavelmente READ UNCOMMITTEDem algumas situações, como não há DDL?
• erro é reportado sem esperar...
• ninguém espera, mas primeiro quem faz commitganha, outros falham em seuscommit

Eu revisei a API JDBC : ela menciona literalmente:

SQLTimeoutException - quando o driver determinou que o valor de tempo limite especificado pelo método setQueryTimeout foi excedido e pelo menos tentou cancelar a instrução atualmente em execução

De acordo com os documentos, o tempo limite é detectado no lado do cliente . A API do cliente amplamente usada não tem influência nos tempos limite do lado do servidor ( incluindo causados ​​por bloqueios ). Provavelmente você pode definir algumas propriedades de conexão proprietárias para influenciar o comportamento do banco de dados.

Eu vejo algumas extensões SQL proprietárias, como:

• https://learn.microsoft.com/en-us/sql/t-sql/statements/set-lock-timeout-transact-sql SET LOCK_TIMEOUT timeout_period
• https://www.postgresql.org/docs/13/runtime-config-client.html set lock_timeout ms &set statement_timeout ms

Para mim, as esperas de banco de dados devido a bloqueios são uma área cinzenta. Um bloqueio significa uma espera?

Não está claro a estratégia que o Postgres adota quando as colunas são definidas como NULL:

UPDATE tbl SET
  col1 = NULL,
  col2 = NULL
WHERE created < current_date - INTERVAL '1 year';

A documentação https://www.postgresql.org/docs/current/mvcc.html é um pouco longa e técnica, então não posso deduzir com segurança:

se a configuração para NULL for executada no local ou as linhas/páginas afetadas forem copiadas ?

Parece que qualquer UPDATE deve criar uma nova linha para a semântica MVCC, mas e se a configuração para NULL for um caso especial?

Para conformidade com o GDPR, penso em anular todos os dados históricos pessoais e tento entender as implicações do UPDATE SET x = NULL. Devo pensar VACUUMdepois disso?

Temos vários esquemas de nomenclatura para colunas (como id/ keyou created/ creation_date/ create_date) e queremos unificá-los.

Também queremos fazê-lo de forma compatível com versões anteriores . Queremos poder executar o código antigo junto com o novo até nos livrarmos do código antigo, seguindo:

ALTER TABLE table_name 
RENAME COLUMN column_name TO new_column_name;

é uma mudança de ruptura.

Existe alguma forma de alias para colunas como visualizações para tabelas (para Postgresql)?

RESPOSTA @CL (tnx para dica!) https://www.postgresql.org/docs/12/ddl-generated-columns.html

Uma coluna gerada não pode ser gravada diretamente. Nos comandos INSERT ou UPDATE, um valor não pode ser especificado para uma coluna gerada, mas a palavra-chave DEFAULT pode ser especificada.

Portanto, se uma ideia é manter colunas até atualizar todo o script de relatório - tudo bem. Mas se você quiser uma reversão para a versão anterior do aplicativo - você está sem sorte ((

Atualmente, estamos na oferta do Google Postgres Cloud: https://cloud.google.com/sql/docs/postgres/db-versions e a v12 está apenas na versão beta.

Importamos taxas de câmbio para DB:

CREATE TABLE currency_rate (
    id int8 NOT NULL,
    date date NOT NULL,
    currency varchar(3) NOT NULL,
    rate numeric(12,6) NOT NULL,
    CONSTRAINT currency_rate_pk PRIMARY KEY (id)
);

ALTER TABLE currency_rate add constraint currency_rate_un UNIQUE (currency, date);

mas, na verdade, precisamos apenas da última taxa disponível para trabalhar.

É complicado escrever CTE com sort e distinct on (currency):

with cr as (
  select distinct on (currency) currency, rate from currency_rate
  order by currency, date)
select
  ...,
  sum((nd.original_amount - nd.new_amount)*cr.rate) as amount
from notification_data nd
join cr on cr.currency = nd.currency
...

A consulta tem o seguinte plano de execução que é bom :

  CTE cr
    ->  Result  (cost=0.28..69.66 rows=13 width=16)
          ->  Unique  (cost=0.28..69.66 rows=13 width=16)
                ->  Index Scan using currency_rate_un on currency_rate  (cost=0.28..67.17 rows=995 width=16)
  ...
              ->  Hash Join  (cost=1029.26..57129.68 rows=18 width=60)
                    Hash Cond: ((nd.currency)::text = (cr.currency)::text)

Eu criei a visualização:

CREATE OR REPLACE VIEW latest_currency_rate AS
SELECT
  DISTINCT ON (currency) currency, rate, date
FROM currency_rate
ORDER BY currency, date DESC;

mas o otimizador de banco de dados não usa o índice de currency_rate_un:

explain select * from latest_currency_rate;

Unique  (cost=60.83..65.38 rows=12 width=16)
  ->  Sort  (cost=60.83..63.10 rows=910 width=16)
        Sort Key: currency_rate.currency, currency_rate.date DESC
        ->  Seq Scan on currency_rate  (cost=0.00..16.10 rows=910 width=16)

e até para:

explain select * from latest_currency_rate where currency = 'USD';

Unique  (cost=16.87..17.13 rows=12 width=16)
  ->  Sort  (cost=16.87..17.13 rows=104 width=16)
        Sort Key: currency_rate.date DESC
        ->  Bitmap Heap Scan on currency_rate  (cost=5.08..13.38 rows=104 width=16)
              Recheck Cond: ((currency)::text = 'USD'::text)
              ->  Bitmap Index Scan on currency_rate_un  (cost=0.00..5.06 rows=104 width=0)
                    Index Cond: ((currency)::text = 'USD'::text)

A integração da nova visualização à consulta original fornece:

explain select
  sum((nd.original_amount - nd.new_amount)*cr.rate) as amount
from notification_data nd
join latest_currency_rate cr on cr.currency = nd.currency
...

...
 ->  Hash  (cost=73.54..73.54 rows=13 width=12)
       ->  Subquery Scan on cr  (cost=68.37..73.54 rows=13 width=12)
             ->  Unique  (cost=68.37..73.41 rows=13 width=16)
                   ->  Sort  (cost=68.37..70.89 rows=1008 width=16)
                         Sort Key: currency_rate.currency, currency_rate.date DESC
                         ->  Seq Scan on currency_rate  (cost=0.00..18.08 rows=1008 width=16)
...

Agora estou intrigado. Por que a consulta CTE original usa Index Scane a exibição não usa o mesmo índice?

Devo reescrever a visão com algum truque alternativo (em vez de distinct on)?

Estou pensando em ir com materialized viewpara evitar varreduras sequenciais ...

Suponha que criamos um índice parcial no Postgres que evita dados NULL inúteis:

 CREATE INDEX my_ix ON my (col1) WHERE col1 IS NOT NULL;

Devo escrever a consulta SELECT como:

SELECT * FROM my WHERE col1 = 'abc';

ou como:

SELECT * FROM my WHERE col1 = 'abc' AND col1 IS NOT NULL;

No caso de bibliotecas de conexão JDBC, pode ser assim:

SELECT * FROM my WHERE col1 = ?;

e não tenho certeza se o Postgres pode inferir a aplicabilidade do índice sem:

 AND col1 IS NOT NULL

Também é interessante se o Postgres puder inferir transitividade, para índice:

 CREATE INDEX my_ix ON my (col1) WHERE col1 > 0;

Espero não precisar escrever:

SELECT * FROM my WHERE col1 > ? AND col1 > 0;

se os parâmetros forem 10, 23, etc (> 0).

A restrição e o índice do Postgres FK são independentes.

Recentemente, fui procurado por índices não utilizados e encontrei um truque para eliminar os índices necessários para a imposição de restrições:

 WHERE NOT EXISTS (SELECT 1 
                   FROM pg_constraint c 
                   WHERE c.conindid = pg_stat_user_indexes.indexrelid)

Criamos restrições FK independentemente dos índices.

É possível associar de alguma forma o índice à restrição FK para pg_constraint.conindidapontar de FK para o índice?

Algo como:

ALTER TABLE my ALTER CONSTRAINT my_your_fk USING INDEX my_your_fk_i;

ATUALIZAÇÃO Vejo algo relacionado nos documentos do Postgres:

ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

Eu vejo nos documentos do Postgres :

Duas transações não podem manter bloqueios de modos conflitantes na mesma tabela ao mesmo tempo

Durante a manutenção (migração de esquema) vimos casos em que não conseguimos ALTER TABLEpor causa de travamento SELECTna transação ( pg_locks+ pg_stat_activity).

Para prosseguir com a migração, revogamos CONNECTos usuários "desonestos" e chutamos todas as suas sessões.

É correto que:

BEGIN;
SELECT ...;

vai bloquear ALTER TABLEaté COMMITou ROLLBACK?

Nós temos muito:

select count(*) from TBL where XDATE between ? and ? and FLD = ?;

Esperamos aumentar o desempenho ajustando o índice na coluna XDATE (que tem o tipo DATE ).

Que tipo de índices mais adequado para o tipo DATE e entre a condição?

Eu paro de usar o SQL Developer porque achei mais conveniente usar o SQL Plus dentro do modo Emacs SQLi.

O SQL Developer em seleções com grande quantidade de linhas mostra apenas algumas linhas. Mas o SQL Plus tende a despejar todas as linhas que correspondem às condições. Eu posso colocar ROWNUM <= N, mas muitas vezes eu esqueço de fazer isso.

Existe alguma opção para o SQL Plus limitar a saída da selectinstrução? Vou colocá-lo /opt/instantclient/glogin.sqlpara aplicar a cada sessão...

É possível definir o procedimento/função do pacote fora do bloco do corpo do pacote (para Oracle)?

A sintaxe simplificada para definir o corpo do pacote é:

create or replace package body <package_name> IS

  function <function_name>
   <function_body>

  procedure <procedure_name>
   <procedure_body>

end <package_name>;
/

É possível definir o procedimento dentro do pacote como:

procedure <package_name>.<procedure_name>
 <procedure_body>

Eu faço esta pergunta porque sou novato e não entendo por que preciso compilar todo o pacote quando preciso apenas aplicar as alterações de um único procedimento ...

RELACIONADO

• Quais são os benefícios dos pacotes em relação aos procedimentos e funções independentes?
• https://stackoverflow.com/questions/1812049/performance-standalone-procedure-vs-packaged-procedure-in-oracle

Tom diz :

• Sempre use um pacote.
• Nunca use um procedimento autônomo, exceto para demos, testes e utilitários autônomos (que não chamam nada e não são chamados por nada)

Durante o desenvolvimento do aplicativo, tenho muitas tabelas que armazenam "pequenas" quantidades de dados (geralmente 10-40 valores, id+ valuee às vezes type) que contêm atributos para "objetos", como fresco/podre, vermelho/verde/azul para produtos.

Não coloco esses atributos na tabela de produtos porque os componentes eletrônicos não podem ser frescos, o gás oxigênio não pode ser vermelho e as tabelas não podem ter contagem ilimitada de linhas...

Para armazenar atributos, eu uso pequenas tabelas personalizadas onde 2-3 campos: idpara vincular, namepara mostrar no aplicativo e, às vezes, typese grupos de atributos na mesma categoria.

Os "objetos" primários são vinculados a atributos por meio de tabelas muitos-para-muitos intermediárias.

Existe alguma razão para criar e manter índices para esses "pequenos dicionários" com menos de 1000 itens (geralmente 10-40)?

Meu banco de dados de destino é Oracle, mas espero que responda independente do fornecedor ...

Preencho - não, mas não tenho competências técnicas que justifiquem o meu preenchimento...