Eu tinha ouvido falar que no postgresql, o jsontipo preserva a ordenação declarada ao contrário de sua contraparte totalmente analisada jsonb.

Eu quero obter a posição ordinal de cada chave em um documento fornecido pelo cliente. Pelo que eu tentei , porém, isso não parece possível.

select *
from jsonb_path_query(
    '[{"foo":1},{"foo":2,"bar":3}]'::jsonb,
    '$.*'::jsonpath
) with ordinality

A consulta anterior me mostra o valor do nó folha 3 na posição ordinal 2, apesar de ser fornecido como a terceira chave, mas não parece haver um json_path_querycompanheiro para jsonb_path_query.

Existe uma função interna para analisar jsonchaves? Ou estou limitado a uma análise mais rigorosa jsonbou completamente irrestrita textpara extrair essas informações?

A página de documentos afirma (ênfase minha)

Status de saída

O psql retorna 0 ao shell se terminou normalmente, 1 se ocorrer um erro fatal próprio (por exemplo, falta de memória, arquivo não encontrado), 2 se a conexão com o servidor foi ruim e a sessão não foi interativa e 3 se ocorreu um erro em um script e a variável ON_ERROR_STOP foi definida.

No entanto, parece que não consigo induzir o código de saída 3.

bash-4.2$ uname -a
Linux mvpg-centos-76.vagrantup.com 3.10.0-957.21.3.el7.x86_64 #1 SMP Tue Jun 18 16:35:19 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
bash-4.2$ psql --version
psql (PostgreSQL) 10.12
bash-4.2$ psql -v ON_ERROR_STOP=1 -c 'select 1/0;'
ERROR:  division by zero
bash-4.2$ echo $?
1
bash-4.2$ 

Eu também reproduzi esse comportamento no FreeBSD 11.3 e Darwin 19.3. Estou entendendo mal a documentação ou usando uma sintaxe defeituosa?

Eu tenho dois não public-esquemas em um banco de dados. Uma tabela chamada "foo"existe em ambos os esquemas e posso executar todos os comandos DDL e DML necessários em ambos. No entanto, quando executo \d, não consigo ver os dois. O que da?

Reprodução

create schema a;
create schema b;

create table a.foo (i int);
create table b.foo (i int);

set search_path = "$user", public, a, b;

Dentropsql

postgres=# \d
            List of relations
 Schema |   Name    | Type  |   Owner
--------+-----------+-------+------------
 a      | foo       | table | pvandivier
(1 row)

Eu \set ECHO_HIDDEN onpara extrair a consulta subjacente executada \de movi as linhas relevantes da WHEREcláusula SELECTpara examinar as diferenças entre os dois objetos de tabela.

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  pg_catalog.pg_table_is_visible(c.oid),
  n.nspname,
  c.relkind
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname IN ('a','b')
ORDER BY 1,2;

A execução desta consulta revela...

 Schema | Name | Type  |   Owner    | pg_table_is_visible | nspname | relkind
--------+------+-------+------------+---------------------+---------+---------
 a      | foo  | table | pvandivier | t                   | a       | r
 b      | foo  | table | pvandivier | f                   | b       | r

A diferença relevante parece ser a saída de pg_table_is_visible(). Mas por que deveria retornar " False" ao perguntar se b.fooestá visível?

Por que está pg_table_is_visible()retornando Falsepara uma mesa a que tenho acesso?

Hoje eu queria definir um uuid de valor 00000000-0000-0000-0000-000000000000. Sendo um homem do SQL Server, eu normalmente ...

select cast(0x0 as uniqueidentifier);

...mas estou no mundo postgres agora, então tirei um sensato ...

select cast('\x00'::bytea as uuid);

ERROR:  cannot cast type bytea to uuid
LINE 1: select cast('\x00'::bytea as uuid);
               ^

Droga! Então eu vou para os documentos postgres na conversão de tipo esperando ver um documento como este para revisar quais tipos de dados eu posso converter por padrão sem precisar criar uma conversão explícita .

Se esse gráfico existir nos documentos, ele está bem oculto. O Google também não é super útil a esse respeito.

Existe uma boa referência de documentação para conversões de tipo permissíveis padrão no postgresql?

^{Para ser claro, eu realmente não me importo com o uuid}

Estou migrando um esquema do NoSQL para o postgres. O objeto de hoje " b" existe no NoSQL como um elemento de objeto ae parece na fonte assim ...

{
  "a": {
    "id": 1,
    "b":{
      "c1": {
        "d1": 1,
        "d2": 2
      },
      "c2": {
        "d1": 1,
        "d2": 2
      },
      "c3": {
        "d1": 3,
        "d2": 4
      }
    }
  }
}

... que atribuí provisoriamente a um esquema semelhante ao ERD abaixo

+---+    +-----+    +---+    +---+
| b |<--<| b_d |>-->| d |>-->| c |
+---+    +-----+    +---+    +---+

db_fiddle completo

Eu gostaria de poder recompor os dados normalizados no objeto original, mas não consegui fazê-lo. Em vez de obter " c" objetos como pares de valores-chave diretos, estou obtendo objetos inteiros. Por exemplo ...

select 
    b_d.b_id, 
    json_agg(json_build_object(c.name,json_build_object('d1',d.d1,'d2',d.d2))) as b
from b_d
join d on d.id = b_d.d_id
join c on c.id = d.c_id
group by b_d.b_id;

... retorna ...

[{
    "c1 ": {
        "d1": 1,
        "d2": 2
    }
},
{
    "c2 ": {
        "d1": 1,
        "d2": 2
    }
},
{
    "c3 ": {
        "d1": 3,
        "d2": 4
    }
}]

...que você notará que tem um nível de aninhamento adicional em relação ao objeto de origem. Suspeito que tenha errado de cara, json_build_objectmas até agora não consegui reconstruir com sucesso o bobjeto " " como aparece no documento json original.

O que modifico na consulta e/ou no esquema subjacente para normalizar e desnormalizar esse objeto?

Estou projetando um processo para testar meus backups postgresql 10.8 restaurando-os em um ponto aleatório no tempo em uma máquina virtual descartável. No entanto, não consegui automatizar totalmente o processo. Estou bloqueado na etapa 8 (seção 25.3.4) da documentação oficial

8. Inicie o servidor.

Ao executar o pg_ctl startover ssh, o comando trava até ser morto. Se eu ssh diretamente na VM e executar pg_ctl start, o comando retornará rapidamente conforme o esperado.

Este tópico de 2012 parece descrever um cenário semelhante. No meu caso, no entanto , o processo postgres inicia com sucesso, mesmo se a sessão de chamada for encerrada enquanto estiver suspensa (possivelmente uma melhoria entre 9.0.5 e 10.8?).

Este problema do github parece relevante, mas infelizmente é "resolvido" por uma longa reescrita em um idioma que não conheço e conclui finalmente que é um bug no pg_ctlbinário.

A Grande Questão

Como posso automatizar a etapa 8 para continuar com os testes de validação subsequentes da minha mídia de backup?

Este é um bug pendente no binário que eu preciso hackear? Ou eu perdi uma implementação sensata?

Estou experimentando com a origem controlando meus trabalhos e agendas do SQL Agent como .jsonarquivos e implantando-os com o conjunto de comandos dbatools SQL Agent .

Dado $sa = Get-Credentiale foo.configda forma abaixo...

{
    "Schedule":                  "Foo",
    "Disabled":                  false,
    "FrequencyType":             "Weekly",
    "FrequencyInterval":         "EveryDay",
    "FrequencySubdayType":       "Time",
    "FrequencySubdayInterval":   0,
    "FrequencyRelativeInterval": "Unused",
    "FrequencyRecurrenceFactor": 1,
    "StartDate":                 "20180823",
    "EndDate":                   "20181023",
    "StartTime":                 "070000",
    "EndTime":                   "235959"
}

A tentativa de usar foo.configem uma solução falha na análise básica com a seguinte mensagem :

~> $foo = Get-Content foo.config | ConvertFrom-Json
~> New-DbaAgentSchedule @foo -ServerInstance "." -SqlCredential $sa

AVISO: [15:50:04][New-DbaAgentSchedule] Um agendamento não foi fornecido! Forneça um nome de programação.

Bem, isso é uma merda... Especialmente porque o seguinte funciona bem...

$bar = @{
    Schedule=                  "Foo"
    Disabled=                  $false
    FrequencyType=             "Weekly"
    FrequencyInterval=         "EveryDay"
    FrequencySubdayType=       "Time"
    FrequencySubdayInterval=   0
    FrequencyRelativeInterval= "Unused"
    FrequencyRecurrenceFactor= 1
    StartDate=                 "20180823"
    EndDate=                   "20181023"
    StartTime=                 "070000"
    EndTime=                   "235959"
}

New-DbaAgentSchedule @bar -ServerInstance "." -SqlCredential $sa

Eu realmente não quero me incomodar digitando cada indivíduo ... -Param1 $foo.Param1 -Param2 $foo.Param2 ... porque sou preguiçoso . Eu realmente prefiro espalhar meus arquivos de configuração nos vários comandos. Por que isso não funciona!!?!1!

Ao perguntar quais bancos de dados são graváveis ​​em um determinado servidor, estou usando o snippet abaixo para listar bancos de PRIMARYdados de réplica e bancos de dados que não participam de um AG.

Agora que implantei um Distributed Availability Group , ele não está funcionando mais! Como o encaminhador local relata como uma PRIMARYréplica no sys.dm_hadr_availability_replica_statesDMV, minha consulta antiga está me dizendo que um banco de dados é gravável quando não é.

Como altero esta consulta para excluir bancos de dados em uma réplica de encaminhador local ?

select 
    ars.role_desc
   ,ag_name = ag.[name]
   ,adc.[database_name]
from sys.availability_groups ag 
join sys.dm_hadr_availability_replica_states ars on ars.group_id = ag.group_id
join sys.availability_databases_cluster adc on adc.group_id = ag.group_id
where ars.is_local = 1
    and ars.role_desc = 'PRIMARY'
union all 
select 
    'LOCAL_ONLY'
   ,@@servername
   ,[name]
from sys.databases d 
where not exists (
    select 1 
    from sys.availability_databases_cluster adc
    where adc.[database_name] = d.[name]
);

...ou mais simplesmente...

Como listar todos os bancos de dados de encaminhadores locais?

Eu sou preguiçoso e prefiro parametrizar uma vez (para obter um object_id para um nome) e usar Invoke-SqlCmdpara o resto do trabalho.

Dada uma série de funções destinadas a trabalhar com um objeto de servidor arbitrário, o teste abaixo é suficiente para confirmar que CmdletBinding um parâmetro de entrada Int32impede a injeção por meio do $object_id?

function foo {
    [CmdletBinding()]Param(
        [Int32]$object_id 
    )
    $query = "select type_desc from sys.objects where object_id = $object_id;"
    Invoke-Sqlcmd -ServerInstance "localhost" -Database "tempdb" -Query $query
}

foo 3
foo "0 union all select name from sys.syslogins where sid = 0x01"
foo $null
foo ([math]::Pow(2,31)+1)
foo @(1,2)

Parece definir um grupo de arquivos para read_onlyimpedir dbcc checkdbtodo o banco de dados se o grupo de arquivos contiver um índice columnstore. Ao tentar executar checkdbou checkfilegroup( para qualquer grupo de arquivos no banco de dados, incluindo secundários de leitura/gravação e[PRIMARY] ), o erro abaixo é retornado...

Msg 8921, Level 16, State 1, Line 24
Check terminated. A failure was detected while collecting facts. 
Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

Existe um método com suporte para ter dados columnstore em um grupo de arquivos somente leitura? Ou estou impedido de realizar verificações de integridade neste cenário?

Reprodução

create database check_fg_ro
go
use check_fg_ro
go
exec sp_changedbowner 'sa';
go
alter database check_fg_ro add filegroup check_fg_ro_2;
alter database check_fg_ro
    add file (
         name='check_fg_ro_2'
        ,filename='C:\check_fg_ro_2.ndf'
    ) to filegroup check_fg_ro_2;
go
create table foo ( 
    i int not null primary key
) on check_fg_ro_2;
go
create columnstore index ccix_foo on foo(i);
go
use master
go
alter database check_fg_ro modify filegroup check_fg_ro_2 read_only;
go
dbcc checkdb( check_fg_ro ) with no_infomsgs, all_errormsgs, extended_logical_checks;
/*
Msg 8921, Level 16, State 1, Line 24
Check terminated. A failure was detected while collecting facts. 
Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
*/
go

_{Isenção de responsabilidade: postado em fóruns da technet}

Eu gostaria de habilitar um aplicativo que despeja um grande e velho heap de dados em uma área de teste para poder iniciar o procedimento armazenado que transforma e carrega os dados na área de produção. Se possível, gostaria de não conceder ao aplicativo nenhum acesso à área de produção. Infelizmente, EXECUTE ASparece estar falhando comigo. Para referência, os bancos de dados em questão estão sendo executados no 2008 R2, mas estou recriando o comportamento no meu 2016localhost. Além disso, a conta de serviço em questão é uma credencial de domínio, mas recriei o comportamento com uma credencial de autenticação SQL para portabilidade.

A configuração

use [master]
create database Prod;
create database Stage;
create login ServiceAccount with password='Password1234';
go
use Prod
create table tbl1 ( i int );
insert tbl1 select 1;
go
use Stage
create user ServiceAccount for login ServiceAccount; 
go
create proc spLoadStageToProd as
begin
    insert Prod.dbo.tbl1(i)
    select checksum(newid())%100; -- random number
end;
go
grant execute on spLoadStageToProd to ServiceAccount;
go

O Teste

A partir do Stagebanco de dados, é bem- exec spLoadStageToProd;sucedido. Previsivelmente, exec as login = 'ServiceAccount'; exec spLoadStageToProd;falha com o erro ...

Msg 229, Level 14, State 5, Procedure spLoadStageToProd, Line 1 [Batch Start Line 21]
The EXECUTE permission was denied on the object 'spLoadStageToProd', database 'Stage', schema 'dbo'.

Ok, então vou tentar execute as ownerpara que o proc tenha as permissões necessárias para fazer o trabalho, mas o ( desenvolvedor que tem a senha para ) ServiceAccountnão pode mexer na minha área de produção.

alter proc spLoadStageToProd
with execute as owner
as
begin
    insert Prod.dbo.tbl1(i)
    select checksum(newid())%100; -- random number
end;
go

Atirar! Bem, agora um simples teste do Staging of exec spLoadStageToProd;retorna o erro:

Msg 916, Level 14, State 1, Procedure spLoadStageToProd, Line 5 [Batch Start Line 35]
The server principal "DOMAIN\peter" is not able to access the database "Prod" under the current security context.

"DOMAIN\peter"é claro que eu: o doof com sysadminfunção de servidor ...

Bem, isso é estranho o suficiente... Eu tentei, alter authorization on object::spLoadStageToProd to dbo;mas o mesmo erro (Msg 916) aparece. E mais... exec as login = 'ServiceAccount'; exec spLoadStageToProd;agora volta...

Msg 229, Level 14, State 5, Procedure spLoadStageToProd, Line 1 [Batch Start Line 37]
The EXECUTE permission was denied on the object 'spLoadStageToProd', database 'Stage', schema 'dbo'.

Foda-se! alter authorization on object::spLoadStageToProd to sa;...

Msg 15151, Level 16, State 1, Line 41
Cannot find the user 'sa', because it does not exist or you do not have permission.

...

...

... triste .

Então... claramente estou entendendo mal algo fundamental. Eu tentei habilitar o encadeamento de propriedade cross-db apenas no caso de ser o postigo pegajoso, mas sem sorte. Depois de pesquisar infrutíferas e uma varredura rápida dessas duas perguntas dba.se, estou muito preso. Onde eu estou errando?

Como faço para que este SPoc seja executado em permissões elevadas entre bancos de dados, independentemente de quem o chama?

O projeto que precipitou essa questão é uma depreciação iterativa, então acho que a assinatura de certificados pode acabar sendo uma sobrecarga administrativa bastante grande com o passar do tempo.

Se for possível usar esse proc armazenado como um wrapper para executar ações restritas independentemente de quem o chama, esse é o objetivo final.

Estou confiante de que o SProc não pode ser modificado indevidamente, então qualquer coisa que seja executada de dentro dele pode ser altamente permitida - na esperança de que eu não precise administrar permissões para o ServiceAccountum por um.

Script de limpeza de cortesia

revert;
use [master]
go
drop database Prod,Stage;
drop login ServiceAccount;
go

Dada uma tabela da forma...

create table dbo.FilterIDXTest ( 
    id int not null identity primary key clustered,
    _bigint bigint,
    _varchar10 varchar(10),
    _varchar20 varchar(20),
    _guid uniqueidentifier
);

go

create unique index uq_FilterIDXTest
    on dbo.FilterIDXTest ( _varchar10, _varchar20 ) 
    include ( _guid )
    where _bigint is not null 
        and id > 5;

go

... em que (se houver) exibição de catálogo (ou outro objeto de referência bem formado) os nomes das colunas na WHEREcláusula podem ser localizados e rigorosamente identificados?

sys.indexespode ser visualizado para nos lembrar que o filter_definitioné ([_bigint] IS NOT NULL AND [id]>(5)).

sys.index_columnspode ser visualizado para nos informar as colunas ONe INCLUDE, mas lista apenas 3 das 5 colunas no CREATE INDEXcomando original. As filter_predicatecolunas não são encontradas aqui.

Devo assumir que as filter_predicatedependências são rigorosamente verificadas em algum estágio porque sp_rename 'dbo.FilterIDXTest._bigint', 'foo_bigint', 'column'falha com o erro ...

Caution: Changing any part of an object name could break scripts and stored procedures.
Msg 5074, Level 16, State 1, Procedure sp_rename, Line 611
The index 'uq_FilterIDXTest' is dependent on column '_bigint'.
Msg 4922, Level 16, State 9, Procedure sp_rename, Line 611
RENAME COLUMN _bigint failed because one or more objects access this column.

... e não consigo acreditar que a única razão pela qual isso falha é por causa de alguma correspondência de texto em sys.indexes.filter_definition.

_{Para referência, a tarefa em questão é extrair o código-fonte de vários servidores legados não documentados. Minha solicitação para usar o RedGate/algum outro aplicativo auxiliar pago para essa finalidade já foi rejeitada, pois " Você pode apenas criar o script dos objetos da interface do usuário, certo? ". Talvez eu tenha sido excessivamente rigoroso, mas gostaria de ter um nível muito alto de confiança de que o índice que estou verificando no controle de origem é o índice que realmente existe. Estou testando este script no sql server 2012 , mas os servidores de destino nos quais ele será executado variam de 2005 SP1 a 2008 R2 SP3 .}

Reunindo um procedimento rápido para ajudar na depuração, encontrei o que parece ser um erro no compilador.

create proc spFoo
    @param bit
as
begin
    if @param = 0
    begin 
        select * 
        into #bar
        from [master].dbo.spt_values
        -- where number between ...
    end
    else
    begin
        select top 10 * 
        into #bar
        from [master].dbo.spt_values
        order by newid();
    end;
end;

A tentativa acima retorna o seguinte erro

Msg 2714, Nível 16, Estado 1, Procedimento spFoo, Linha 19
Já existe um objeto denominado '#bar' no banco de dados.

Em um sentido legível por humanos, o proc parece estar bem: apenas uma select intoinstrução será executada, uma vez que está dentro dos if-elseblocos. Muito bem, porém, o servidor SQL não pode confirmar que as instruções são logicamente excluídas umas das outras. Talvez mais confuso seja que o erro permanece quando o drop table #fooé colocado dentro do bloco if-else (que se supõe que diria ao compilador para desalocar o nome do objeto) como abaixo.

create proc spFoo
    @param bit
as
begin
    select top 1 * 
    into #bar
    from [master].dbo.spt_values

    if @param = 0
    begin 
        drop table #bar;
        
        select * 
        into #bar
        from [master].dbo.spt_values
        -- where number between ...
    end
    else
    begin
        drop table #bar;
        
        select top 10 * 
        into #bar
        from [master].dbo.spt_values
        order by newid();
    end;
end;

O proc em si é bom. Eu absorvi e escrevi as instruções create table #foo( ... )e insert #foo ( ... ), eu estava tentando pular com a select * into sintaxe. Neste ponto, estou apenas tentando entender por que o compilador me cagou com a sintaxe do cara preguiçoso. A única coisa que consigo pensar é que o comando DDL reserva o nome do objeto IN TEMPDB .

Por que o texto em negrito?

create proc spIck
as
begin
    create table #ack ( col1 int );
    drop table #ack;
    create table #ack ( colA char( 1 ) );
    drop table #ack;
end;

Isso falha com o mesmo código de erro acima. Mas o seguinte...

create proc spIck
as
begin
    create table ack ( col1 int );
    drop table ack;
    create table ack ( colA char( 1 ) );
    drop table ack;
end;

... consegue. O mesmo segue acima para a tentativa de proc original. Então...

Minha pergunta é esta

Qual é a diferença (e por que está presente) na reserva de nome de objeto para TempDBobjetos em oposição aos bancos de dados do usuário. Nenhuma das referências de processamento de consulta lógica nem as referências de comando DDL que revisei parecem explicar isso.

Gostaria de navegar para abrir um caminho de diretório a partir de um caminho UNC comentado em linha. O SSMS integra-se ao Windows Explorer e o Explorer permite a navegação UNC a partir da barra de endereços.

De fato, quando no navegador integrado, é possível navegar entre os caminhos UNC. NO ENTANTO, a funcionalidade click-thru fornecida para http:/URLs precedidos no editor de texto do SSMS parece não existir para caminhos UNC. A Web Browserseção de Options/ Environmentparece abrir as opções do Internet Explorer e a seção Text Editor/ Plain Text/ Generaltem a opção "Ativar navegação de URL de clique único", mas não parece permitir a definição do que é detectado como uma URL.

Capturas de tela no link abaixo

(adicionado código de amostra abaixo)

use [master]
go

-- http://www.google.com
-- \\servername\unc\BackupFolder\

select 'This query needs to be uploaded to the URL' Header
union
select 'and also saved for archiving at the UNC path'
union
select 'so is it possible to clickthrough to both?'

Tenho uma solicitação de suporte de produção de um usuário de alto valor para ajustar as configurações em um painel de toda a empresa para que ele possa gerenciar uma seção de seu fluxo de trabalho que está sendo excluída desses relatórios. Dadas as duas opções imediatamente óbvias de

1. alterando as configurações do painel para TODOS os usuários em toda a empresa ou
2. replicando o painel apenas para HVU

...foi decidido que eu deveria criar uma instância "dev" onde HVU pudesse ver uma variante de seu painel. Para conseguir isso, eu portei a .rdlfamília para outra ReportServerinstância em uma árvore de diretório idêntica e clonei todos os procedimentos armazenados nos conjuntos de dados pertinentes de Schema1para Schema1_dev.

Pergunta: todos os procedimentos armazenados e o esquema de segurança existem no mesmo servidor e banco de dados, portanto, não há risco de usuários órfãos. É possível / qual é a melhor maneira de clonar todas as permissões de usuário atuais Schema1para Schema1_devoutras que não sejam verificadas por meio da GUI do SSMS?