我不确定HEAP_XMAX_COMMITTED对应什么样的真实情况。[1] 中没有关于 HEAP_XMAX_COMMITTED 的测试用例。
两个会话,如何重现元组标记为 HEAP_XMAX_COMMITTED 的情况?
[1] https://git.postgresql.org/cgit/postgresql.git/plain/contrib/pageinspect/expected/page.out
我不确定HEAP_XMAX_COMMITTED对应什么样的真实情况。[1] 中没有关于 HEAP_XMAX_COMMITTED 的测试用例。
两个会话,如何重现元组标记为 HEAP_XMAX_COMMITTED 的情况?
[1] https://git.postgresql.org/cgit/postgresql.git/plain/contrib/pageinspect/expected/page.out
假设我有这张表:
CREATE TABLE nodes (node_path ltree);
INSERT INTO nodes VALUES ('Top.Science');
INSERT INTO nodes VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO nodes VALUES ('Top.Science.Astronomy.Cosmology');
INSERT INTO nodes VALUES ('Top.Hobbies');
INSERT INTO nodes VALUES ('Top.Hobbies.Amateurs_Astronomy');
INSERT INTO nodes VALUES ('Top.Collections.Pictures.Astronomy');
INSERT INTO nodes VALUES ('Top.Collections.Pictures.Astronomy.Stars');
INSERT INTO nodes VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
INSERT INTO nodes VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');
INSERT INTO nodes VALUES ('Top.Dislikes');
CREATE INDEX ON nodes USING GIST (node_path);
CREATE INDEX ON nodes USING BTREE (node_path);
请注意,此表中缺少以下路径:
'Top'
'Top.Science.Astronomy'
'Top.Collections'
'Top.Collections.Pictures'
如何递归查询该表以获得树状结构(没有那些丢失的行)?
如果路径没有丢失,那么以下查询将得到结果:
with recursive
base as (
select node_path,
array[row_number() over (order by node_path)] as sort_path
from nodes
where nlevel(node_path) = 1
union all
select c.node_path,
p.sort_path||row_number() over (order by c.node_path)
from base p
join nodes c
on subpath(c.node_path, 0, -1) = p.node_path
)
select * from base order by sort_path;
但由于行缺失,它破坏了链条。结果,诸如此类的事情:
nlevel(node_path) = 1
subpath(c.node_path, 0, -1) = p.node_path
没有道理。
我如何查询这个来获取树?
请注意,我在 Stack Overflow 上发布了一个问题,我想如果我能得到上述问题的答案,那么我也许也能解决我的问题。当前的问题源于 SO 问题:
这是基本的 SQL 代码
create table users (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(50),
last_name varchar(50),
age tinyint
);
insert into users (name, last_name, age)
values
("John", "Doe", 30),
("Jane", "Doe", 35);
create table departments (
user_id int,
department varchar(50),
foreign key (user_id) references users(id)
);
insert into departments
values
(1, "IT"),
(2, "HR");
我之前没有质疑过,但现在它对我来说看起来太奇怪了,所以我决定就此提出一个问题。为什么在定义外键关系时需要使用括号表示法而不是常规的点表示法?为什么不是
foreign key user_id references users.id
有效的语法?我不明白它会如何引起任何歧义或任何事情
我不确定“为什么”问题是这里的主题,但我希望它们是
您有一个接受日期时间数组的存储过程,这些数组被加载到临时表中,并用于过滤表中的日期时间列。
编写查询来执行过滤的最有效方法是什么?
USE StackOverflow2013;
CREATE TABLE
#d
(
dfrom datetime,
dto datetime,
PRIMARY KEY (dfrom, dto)
)
INSERT
#d
(
dfrom,
dto
)
SELECT
dfrom = '2013-11-20',
dto = '2013-12-05'
UNION ALL
SELECT
dfrom = '2013-11-27',
dto = '2013-12-12';
CREATE INDEX
p
ON dbo.Posts
(CreationDate)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
我能得到的最好的就是EXISTS
像这样使用:
SELECT
c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE EXISTS
(
SELECT
1/0
FROM #d AS d
WHERE p.CreationDate BETWEEN d.dfrom
AND d.dto
);
这导致了一个看起来相当悲伤的执行计划:
嵌套循环是唯一可用的连接运算符,因为我们没有相等谓词。
我正在寻找的是产生不同类型连接的替代语法。
谢谢!
在事实表或事务表上选择聚集索引的好策略是什么?我正在使用 SQL Server 2019。
我选择了一个具有以下属性的通用销售表 (FactSales):
我可以看到 5 个选项(但可能还有更多),我在下面编写了这些选项——各有利弊。
请让我知道您会选择什么。以及为什么。
CREATE TABLE dbo.FactSales
(
DateOfSaleKey INT NOT NULL,
StoreKey INT NOT NULL,
CheckNumber INT NOT NULL, -- not unique across stores
SaleItemKey INT NOT NULL,
CashierKey INT NOT NULL,
TerminalKey INT NOT NULL,
SaleTypeKey INT NOT NULL,
TimeSlotKey INT NOT NULL,
TransactionTypeKey INT NOT NULL,
SaleTime DATETIME NOT NULL,
SalesQuantity INT NOT NULL,
SalesNet DECIMAL (16, 8) NOT NULL,
SalesGross DECIMAL (16, 8) NULL,
VAT DECIMAL (16, 8) NOT NULL,
DiscountQuantity INT NOT NULL,
Discount DECIMAL (16, 8) NOT NULL,
VoidQuantity INT NOT NULL,
Void DECIMAL (16, 8) NOT NULL,
RefundQuantity INT NOT NULL,
Refund DECIMAL (16, 8) NOT NULL,
)
ALTER TABLE dbo.FactSales ADD CONSTRAINT PK_FactSales PRIMARY KEY NONCLUSTERED (DateOfSaleKey, StoreKey, CheckNumber, SaleItemKey)
-- OPTION #1: add a surrogate key (identity) and make that the clustered index
-- unique, narrow and always increasing, but unnecessary column
ALTER TABLE dbo.FactSales ADD SalesKey INT IDENTITY NOT NULL
CREATE UNIQUE CLUSTERED INDEX CX_FactSales ON dbo.FactSales (SalesKey)
-- OPTION #2: make the primary key also the clustered index:
-- unique, but wide
ALTER TABLE dbo.FactSales ADD DROP CONSTRAINT PK_FactSales
ALTER TABLE dbo.FactSales ADD CONSTRAINT PK_FactSales PRIMARY KEY CLUSTERED (DateOfSaleKey, StoreKey, CheckNumber, SaleItemKey)
-- OPTION #3: base the clustered index on how the data is inserted
-- optimised for inserting new data, but not unique
CREATE CLUSTERED INDEX CX_FactSales ON dbo.FactSales (DateOfSaleKey, StoreKey)
-- OPTION #4: base the clustered index on how the data is selected
-- optimised for inserting new data and some reports, but not unique and getting wider
CREATE CLUSTERED INDEX CX_FactSales ON dbo.FactSales (DateOfSaleKey, StoreKey, SaleItemKey)
-- OPTION #5: base the clustered index on how the data is selected -- more selective (so it covers more reports)
-- optimised for inserting new data and more reports, but not unique and even wider
CREATE CLUSTERED INDEX CX_FactSales ON dbo.FactSales (DateOfSaleKey, StoreKey, SaleItemKey, CheckNumber)
在我的设置中,此问题的相关表是:
和表非常简单,这里是和表(是我的模式的名称)items
:users
collections
collection_items
mi
CREATE TABLE IF NOT EXISTS mi.users (
id UUID DEFAULT uuid_generate_v4 () CONSTRAINT users_pk PRIMARY KEY,
created_at TIMESTAMP DEFAULT now(),
name VARCHAR(50) UNIQUE NOT NULL CONSTRAINT min_length_users_name CHECK (char_length(name) >= 4),
email VARCHAR UNIQUE NOT NULL,
pic BYTEA
);
CREATE TABLE IF NOT EXISTS mi.items (
id UUID DEFAULT uuid_generate_v4 () CONSTRAINT items_pk PRIMARY KEY,
created_at TIMESTAMP DEFAULT now(),
author_id UUID NOT NULL CONSTRAINT item_author_fk REFERENCES mi.users (id),
title VARCHAR(300) NOT NULL CONSTRAINT min_length_items_title CHECK (char_length(title) >= 4),
description VARCHAR(5000) CONSTRAINT min_length_items_description CHECK (char_length(description) >= 5)
);
CREATE TABLE IF NOT EXISTS mi.collections (
id UUID DEFAULT uuid_generate_v4 () CONSTRAINT collections_pk PRIMARY KEY,
created_at TIMESTAMP DEFAULT now(),
author_id UUID NOT NULL CONSTRAINT collection_author_fk REFERENCES mi.users (id),
title VARCHAR(300) NOT NULL CONSTRAINT min_length_items_title CHECK (char_length(title) >= 4),
description VARCHAR(5000) CONSTRAINT min_length_items_description CHECK (char_length(description) >= 5)
);
CREATE TABLE IF NOT EXISTS mi.collection_items (
id UUID DEFAULT uuid_generate_v4 () CONSTRAINT collection_items_pk PRIMARY KEY,
created_at TIMESTAMP DEFAULT now(),
collection_id UUID NOT NULL CONSTRAINT collection_items_collection_fk REFERENCES mi.collections (id),
item_id UUID NOT NULL CONSTRAINT collection_items_item_id REFERENCES mi.items (id),
CONSTRAINT you_can_only_add_an_item_once_to_a_collection UNIQUE (collection_id, item_id)
);
我想要的是,在查询集合时,还以 JSON 对象的 JSON 数组返回其项目(我正在使用node-pg
,这就是为什么有带有 的变量${}
):
SELECT
mi.collections.created_at,
jsonb_build_object(
'id', mi.collections.author_id,
'name', mi.users.name,
'pic', mi.users.pic
) author,
mi.collections.title,
mi.collections.description,
(
SELECT COUNT(*)
FROM mi.collection_items
WHERE mi.collection_items.collection_id = '${collectionId}'
) AS total_items,
-- This is where I'm trying to get it done.
jsonb_build_array(
SELECT
jsonb_build_object(
'id', mi.items.id,
'title', mi.items.title,
'description', mi.items.description
) item
FROM mi.collection_items
JOIN mi.items ON mi.collection_items.item_id = mi.items.id
WHERE mi.collection_items.collection_id = '${collectionId}'
ORDER BY mi.collection_items.created_at DESC
OFFSET ${offset}
LIMIT ${paginationLimit}
)
FROM mi.collections
JOIN mi.users ON mi.users.id = mi.collections.author_id
WHERE mi.collections.id = '${collectionId}'
该查询将产生以下形式的结果:
{
"created_at": "2023-07-10T20:54:34.883Z",
"title": "Title 1",
"description": "A great description",
"author": {
"id": "906ca589-db0f-4c6e-add4-220cc3f2ea95",
"name": "john",
"pic": "\\x2f686f6d652f7068696c692f436f64652f6d61676e75735f696e6465782f6261636b656e642f6173736574732f737461636b6f766572666c6f775f7069635f312e706e67"
},
"total_items": 2,
"items": [
{
"id": "906ca589-abcd-4c6e-add4-220cc3f2ea95",
"title": "Title 1",
"description": "Description 1"
},
{
"id": "906ca589-efgh-4c6e-add4-220cc3f2ea95",
"title": "Title 2",
"description": "Description 2"
}
]
}
我们有两台运行 SQL Server 2019 (15.0.x) 的服务器。
当我们进行完整数据库备份(通过右键单击数据库,然后单击“任务”,然后单击“备份”)时,是否包括备份所有项目,然后将其Programmability
还原到其他服务器?
我面临 MariaDB 数据库的磁盘空间问题,非常感谢您对解决该问题的见解。
这是场景:
MariaDB 版本:10.3
操作系统:Ubuntu 20.04
引擎:Innodb
表名:'foo'
数据长度:26GB
索引长度:16GB
可用数据:7GB
碎片 ~ 17%
innodb_file_per_table=ON
问题是存储数据库数据的主磁盘只剩下 8GB 可用空间。我们最近遇到了对“foo”表的批量删除操作,这导致了碎片。我想将空间回收回操作系统。
考虑到可用空间有限,OPTIMIZE TABLE
直接在“foo”表上运行命令是不可行的。该操作需要额外的磁盘空间来创建表的碎片整理副本。
我有一个额外的磁盘,空间足够,可以用来解决这个问题。夜间停机时间可能长达 6 小时。
这是我正在考虑的方法,非常感谢您对其可行性的反馈:
ALTER TABLE foo ENGINE innodb;
您是否建议使用其他方法来有效处理这种情况?
DBCC INPUTBUFFER
我知道SQL Server中有一个命令,
对我来说,输入缓冲区似乎是会话正在运行的 SQL。
SQL Server 也有sys.dm_exec_sql_text
,
并且sys.dm_exec_input_buffer
,其中
我有点困惑。对我来说,它们看起来非常相似且难以辨认。
我还看到了Jonathan Kehayias 的一篇文章 Understanding the sql_text Action in Extended Events
。他在文章中指出
创建此事件会话后,我们可以在环境中运行几个不同的测试场景,以显示此操作如何不是 sql_text,而是正在触发的事件的 input_buffer
他非常确信 SQL 文本不是输入缓冲区,我不明白其中的原因。有人可以帮忙澄清一下吗?谢谢。