我有一个多步骤 PostgreSQL + PostGIS 查询/地理处理操作,每次运行时都需要更改输出表的架构名称。我使用的基表都位于另一个不会更改的架构中,但查询的输出每次都是在另一个项目架构中创建的。
例如,我有一个新项目“project_abc_2023”,这是我创建的新模式。目前,我必须将所有输出表模式名称更改为这个新的“project_abc_2023”模式。我希望能够将这些模式名称更改为我创建的项目模式。
我有一个多步骤 PostgreSQL + PostGIS 查询/地理处理操作,每次运行时都需要更改输出表的架构名称。我使用的基表都位于另一个不会更改的架构中,但查询的输出每次都是在另一个项目架构中创建的。
例如,我有一个新项目“project_abc_2023”,这是我创建的新模式。目前,我必须将所有输出表模式名称更改为这个新的“project_abc_2023”模式。我希望能够将这些模式名称更改为我创建的项目模式。
我正在使用 jooq 生成 SQL 查询,并且我有点懒惰从 Bottom_select 的 another_select 生成嵌套选择。我生成的查询包含重复的表达式。postgres 13+ 是否在幕后应用了一些优化技术?
查询的简化示例部分
SELECT
case
when b.count is null then 100.00
when c.count is null then -100.00
else round(
cast((100.00 * ((c.count - b.count) / cast(b.count as numeric))) as numeric),
2
)
end as expression_01,
case
when b.count is null then 100.00
when c.count is null then -100.00
else round(
cast((100.00 * ((c.count - b.count) / cast(b.count as numeric))) as numeric),
2
)
end as expression_02
from some_table
比方说
round(
cast((100.00 * ((c.count - b.count) / cast(b.count as numeric))) as numeric),
2
)
重复多次,Postgres会计算一次还是多次?
设置:
db.t3.xlarge
RDS 实例。该数据库连接到在 Lambda 中运行的 Laravel 应用程序。lambda 中的过程之一需要计算表中的行数spin
。
在第一次迭代期间,我们发现select count()
以下面的方式编写的内容有时会花费大量的秒数。有些请求是立即发出的,而另一些请求可能需要 20 秒以上。
select count(*) from spin;
我们在互联网上挖掘了一些抱怨此事的人的答案。我们决定向查询添加一个条件,这使其成为亚秒级查询:
select count(*) from spin where id > 0;
直到几天前,我们的服务开始收到比平时更多的流量,并且查询运行时间变得非常跳跃。
+--------+-------+------------------+---------+---------+------+-----------+----------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+--------+-------+------------------+---------+---------+------+-----------+----------------------------------------------------------+
| 114168 | vmx | 10.0.2.175:43169 | fdata | Execute | 60 | executing | select count(*) as aggregate from `spin` where `id` > 0 |
| 114171 | vmx | 10.0.3.149:31136 | fdata | Execute | 58 | executing | select count(*) as aggregate from `spin` where `id` > 0 |
| 114118 | vmx | 10.0.2.175:36571 | fdata | Execute | 109 | executing | select count(*) as aggregate from `spin` where `id` > 0 |
+--------+-------+------------------+---------+---------+------+-----------+----------------------------------------------------------+
我怀疑这一定是由于对表的某些访问锁定造成的spin
。在表锁定期间,select count()
挂起。
任何意见表示赞赏,谢谢。
我正在配置具有三个副本的 SQL Server 可用性组:node1\inst
、node2\inst
和node3\inst
。我已设置node1\inst
为通过同步提交进行自动故障转移,而node2\inst
和 则node3\inst
配置为手动故障转移。
我对故障转移行为有几个问题:
当node1\inst
主副本遇到问题时,它是否会自动故障转移到node2
或者node3
即使它们设置为手动故障转移?此外,自动故障转移是否仅发生在自动故障转移和同步提交副本之间?
如果node2
成为主副本并且发生问题,它是否仍然遵循其手动故障转移设置,或者是否会自动故障转移到node1
?
我试图了解 SQL Server 可用性组中复杂的故障转移机制,以确保可靠的高可用性设置。深入了解如何在这些配置中确定自动故障转移决策将非常有帮助。
提前感谢你的帮助!
create table test (
id int identity,
id_int int default 1
)
insert test default values
go 1000
begin transaction
update test
set id_int = id_int
where id = 1000
waitfor delay '00:00:10'
commit
另一个会话无需等待即可得到结果:
set transaction isolation level read committed
select * from test
where id =1000
id | id_int
---------------
1000 |1
有人可以解释为什么我立即得到结果吗?
但是,当我使用可重复读隔离级别时,我应该等待:
set transaction isolation level repeatable read
select * from test
where id =1000
在 SSMS 主屏幕的右下角和显示我的用户名和我所在数据库名称的左侧,它给出了我所在的服务器的名称。该字符串与我首次打开 SSMS 时连接到服务器时在“服务器名称”字段中输入的字符串相同,并且也显示在对象资源管理器中。SSMS 如何确定此名称?我试过了
SELECT
HOST_NAME(),
@@SERVICENAME,
DB_NAME,
@@SERVERNAME,
SERVERPROPERTY('InstanceName'),
SERVERPROPERTY('MachineName')
但只SERVERPROPERTY('MachineName')
返回 SSMS 显示内容的子字符串。其余的与所讨论的字符串完全无关。
当我处理碎片时,我有一个关于非聚集索引的问题。在非聚集索引中,实际表数据(通常存储在聚集索引中,否则存储在堆中)与非聚集索引分开存储。当我创建非聚集索引时,这对表和索引碎片有何影响?
我很欣赏详细的回复。
我正在尝试使用以下命令将 Postgres 集群从 PG 12 升级到 PG 15:
sudo pg_upgradecluster 12 main --method=link
我的集群使用一些表空间:
postgres=# SELECT oid, spcname, pg_tablespace_location(oid) FROM pg_tablespace;
oid | spcname | pg_tablespace_location
------------+--------------------+----------------------------------
1664 | pg_global |
1663 | default_old |
1905123481 | ts1 | /mnt/postgres/ts1
1905522771 | ts2 | /mnt/postgres/ts2
1905595436 | ts3 | /mnt/postgres/ts3
当我运行 pg_upgradecluster 脚本时,它立即失败并出现以下错误:
tablespace directory "" does not exist
所有表空间都位于不同的磁盘上
我已经将另一个没有表空间的集群从 PG 12 升级到 PG 15,没有任何错误。
我不明白现在该怎么办:(
有没有人以前遇到过这个问题并且知道如何解决它?
提前致谢
我正在尝试在 C 中构建存储函数,它将采用一个 bigint 数组作为参数,并返回另一个数组,该数组是参数数组的副本,但所有元素都递增 1。我的问题是,当我将这段代码作为独立程序运行时,它运行速度非常快:10M 元素需要 0.1 秒,但作为 Postgresql C 存储过程运行时需要 6 秒。
我想知道是否有人可以发现我是否做了任何明显错误的事情?...
我的C存储过程:
#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"
#include "utils/array.h"
#include "catalog/pg_type.h"
PG_MODULE_MAGIC;
#define ARRPTR(x) ( (int64 *) ARR_DATA_PTR(x) )
#define ARRNELEMS(x) ArrayGetNItems(ARR_NDIM(x), ARR_DIMS(x))
PG_FUNCTION_INFO_V1(test_inc);
Datum
test_inc(PG_FUNCTION_ARGS)
{
ArrayType *a = PG_GETARG_ARRAYTYPE_P(0);
int n = ARRNELEMS(a);
int nbytes = ARR_OVERHEAD_NONULLS(1) + sizeof(int64) * n;
ArrayType *r = (ArrayType *) palloc0(nbytes);
SET_VARSIZE(r, nbytes);
ARR_NDIM(r) = 1;
r->dataoffset = 0; // marker for no null bitmap
ARR_ELEMTYPE(r) = INT8OID;
ARR_DIMS(r)[0] = n;
ARR_LBOUND(r)[0] = 1;
int64 *ad = ARRPTR(a);
int64 *rd = ARRPTR(r);
ereport(WARNING,
errcode(ERRCODE_WARNING),
errmsg("Before loop"));
for (int i = 0; i < n; i++) {
rd[i] = ad[i] + 1;
}
ereport(WARNING,
errcode(ERRCODE_WARNING),
errmsg("After loop"));
PG_RETURN_POINTER(r);
}
我这样编译并安装它:
gcc -fPIC -O2 -I/usr/include/postgresql/15/server -I/usr/include/postgresql/internal -c test.c
gcc -shared -o test.so test.o
/usr/bin/install -c -m 755 test.so '/usr/lib/postgresql/15/lib/'
我用来测试它的SQL代码:
CREATE FUNCTION test_inc(_int8) RETURNS _int8 AS '/usr/lib/postgresql/15/lib/test.so'
LANGUAGE C IMMUTABLE PARALLEL SAFE;
create table t as select generate_series(0, 10000000) n;
create table t2 as select array_agg(n order by n) n from t;
create table t3 as select test_inc(n) n from t2;
最后一个查询在 6 秒内运行。删除一行rd[i] = ad[i] + 1;
代码可以在 0.6 秒内运行。另外,查看警告消息,我发现执行卡在循环之后的某处,而不是循环内部。
我的独立 C 代码如下所示,运行时间为 0.1 秒:
#include <stdio.h>
#include <stdlib.h>
int main(int argc, char ** argv) {
long n = 10000000;
long *a = (long*)malloc(n * sizeof(long));
long *r = (long*)malloc(n * sizeof(long));
for (int i = 0; i < n; i++) {
a[i] = i;
}
for (int i = 0; i < n; i++) {
r[i] = a[i] + 1;
}
// To make sure compiler does not remove previous loop because result is unused.
long res = 0;
for (int i = 0; i < n; i++) {
res += r[i];
}
printf("%ld", res);
}
在查询计划中,我看到正在转换的行的类型。有一个应该使用的索引,但没有。类型转换是一个危险信号吗?
该表有一个 ANALYZE 并有 1.1 亿行。其中,10% 与查询匹配。
这是完整的解释:
explain select * from item_info where subtype = 'Dataset';
QUERY PLAN
------------------------------------------------------------------------------------
Gather (cost=1000.00..1592021.24 rows=1320650 width=27)
Workers Planned: 2
-> Parallel Seq Scan on item_info (cost=0.00..1458956.24 rows=550271 width=27)
Filter: ((subtype)::text = 'Dataset'::text)
(4 rows)
Time: 0.652 ms=> \d item_info
Table "public.item_info"
Column | Type | Collation | Nullable | Default
--------------+-------------------+-----------+----------+---------
root_item_pk | bigint | | not null |
type | character varying | | |
subtype | character varying | | |
Indexes:
"item_info_pkey" PRIMARY KEY, btree (root_item_pk)
"item_info_subtype_idx" btree (subtype)
"item_info_type_idx" btree (type)