Jack Douglas's questions

我正在寻找并排安装多个版本的 SQL Server 以同时运行。我想让他们监听不同的 IP 地址。

它们将在 Windows Server 2019 Core 上运行。

我目前使用这样的东西安装：

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

我是对的，Setup.exe 上没有指定要收听的 IP 地址的开关吗？如果不是，在 Setup.exe 运行后从命令行配置它的最简单方法是什么？

化身在本网站上另一个问题的回答中进行了解释。答案提到了“孤儿”化身：

…还有其他因素会导致 ORPHANED 化身和 OBSOLETE 备份…

我从 Oracle 文档中看到，V$DATABASE_INCARNATION其中包含一个STATUS列，该列的值必须为ORPHAN、CURRENT或PARENT，这必须是相关的。

什么是“孤儿”化身，哪些步骤会导致STATUS= ORPHANin出现一行V$DATABASE_INCARNATION？

这看起来很奇怪，我在 MySQL 命令行上得到的结果与通过 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;

| 富 |
| :------------------------ |
| 敏捷的棕色狐狸|
| 跳过懒狗|

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

| regexp_replace(foo, '[^az]+', '-') |
| :---------------------------------------- |
| 快棕狐 |
| 快棕狐跳过懒狗|

db<>在这里摆弄

我希望看到：

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

| REGEXP_REPLACE(foo, '[^az]+', '-') |
| :-------------------------------- |
| 快棕狐 |
| 跳过懒狗|

我做错了什么或者这是一个 MySQL 错误？

在不完全恢复并打开 9i 数据库后，resetlogs我们运行了一个成功完成的完整备份。备份包含一个命令，用于在完成后删除过时的备份：

delete noprompt obsolete device type sbt;

RMAN 配置为使用 REDUNDANCY 2：

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

然而，旧版本的所有备份都立即标记为过时并删除，而不考虑冗余。

1. 如果我们有一个RECOVERY WINDOW配置而不是，这种行为会有所不同REDUNDANCY 2吗？
2. 这种行为在更高版本的 Oracle 中是否相同？

---

编辑：添加的输出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

word_similarity 函数的文档说：

返回一个数字，指示第一个字符串与第二个字符串中最相似的单词的相似程度。该函数在第二个字符串中搜索最相似的单词而不是最相似的子字符串。结果的范围是零（表示两个字符串完全不同）到一个（表示第一个字符串与第二个字符串中的一个单词相同）。

但是如果“第一个字符串”包含多个单词，他们对于结果应该是什么有点模糊。以下结果的解释是什么：

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

| word_similarity |
| :---------------- |
| 1 |

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

| word_similarity |
| :---------------- |
| 1 |

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

| word_similarity |
| :---------------- |
| 0.8 |

dbfiddle在这里

一夜之间的硬盘驱动器故障会产生各种副作用，包括留下一个需要恢复的数据文件：

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

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

现在一切恢复正常，但仍有一个谜团：在恢复之前，当批处理作业试图访问数据文件中的段时，客户端应用程序将 ORA-00376 记录到日志记录表中。然而这个错误并没有出现在alert log中，怎么会这样呢？

在出现ORA-00376错误时，alert日志中确实出现了以下内容：

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

在最初的问题上：

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

new_customerWeb 应用程序每秒调用我的函数数次（但每个会话仅调用一次）。它所做的第一件事就是锁定customer表（执行“如果不存在则插入”- 的简单变体upsert）。

我对文档的理解是，其他调用new_customer应该简单地排队，直到所有先前的调用都完成：

LOCK TABLE 获得表级锁，如有必要，等待释放任何冲突的锁。

为什么有时会陷入僵局？

定义：

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;
$$;

来自日志的错误：

2015-07-28 08:02:58 BST 详细信息：进程 12380 等待数据库 12141 的关系 16438 上的 ExclusiveLock；被进程 12379 阻止。
        进程 12379 等待数据库 12141 的关系 16438 上的 ExclusiveLock；被进程 12380 阻止。
        进程 12380：选择 new_customer(decode($1::text, 'hex'))
        进程 12379：选择 new_customer(decode($1::text, 'hex'))
2015-07-28 08:02:58 BST 提示：有关查询详细信息，请参阅服务器日志。
2015-07-28 08:02:58 BST 上下文：SQL 函数“new_customer”语句 1
2015-07-28 08:02:58 BST 声明：选择 new_customer(decode($1::text, 'hex'))

关系：

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

---

编辑：

我已经设法获得了一个简单的可重现测试用例。对我来说，由于某种竞争条件，这看起来像是一个错误。

架构：

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;
$$;

bash 脚本在两个 bash 会话中同时运行：

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

错误日志（通常是 1000 次调用的少数死锁）：

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

---

编辑2：

@ypercube建议使用函数外部的变体lock table：

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

有趣的是，这消除了死锁。

我可以使用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;$$;

有没有办法用一条 SQL 语句做同样的事情？

从 9.2 开始，可以使用该security_barrier属性来防止恶意选择的函数和运算符访问“隐藏”在视图过滤器后面的数据（postgres 文档中的完整信息）。

您可以在下面的测试中看到这种情况的发生，但是在测试结束时使用 set-returning 函数而不是视图没有观察到相同的效果。

这只是这个单独测试的一个怪癖，还是设置返回函数总是一种安全的¹方法来防止这种泄漏？

试验台：

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

测试1：

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 │
└────────┘
*/

测试2：

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 │
└────────┘
*/

清理：

drop schema stack cascade;

---

_{¹出于性能原因，您可能不想走这条路线，即使它是安全的}

大表上的CLUSTER命令可能需要很长时间，并且在运行时会阻塞对表的读取和写入。

我不需要我的表中的数据严格按照索引顺序排序，我只希望经常一起查询的行更有可能位于同一个数据库块中，而不是均匀地分散在表中（这是它们的分布由于日期插入表的方式的性质，自然有）。

它可以产生很大的不同。在下面的示例中，唯一的区别是insert有一个额外的order by mod(g,10)，以便测试数据由 预先聚类host_id。当获取一个特定的所有数据时，需要读取的块要少得多host_id。

有没有一些方法可以在没有独占锁和命令的日志记录开销的情况下实现这种集群cluster？

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;

从文档：

在 PostgreSQL 中，行的 UPDATE 或 DELETE不会立即删除该行的旧版本 [...] 但最终，任何事务都不再对过时或已删除的行版本感兴趣。然后必须回收它占用的空间以供新行重用，以避免磁盘空间需求的无限增长。这是通过运行 VACUUM来完成的。

当真空运行时，它是否通过重写整个块有效地释放已删除行占用的空间，或者只有当新行小于为它们让路的已删除行时才会出现块碎片，新行适合可用的“洞” ?

表中的每一行都有一个类型的系统列 ，表示该行的物理位置：ctidtid

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

select ctid
     , id
from t;

ctid | ID
:---- | -：
(0,1) | 1
(0,2) | 2

dbfiddle在这里

ctid从最合适的类型（例如integer，bigint或）中获取页码的最佳方法是什么numeric(1000,0)？

我能想到的唯一方法是非常丑陋。

查询语言 (SQL) 和 PL/pgSQL 函数对临时表的处理方式不同：

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;

文档说：

...当临时表存在时，具有相同名称的现有永久表对当前会话不可见，除非它们被模式限定名称引用...

这没有提到上面演示的异常。这在其他地方有记录吗？

是一种 postgres 组合方式IS DISTINCT FROM还是ANY其他一些巧妙的方式来获得相同的结果？

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

我运行了这个命令：

cluster verbose address using address_locality_lookup;

并得到了这个结果：

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

这是否表明聚类成功（即所有行现在都按address_locality_lookup索引指示的顺序排列）？

“可移动”和“不可移动”分别表示什么？

这是我的实际问题的一个最小示例：

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

returning当然，您可以使用子句返回插入的列：

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

您还可以返回文字：

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 |
*/

但您不能返回源列：

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
*/

有什么办法可以摆脱w.rnd最后的returning条款吗？

db<>在这里摆弄

由于我对模型子句的理解有限，我预计以下三个查询会返回相同的结果，因为数据中没有空值，并且函数是等效的：

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 
*/

关于解释此行为的规则处理，我缺少什么？

_{SQLFiddle在这里}

对于以下数据，我希望能够删除一些行并插入其他行，给出以下结果。这是否可以通过单个语句（例如merge语句）来实现？

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 )}

- - 编辑：

澄清一下，我试图用一个单一的merge语句得到的效果是这样的：

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

我想要一个单语句解决方案的原因是为了防止另一个事务在主事务insert之间插入/提交数据的竞争条件。delete

我使用xmin系统列来实现一种乐观锁定的形式，有时需要“触摸”行来碰撞xmin而不实际更新行。我目前只是做一个“虚拟”更新：

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 小提琴）}

我很好奇是否有另一种在xid不进行更新的情况下碰撞的方法，类似于 unixtouch命令？

我发现自己写了以下内容：

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

并想知道是否有更简洁的方法而不会牺牲太多的可读性。

我找到了一种我作为答案发布的方式，但我对此并不完全满意，并且会对替代方案非常感兴趣

在这种情况下val是唯一的foo- 没有重复