VACUUM无法在事务块内运行 [英] VACUUM cannot run inside a transaction block

问题描述

我正在使用安装在azure VM中的postgresedb 9.6版本，并且在运行某些查询时面临更多的延迟问题.

i am using postgresedb 9.6 version which is installed in azure VM and facing more latency issues while running some queries.

从[table_name]中选择max(trans_booked_dt)作为max_dt，min(trans_booked_dt)作为min_dt

select max(trans_booked_dt) as max_dt, min(trans_booked_dt) as min_dt from [table_name]

将表[tablename_new]创建为
选择不同的a.trans_id，b.customer_id
来自tablename_1 a
内部联接tablename_2 b
在a.trans_id = b.trans_id;

create table [tablename_new] as
select distinct a.trans_id, b.customer_id
from tablename_1 a
inner join tablename_2 b
on a.trans_id = b.trans_id;

注意:到目前为止，我们没有这些表的索引.

Note: we dont have indexes for these tables as of now.

我在执行VACCUM命令时遇到以下错误，这是解决此问题的一部分.

i am getting the below error while executing VACCUM command as part of anlayzing this issue.

VACUUM(FULL，ANALYZE，VERBOSE)tablename_1;

VACUUM(FULL, ANALYZE, VERBOSE) tablename_1;

错误:VACUUM无法在事务块内运行

ERROR: VACUUM cannot run inside a transaction block

请让我知道为什么我会收到此错误.

Please let me know why i am getting this error.

推荐答案

巴杜古，

您正在使用哪个版本的SQL Workbench?

Which version of SQL Workbench are you using?

在连接窗口中打开自动提交"后，真空"可以正常运行.或者，您可以通过脚本执行此操作:

After turn on 'Autocommit' at connection window, 'vacuum' can running normally. Or you can do this via your script:

vacuum;

SET autocommit ON/OFF 连接配置文件 SQL 自动提交

With the command SET autocommit ON/OFF autocommit can be turned on or off for the current connection. This is equivalent to setting the autocommit property in the connection profile or toggling the state of the SQL → Autocommit menu item.

如果您还有其他疑问，请告诉我们.

Please let us know if you have any additional questions.

此致

迈克

这篇关于VACUUM无法在事务块内运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！