Jack Douglas's questions

Estou procurando instalar várias versões do SQL Server lado a lado para executar simultaneamente. Eu gostaria que eles ouvissem em endereços IP diferentes.

Eles serão executados no Windows Server 2019 Core.

Atualmente instalo usando algo assim:

Setup.exe /qs /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT AUTHORITY\Network Service" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /SAPWD="MYSECRETPASSWORD" /SQLSVCINSTANTFILEINIT="True" /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS /SECURITYMODE=SQL

Estou certo de que não há opções no Setup.exe que especifiquem o endereço IP para ouvir? Se não, qual é a maneira mais simples de configurar isso na linha de comando após a execução do Setup.exe?

As encarnações são explicadas em uma resposta a outra pergunta neste site. A resposta menciona encarnações 'órfãs':

…Existem outros fatores que resultam em encarnações ORPHANED e backups OBSOLETE…

Vejo nos documentos do Oracle que V$DATABASE_INCARNATIONinclui uma STATUScoluna que pode ter valores de ORPHAN, CURRENTou PARENT, que devem estar relacionados.

O que são encarnações 'órfãs' e quais etapas resultariam em uma linha com STATUS= ORPHANin V$DATABASE_INCARNATION?

Isso parece um comportamento estranho, e recebo o mesmo na linha de comando do MySQL como via db<>fiddle:

create table t(foo varchar(255));

insert into t (foo) values
('the quick brown fox'),
('jumped over the lazy dog');

select * from t;

| foo |
| :----------------------- |
| a rápida raposa marrom |
| saltou sobre o cão preguiçoso |

select regexp_replace(foo, '[^a-z]+', '-') from t;

| regexp_replace(foo, '[^az]+', '-') |
| :------------------------------------------ |
| a-raposa-marrom-rápida |
| a-raposa-marrom-rápida-pulou-sobre-o-cachorro-preguiçoso |

db<>fique aqui

Eu esperaria ver:

select REGEXP_REPLACE(foo, '[^a-z]+', '-') from t;

| REGEXP_REPLACE(foo, '[^az]+', '-') |
| :---------------------------------- |
| a-raposa-marrom-rápida |
| saltou-sobre-o-cachorro-preguiçoso |

Estou fazendo algo errado ou isso é um bug do MySQL?

Após a recuperação incompleta e a abertura de um banco de dados 9i resetlogs, executamos um backup completo que foi concluído com sucesso. O backup inclui um comando para excluir backups obsoletos após a conclusão:

delete noprompt obsolete device type sbt;

O RMAN está configurado para usar REDUNDÂNCIA 2:

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

No entanto, todos os backups da versão antiga foram imediatamente marcados como obsoletos e excluídos sem levar em consideração a redundância.

1. Esse comportamento teria sido diferente se tivéssemos um RECOVERY WINDOWconfigurado em vez de REDUNDANCY 2?
2. Esse comportamento é o mesmo em versões posteriores do Oracle?

edit: saída adicionada de LIST INCARNATION:

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            CUR Reset SCN      Reset Time
------- ------- -------- ---------------- --- -------------- ----------
1       1       LIVE     3494832994       NO  1              19-JAN-04
2       2       LIVE     3494832994       NO  11966702870498 01-JAN-14
3       3       LIVE     3494832994       YES 12041003378277 04-JUL-18

Os documentos sobre a função word_similarity dizem:

Retorna um número que indica a semelhança da primeira string com a palavra mais semelhante da segunda string. A função procura na segunda string uma palavra mais semelhante e não uma substring mais semelhante. O intervalo do resultado é zero (indicando que as duas strings são completamente diferentes) a um (indicando que a primeira string é idêntica a uma das palavras da segunda string).

Mas eles são um pouco vagos sobre qual deve ser o resultado se a 'primeira string' contiver mais de uma palavra. Qual é a explicação para os seguintes resultados:

select word_similarity('foo bar','foo bar baz');

| palavra_semelhança |
| :-------------- |
| 1 |

select word_similarity('baz bar','foo bar baz');

| palavra_semelhança |
| :-------------- |
| 1 |

select word_similarity('baz foo','foo bar baz');

| palavra_semelhança |
| :-------------- |
| 0,8 |

dbfiddle aqui

Uma falha noturna no disco rígido teve vários efeitos colaterais, incluindo deixar um único arquivo de dados precisando de recuperação:

SQL> select count(*) from v$datafile where status='RECOVER';

  COUNT(*)
----------
         1

Tudo voltou ao normal, mas um mistério permanece: antes da recuperação, o aplicativo cliente registrou um ORA-00376 em uma tabela de registro quando um trabalho em lote tentou acessar um segmento no arquivo de dados. Porém esse erro não apareceu no log de alerta, como isso pode acontecer?

No momento do erro ORA-00376, o seguinte apareceu no log de alerta:

Errors in file /app/oracle/admin/live33/bdump/live33_j002_938224.trc:
ORA-12012: error on auto execute of job 24760001
ORA-20001: Error during batch run
ORA-06512: at "*******.***_*****", line 266
ORA-06512: at "*******.***_***_***_*****", line 355
ORA-06512: at line 1

E na época da edição original:

KCF: write/open error block=0x2fb23 online=1
     file=177 /data/ora9i/live/db3b/nsbme.dbf
     error=27072 txt: 'IBM AIX RISC System/6000 Error: 5: I/O error
Additional information: 195363'
Automatic datafile offline due to write error on
file 177: /data/ora9i/live/db3b/nsbme.dbf

Minha função new_customeré chamada várias vezes por segundo (mas apenas uma vez por sessão) por um aplicativo da web. A primeira coisa que ele faz é bloquear a customertabela (para fazer uma 'inserção se não existir' - uma variante simples de um upsert).

Meu entendimento dos documentos é que outras chamadas new_customerdevem simplesmente ficar na fila até que todas as chamadas anteriores sejam concluídas:

LOCK TABLE obtém um bloqueio em nível de tabela, aguardando, se necessário, a liberação de quaisquer bloqueios conflitantes.

Por que às vezes é um impasse em vez disso?

definição:

create function new_customer(secret bytea) returns integer language sql 
                security definer set search_path = postgres,pg_temp as $$
  lock customer in exclusive mode;
  --
  with w as ( insert into customer(customer_secret,customer_read_secret)
              select secret,decode(md5(encode(secret, 'hex')),'hex') 
              where not exists(select * from customer where customer_secret=secret)
              returning customer_id )
  insert into collection(customer_id) select customer_id from w;
  --
  select customer_id from customer where customer_secret=secret;
$$;

erro do registro:

2015-07-28 08:02:58 BST DETALHE: Processo 12380 aguarda ExclusiveLock na relação 16438 da base de dados 12141; bloqueado pelo processo 12379.
        Processo 12379 aguarda ExclusiveLock na relação 16438 do banco de dados 12141; bloqueado pelo processo 12380.
        Processo 12380: selecione new_customer(decode($1::text, 'hex'))
        Processo 12379: selecione new_customer(decode($1::text, 'hex'))
2015-07-28 08:02:58 BST DICA: Consulte o log do servidor para obter detalhes da consulta.
2015-07-28 08:02:58 BST CONTEXT: Função SQL "new_customer" instrução 1
28/07/2015 08:02:58 DECLARAÇÃO BST: selecione new_customer(decode($1::text, 'hex'))

relação:

postgres=# select relname from pg_class where oid=16438;
┌──────────┐
│ relname  │
├──────────┤
│ customer │
└──────────┘

editar:

Consegui obter um caso de teste reproduzível simples. Para mim, isso parece um bug devido a algum tipo de condição de corrida.

esquema:

create table test( id serial primary key, val text );

create function f_test(v text) returns integer language sql security definer set search_path = postgres,pg_temp as $$
  lock test in exclusive mode;
  insert into test(val) select v where not exists(select * from test where val=v);
  select id from test where val=v;
$$;

script bash executado simultaneamente em duas sessões bash:

for i in {1..1000}; do psql postgres postgres -c "select f_test('blah')"; done

log de erros (geralmente um punhado de impasses nas 1000 chamadas):

2015-07-28 16:46:19 BST ERROR:  deadlock detected
2015-07-28 16:46:19 BST DETAIL:  Process 9394 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9393.
        Process 9393 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9394.
        Process 9394: select f_test('blah')
        Process 9393: select f_test('blah')
2015-07-28 16:46:19 BST HINT:  See server log for query details.
2015-07-28 16:46:19 BST CONTEXT:  SQL function "f_test" statement 1
2015-07-28 16:46:19 BST STATEMENT:  select f_test('blah')

edição 2:

@ypercube sugeriu uma variante com o lado de lock tablefora da função:

for i in {1..1000}; do psql postgres postgres -c "begin; lock test in exclusive mode; select f_test('blah'); end"; done

curiosamente, isso elimina os impasses.

Posso inserir várias linhas em uma tabela com valores padrão para todas as colunas da maneira RBAR :

create table course(course_id serial primary key);

do $$
begin
  for i in 1..100000 loop
    insert into course default values;
  end loop;
end;$$;

Existe uma maneira de fazer o mesmo com uma única instrução SQL?

Desde 9.2, é possível usar o security_barrieratributo para proteger contra funções escolhidas maliciosamente e operadores que acessam dados 'ocultos' por trás de filtros em visualizações (informações completas na documentação do postgres ).

Você pode ver isso acontecendo no teste abaixo, mas o mesmo efeito não é observado com a função set-return em vez de uma visualização no final do teste.

Isso é apenas uma peculiaridade deste teste individual ou as funções de retorno são sempre uma maneira segura ^de se proteger contra esse tipo de vazamento?

banco de ensaio:

create schema stack;
set search_path=stack;
create table t(secret) as values (1), (2);

teste1:

create view v as select * from t where secret^4>1;
select * from v;
/*
┌────────┐
│ secret │
├────────┤
│      2 │
└────────┘
*/
create function f(integer) returns integer cost 1 language plpgsql as $$
begin
  raise notice 'secret is: %',$1;
  return $1;
end;
$$;
select * from v where f(secret)>0;
/*
NOTICE:  secret is: 1                <-------- SECURITY LEAK
NOTICE:  secret is: 2
┌────────┐
│ secret │
├────────┤
│      2 │
└────────┘
*/

teste2:

create function fv() returns setof t language sql as $$
  select * from t where secret^4>1
$$;
select * from fv() where f(secret)>0;
/*
NOTICE:  secret is: 2                <-------- no leak
┌────────┐
│ secret │
├────────┤
│      2 │
└────────┘
*/

limpar:

drop schema stack cascade;

_{¹ Existem razões de desempenho pelas quais você pode não querer seguir este caminho, mesmo que seja seguro}

O CLUSTERcomando em uma tabela grande pode levar muito tempo e bloqueia leituras e gravações na tabela durante sua execução.

Não preciso que os dados em minha tabela sejam estritamente classificados em ordem de índice, só quero que as linhas que são comumente consultadas juntas tenham mais probabilidade de estar nos mesmos blocos de banco de dados do que espalhadas uniformemente pela tabela (que é a distribuição que eles naturalmente devido à natureza da forma como a data é inserida na tabela).

Isso pode fazer uma grande diferença. No exemplo abaixo, a única diferença é que o insertpossui um adicional order by mod(g,10)para que os dados de teste sejam pré-agrupados por host_id. Muito menos blocos precisam ser lidos ao obter todos os dados para um arquivo host_id.

Existe alguma maneira de obter esse tipo de agrupamento sem o bloqueio exclusivo e a sobrecarga de registro do clustercomando?

create schema stack;
set search_path=stack;
--
create table foo(host_id integer, bar text default repeat('a',400));
insert into foo(host_id) select mod(g,10) from generate_series(1,500000) g;
create index nu_foo on foo(host_id);
explain analyze select count(bar) from foo where host_id=1;
/*
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=30188.66..30188.67 rows=1 width=404) (actual time=1129.858..1129.859 rows=1 loops=1)
   ->  Bitmap Heap Scan on foo  (cost=919.27..30066.46 rows=48883 width=404) (actual time=253.149..1110.013 rows=50000 loops=1)
         Recheck Cond: (host_id = 1)
         Rows Removed by Index Recheck: 320257
         ->  Bitmap Index Scan on nu_foo  (cost=0.00..907.04 rows=48883 width=0) (actual time=251.863..251.863 rows=50000 loops=1)
               Index Cond: (host_id = 1)
 Total runtime: 1129.893 ms
*/
--
drop table foo;
--
create table foo(host_id integer, bar text default repeat('a',400));
insert into foo(host_id) select mod(g,10) from generate_series(1,500000) g order by mod(g,10);
create index nu_foo on foo(host_id);
explain analyze select count(bar) from foo where host_id=1;
/*
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=7550.20..7550.21 rows=1 width=32) (actual time=24.397..24.397 rows=1 loops=1)
   ->  Bitmap Heap Scan on foo  (cost=47.80..7543.95 rows=2500 width=32) (actual time=3.988..16.189 rows=50000 loops=1)
         Recheck Cond: (host_id = 1)
         ->  Bitmap Index Scan on nu_foo  (cost=0.00..47.17 rows=2500 width=0) (actual time=3.649..3.649 rows=50000 loops=1)
               Index Cond: (host_id = 1)
 Total runtime: 24.437 ms
*/
--
drop schema stack cascade;

Dos documentos :

No PostgreSQL, um UPDATE ou DELETE de uma linha não remove imediatamente a versão antiga da linha [...] Mas, eventualmente, uma versão desatualizada ou excluída da linha não é mais interessante para nenhuma transação. O espaço que ocupa deve então ser recuperado para reutilização por novas linhas, para evitar o crescimento ilimitado dos requisitos de espaço em disco. Isso é feito executando VACUUM .

Quando o vácuo é executado, ele libera eficientemente o espaço ocupado pelas linhas excluídas, reescrevendo todo o bloco, ou a fragmentação do bloco ocorre com novas linhas se encaixando nos 'buracos' disponíveis apenas se forem menores do que a linha excluída que abriu caminho para elas ?

Cada linha em uma tabela tem uma coluna ctid de sistema do tipo tidque representa a localização física da linha:

create table t(id serial);
insert into t default values;
insert into t default values;

select ctid
     , id
from t;

ctid | Eu iria
:---- | -:
(0,1) | 1
(0,2) | 2

dbfiddle aqui

Qual é a melhor maneira de obter apenas o número da página ctidno tipo mais apropriado (por exemplo integer, bigintou numeric(1000,0))?

A única maneira que consigo pensar é muito feia.

As funções Query Language (SQL) e PL/pgSQL tratam as tabelas temporárias de maneira diferente:

begin;
create table foo(id) as values (1);
select * from foo;
/*
 id
----
  1
*/
savepoint s;
create function f() returns setof integer language sql as $$
  create temporary table foo(id) on commit drop as values (2);
  select id from foo;
$$;
select * from f();
/*
 f
---
 1
*/
rollback to s;
create function f() returns setof integer language plpgsql as $$
begin
  create temporary table foo(id) on commit drop as values (2);
  return query select id from foo;
end;
$$;
select * from f();
/*
 f
---
 2
*/
rollback;

Os docs dizem :

...Tabelas permanentes existentes com o mesmo nome não são visíveis para a sessão atual enquanto a tabela temporária existir, a menos que sejam referenciadas com nomes qualificados pelo esquema...

Isso não menciona a exceção demonstrada acima. Isso está documentado em outro lugar?

É uma maneira postgres de combinar IS DISTINCT FROMou ANYalguma outra maneira legal de obter o mesmo resultado?

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo <> any(array[null, 'A']);

 count
-------
     1
(1 row)

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo is distinct from any(array[null, 'A']);  

ERROR:  syntax error at or near "any"
LINE 3: where foo is distinct from any(array[null, 'A']);
                                   ^

Eu executei este comando:

cluster verbose address using address_locality_lookup;

E obteve este resultado:

INFO:  clustering "dba.address" using sequential scan and sort
INFO:  "address": found 0 removable, 29323623 nonremovable row versions in 1139047 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 26.00s/8286.70u sec elapsed 8342.01 sec.
CLUSTER
Time: 9366585.421 ms

Isso indica que o clustering foi bem-sucedido (ou seja, todas as linhas agora estão na ordem indicada pelo address_locality_lookupíndice)?

O que 'removível' e 'não removível' indicam?

Aqui está um exemplo mínimo do meu problema do mundo real:

create table t(id serial primary key, rnd double precision);

claro que você pode retornar as colunas inseridas com uma returningcláusula:

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *;
/*
| ID |            RND |
|----|----------------|
|  9 | 0.203221440315 |
*/

você também pode retornar um literal:

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, 1.0 dummy;
/*
| ID |            RND | DUMMY |
|----|----------------|-------|
| 11 | 0.594980469905 |     1 |
*/

mas você não pode retornar as colunas de origem:

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, w.rnd;
/*
ERROR: missing FROM-clause entry for table "w": with w as (insert into t(rnd) values(random()) returning *) insert into t(rnd) select random() from w returning *, w.rnd
*/

Existe alguma maneira de sair da cláusula w.rndfinal ?returning

db<>mexa aqui

Com meu entendimento limitado da cláusula de modelo, esperava que as três consultas a seguir retornassem os mesmos resultados porque não há nulos nos dados e as funções são equivalentes:

select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/sum(v)[any] );  --plain sum
/*
         K          V        SHR
---------- ---------- ----------
         1        100        0.5 
         2        100        0.5 
*/
select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/nullif(sum(v)[any],0) ); --with nullif
/*
         K          V        SHR
---------- ---------- ----------
         1        100       0.25   <------\___ why?
         2        100       0.25   <------/
*/
select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/decode(sum(v)[any],0,null,sum(v)[any]) );  -- with decode
/*
         K          V        SHR
---------- ---------- ----------
         1        100        0.5 
         2        100        0.5 
*/

O que estou perdendo sobre o processamento de regras que explica esse comportamento?

_{SQLFiddle aqui}

para os dados a seguir, gostaria de poder deletar algumas linhas e inserir outras dando o resultado abaixo. Isso é possível com uma única instrução (por exemplo, com a mergeinstrução)?

create table product(product_id integer primary key);
insert into product(product_id) values(1);
insert into product(product_id) values(2);
insert into product(product_id) values(3);

create table split( parent_id integer not null references product, 
                    child_id integer not null references product,
                    primary key(parent_id, child_id) );

insert into split(parent_id, child_id) values(1,2);
insert into split(parent_id, child_id) values(1,3);

create table sale(sale_at date, product_id integer references product);

insert into sale(sale_at, product_id) values(sysdate, 1);
insert into sale(sale_at, product_id) values(sysdate, 1);
insert into sale(sale_at, product_id) values(sysdate, 1);
insert into sale(sale_at, product_id) values(sysdate, 2);
insert into sale(sale_at, product_id) values(sysdate, 2);

select sale_at, product_id from sale where product_id not in (select parent_id from split)
union all
select sale_at, child_id from sale join split on(parent_id=product_id);

/*
|                     SALE_AT | PRODUCT_ID |
--------------------------------------------
| June, 10 2013 15:18:22+0000 |          2 |
| June, 10 2013 15:18:22+0000 |          2 |
| June, 10 2013 15:18:22+0000 |          3 |
| June, 10 2013 15:18:22+0000 |          2 |
| June, 10 2013 15:18:22+0000 |          3 |
| June, 10 2013 15:18:22+0000 |          2 |
| June, 10 2013 15:18:22+0000 |          3 |
| June, 10 2013 15:18:22+0000 |          2 |
*/

_{( SQLFiddle )}

----editar:

para esclarecer, o efeito que estou tentando obter com uma única mergedeclaração é este:

insert into sale(sale_at, product_id)
select sale_at, child_id from sale join split on parent_id=product_id;

delete from sale where product_id in (select parent_id from split);

e o motivo pelo qual gostaria de uma solução de instrução única é evitar uma condição de corrida em que outra transação insere/confirma dados entre inserte deletena transação principal.

Eu uso a xmincoluna do sistema para implementar uma forma de bloqueio otimista e, às vezes, preciso 'tocar' a linha para aumentar xminsem realmente atualizar a linha. Atualmente, apenas faço uma atualização 'fictícia':

create table t(id integer);
insert into t(id) values(1);
insert into t(id) values(2);

select xmin::text from t where id=1
/*    
|  XMIN |
---------
| 87159 |
*/

update t set id=id where id=1

select xmin::text from t where id=1
/*
|  XMIN |
---------
| 87196 |
*/

_{(SQL Fiddle)}

Estou curioso para saber se existe outra maneira de xidatualizar sem fazer uma atualização, análoga ao touchcomando unix?

Me peguei escrevendo o seguinte:

select 'yes' 
where exists(select * from foo where val=1)
and not exists(select * from foo where val<>1);

e imaginando se existe uma maneira mais concisa sem sacrificar muita legibilidade.

Eu encontrei uma maneira que estou postando como resposta, mas não estou totalmente feliz com isso e estaria muito interessado em alternativas

Neste caso, valé único dentro foo- não há duplicatas