主页 / user-68127

Peter Vandivier's questions

Martin Hope
Peter Vandivier
Asked: 2022-07-01 19:03:48 +0800 CST
  • 0

我听说 在postgresql 中，与完全解析的对应类型不同，该json类型保留了声明的顺序jsonb。

我想获取客户端提供的文档中每个键的序号位置。从我尝试过的情况来看，这似乎是不可能的。

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

前面的查询向我显示了位于序号位置 2 的叶节点值 3，尽管它是作为第三个键提供的，但似乎json_path_query没有jsonb_path_query.

json是否有解析键的内置函数？或者我是否仅限于更严格jsonb或完全不受约束text的解析来提取这些信息？

postgresql json
Martin Hope
Peter Vandivier
Asked: 2020-02-26 05:15:29 +0800 CST
  • 6

文档页面状态（强调我的）

退出状态

如果 psql 正常完成，则返回 0 给 shell，如果发生了自己的致命错误（例如内存不足，找不到文件），则返回 1，如果与服务器的连接变坏并且会话不是交互式的，则返回2，如果脚本中发生错误并且设置了变量 ON_ERROR_STOP。

但是，我似乎无法诱导退出代码 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$

我还在 FreeBSD 11.3 和 Darwin 19.3 上重现了这种行为。我是否误解了文档或使用了错误的语法？

postgresql psql
Martin Hope
Peter Vandivier
Asked: 2020-01-08 10:44:06 +0800 CST
  • 2

我public在一个数据库中有两个非模式。两个模式中都存在一个名为的表"foo"，我可以对两者执行所有必要的 DDL 和 DML 命令。但是，当我执行时\d，我看不到它们。是什么赋予了？

复制品

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;

在psql

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

我\set ECHO_HIDDEN on提取了运行的基础查询\d并将相关行从WHERE子句移到 中，SELECT以检查两个表对象之间的差异。

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;

运行此查询显示...

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

相关的差异似乎是 的输出pg_table_is_visible()。但是为什么False在询问是否b.foo可见时它应该返回“”？

为什么要pg_table_is_visible()返回False我可以访问的表？

postgresql
Martin Hope
Peter Vandivier
Asked: 2019-11-28 07:16:11 +0800 CST
  • 6

今天我想定义一个 uuid 的 value 00000000-0000-0000-0000-000000000000。作为一名 SQL Server 人员，我通常会...

select cast(0x0 as uniqueidentifier);

...但我现在在 postgres 世界里，所以我拿出了一个明智的...

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

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

该死！因此，我前往有关类型转换的 postgres 文档，希望看到像这样的文档来查看默认情况下我可以在哪些数据类型之间进行转换，而无需创建显式转换。

sql-server-implicit-explicit-conversion-matrix

如果文档中确实存在这样的图表，则它被很好地隐藏了。谷歌在这方面同样不是很有帮助。

对于 postgresql 中的默认允许类型转换，是否有很好的文档参考？

需要明确的是，我实际上并不关心 uuid

postgresql datatypes
Martin Hope
Peter Vandivier
Asked: 2019-11-19 05:51:06 +0800 CST
  • 1

我正在将模式从 NoSQL 迁移到 postgres。今天的对象“ b”作为对象的一个​​元素存在于 NoSQL 中，并且a在源代码中看起来像这样......

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

...我暂时将其分配给类似于以下 ERD 的模式

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

完整的 db_fiddle

我希望能够将规范化数据重新组合成原始对象，但我一直无法这样做。我没有将“ c”对象作为直接键值对获取，而是获取整个对象。例如...

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;

...返回...

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

...您会注意到，与源对象相比，它具有额外的嵌套级别。我怀疑我一开始就出错了，json_build_object但到目前为止还无法成功地重建b原始 json 文档中出现的“”对象。

我应该对查询和/或底层架构进行哪些修改以规范化和非规范化该对象？

postgresql-10 json
Martin Hope
Peter Vandivier
Asked: 2019-07-18 02:54:51 +0800 CST
  • 6

我正在设计一个过程来测试我的 postgresql 10.8 备份，方法是将它们还原到一次性虚拟机中的随机时间点。我一直无法完全自动化这个过程。我在官方文档的第 8 步（第 25.3.4 节）被阻止

  1. 启动服务器。

执行pg_ctl startover ssh 时，命令会挂起，直到它被杀死。如果我直接 ssh 到 VM 上并执行pg_ctl start，那么命令会按预期快速返回。

2012 年的这个帖子似乎描述了类似的场景。然而，在我的情况下，即使调用会话在挂起时被终止，postgres 进程也会成功启动（虽然可能在 9.0.5 和 10.8 之间有所改进？）。

这个 github 问题似乎是相关的，但遗憾的是通过用我不知道的语言进行长时间的重写来“解决”并最终得出结论，这是pg_ctl二进制文件中的一个错误。

大问题

如何自动执行第 8 步，以便继续对备份媒体进行后续验证测试？

这是我需要破解的二进制文件中的一个突出错误吗？还是我错过了一个明智的实施？

restore postgresql-10
Martin Hope
Peter Vandivier
Asked: 2018-10-11 07:25:36 +0800 CST
  • 3

我正在尝试将我的 SQL Agent 作业和计划作为.json文件进行源控制，并使用dbatools SQL Agent 命令套件部署它们。

给定$sa = Get-Credential和foo.config以下形式......

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

尝试foo.config在解决方案中使用失败并显示以下消息的基本解析：

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

警告：[15:50:04][New-DbaAgentSchedule] 未提供时间表！请提供时间表名称。

好吧，这很糟糕......特别是因为以下工作正常......

$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

我真的不想打扰每个人... -Param1 $foo.Param1 -Param2 $foo.Param2 ... ，因为我很懒。我真的更愿意将我的配置文件拼成各种命令。为什么这不起作用!!?!1!

sql-server-agent powershell
Martin Hope
Peter Vandivier
Asked: 2018-06-22 05:02:56 +0800 CST
  • 2

当询问在任何给定服务器上哪些数据库是可写的时，我一直在使用下面的代码片段来列出PRIMARY副本数据库和不参与 AG 的数据库。

既然我已经部署了分布式可用性组，它就无法正常工作了！由于本地转发器在 DMV 上报告为PRIMARY副本，所以sys.dm_hadr_availability_replica_states我的旧查询告诉我数据库是可写的，而实际上是不可写的。

如何更改此查询以排除本地转发器副本中的数据库？

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]
);

……或者更简单的……

如何列出所有本地转发器数据库？

sql-server availability-groups
Martin Hope
Peter Vandivier
Asked: 2018-05-11 23:34:00 +0800 CST
  • 2

我很懒惰，宁愿参数化一次（以获取名称的 object_id）并Invoke-SqlCmd用于其余工作。

给定一系列旨在与任意服务器对象一起使用的函数，下面的测试是否足以确认 CmdletBinding 一个输入参数可以Int32防止通过用户提供的方式进行注入$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)
sql-server powershell
Martin Hope
Peter Vandivier
Asked: 2018-02-23 13:52:02 +0800 CST
  • 15

如果文件组包含列存储索引，则似乎设置文件组以read_only防止dbcc checkdb整个数据库。尝试运行checkdb或checkfilegroup（对于数据库中的任何文件组，包括读写辅助和[PRIMARY]）时，返回以下错误...

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.

在只读文件组中有列存储数据的受支持方法吗？还是在这种情况下我被排除在完整性检查之外？

复现

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

免责声明：交叉发布到technet 论坛

sql-server dbcc-checkdb
Martin Hope
Peter Vandivier
Asked: 2017-04-21 12:58:13 +0800 CST
  • 4

我想启用一个将大量数据转储到暂存区的应用程序，以便能够启动将数据转换并加载到生产区的存储过程。如果可能，我不想授予应用程序对生产区域的任何访问权限。不幸的是，EXECUTE AS似乎让我失望了。作为参考，有问题的数据库正在运行2008 R2，但我正在我的2016本地主机上重新创建行为。同样，有问题的服务帐户是一个域凭据，但我已经使用 SQL Auth 凭据重新创建了行为以实现可移植性。

设置

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

测试

从Stage数据库，exec spLoadStageToProd;成功。不出所料，exec as login = 'ServiceAccount'; exec spLoadStageToProd;失败并出现错误...

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'.

好的，所以我会尝试execute as owner让 proc 拥有完成工作所需的权限，但（拥有密码的开发人员）ServiceAccount不会在我的生产区域乱搞。

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

射击！那么现在 Staging of 的一个简单测试exec spLoadStageToProd;返回错误：

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"当然是我：与sysadmin服务器角色的愚蠢...

嗯，这很奇怪……我试过alter authorization on object::spLoadStageToProd to dbo;了，但弹出了同样的错误（消息 916）。更何况……exec as login = 'ServiceAccount'; exec spLoadStageToProd;现在又回来了……

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'.

算了！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.

...

...

……伤心。

所以......显然我误解了一些基本的东西。我尝试启用跨数据库所有权链接，以防万一这是粘性检票口但没有运气。在毫无结果地谷歌搜索并快速浏览了这 两个dba.se 问题之后，我陷入了困境。我哪里错了？

我如何让这个 SProc 在提升的跨数据库权限下运行，而不管是谁调用它？

引发这个问题的项目是一个迭代弃用，所以我认为随着时间的推移，证书签名最终可能会成为一个相当大的管理开销。

如果可以使用此存储过程作为包装器来执行与调用它的人无关的受限操作，这就是最终目标。

我相信 SProc 不会被不当修改，因此从其中执行的任何内容都可能获得高度许可 - 希望我不需要ServiceAccount逐一管理权限。

礼貌清理脚本

revert;
use [master]
go
drop database Prod,Stage;
drop login ServiceAccount;
go
sql-server permissions
Martin Hope
Peter Vandivier
Asked: 2016-09-23 19:22:41 +0800 CST
  • 4

给出表格的表格......

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

WHERE...在什么（如果有的话）目录视图（或其他格式良好的参考对象）中可以找到并严格标识子句中的列名称？

sys.indexes可以查看提醒我们的filter_definition是([_bigint] IS NOT NULL AND [id]>(5))。

sys.index_columns可以查看以告诉我们ON和INCLUDE列，但仅列出原始CREATE INDEX命令中 5 列中的 3 列。filter_predicate此处未找到这些列。

我必须假设filter_predicate在某个阶段严格检查了依赖关系，因为sp_rename 'dbo.FilterIDXTest._bigint', 'foo_bigint', 'column'失败并出现错误...

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.

...而且我无法让自己相信失败的唯一原因是因为在sys.indexes.filter_definition.

作为参考，手头的任务是从许多未记录的遗留服务器中提取源代码。我为此目的使用 RedGate/一些其他付费助手应用程序的请求已被拒绝，因为“你可以从 UI 中编写出对象，对吧？ ”。也许我过于严谨了，但我希望非常确信我正在签入源代码管理的索引是实际存在的索引。我正在sql server 2012上测试这个脚本，但它最终将在其上执行的目标服务器范围从2005 SP1到2008 R2 SP3。

sql-server-2012 metadata
Martin Hope
Peter Vandivier
Asked: 2016-02-11 08:45:43 +0800 CST
  • 8

将一个 quickie proc 放在一起以帮助调试，我遇到了一个似乎是编译器错误的问题。

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;

尝试以上操作会返回以下错误

消息 2714，级别 16，状态 1，过程 spFoo，第 19 行
数据库中已经有一个名为“#bar”的对象。

在人类可读的意义上，proc 看起来很好：只有一个select into语句会被执行，因为它们被包裹在if-else块中。不过很好，SQL Server 无法确认这些语句在逻辑上是相互排除的。也许更令人困惑的是，当drop table #foo被放置在 if-else 块中时，错误仍然存​​在（假设会告诉编译器取消分配对象名称），如下所示。

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;

proc 本身很好。我吸收了它并编写了create table #foo( ... )andinsert #foo ( ... )语句，我一直试图跳过select * into 语法。在这一点上，我只是想理解为什么编译器用懒惰的语法对我不利。我唯一能想到的是 DDL 命令保留对象名称IN TEMPDB。

为什么是粗体字？

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

这将失败并显示与上述相同的错误代码。但是下面...

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

...成功。上面的原始 proc 尝试也是如此。所以...

我的问题是

TempDB与用户数据库相比，对象的对象名称保留有什么区别（以及为什么存在） 。我看过的逻辑查询处理参考和 DDL 命令参考都没有解释这一点。

sql-server tempdb
Martin Hope
Peter Vandivier
Asked: 2015-08-13 07:49:07 +0800 CST
  • 4

我想导航以从内嵌注释的 UNC 路径打开目录路径。SSMS 与Windows 资源管理器集成，资源管理器允许从地址栏进行 UNC 导航。

事实上，在集成浏览器中，可以在 UNC 路径之间导航。http:/但是， UNC 路径似乎不存在 SSMS 文本编辑器中为前缀 URL 提供的点击功能。/Web Browser部分似乎可以打开 Internet Explorer 选项，/ /Options部分具有“启用单击 URL 导航选项”，但似乎不允许定义检测为 URL 的内容。EnvironmentText EditorPlain TextGeneral

下面链接的截图

UrlAllowsClickThrough

UncDoesNotAllowClickthrough

TextEditor_PlainText_General

（下面添加了示例代码）

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?'
sql-server ssms
Martin Hope
Peter Vandivier
Asked: 2015-06-30 19:43:05 +0800 CST
  • 1

我有一个高价值用户的产品支持请求，要求他调整公司范围仪表板上的设置，以便他可以管理他的工作流程中目前被排除在这些报告之外的一部分。鉴于两个显而易见的选择

  1. 更改公司范围内所有用户的仪表板设置或
  2. 仅为 HVU 复制仪表板

...决定我应该创建一个“开发”实例，HVU 可以在其中看到他的仪表板的变体。为此，我将该系列移植到同一目录树中的.rdl另一个实例，并将相关数据集中的所有存储过程从.ReportServerSchema1Schema1_dev

问题：存储过程和安全模式都存在于同一服务器和数据库中，因此不存在孤立用户的风险。除了通过 SSMS GUI 检查之外，是否有可能/什么是克隆所有当前用户权限的最佳方法Schema1？Schema1_dev

sql-server-2008-r2 schema