PostgreSQL，ODBC和临时表 [英] PostgreSQL, ODBC and temp table

问题描述

您能否告诉我为什么该查询在pgAdmin中有效，但不适用于使用ODBC的软件:

Could you tell me why this query works in pgAdmin, but doesn't with software using ODBC:

CREATE TEMP TABLE temp296 WITH (OIDS) ON COMMIT DROP AS
SELECT age_group AS a,male AS m,mode AS t,AVG(speed) AS speed
FROM person JOIN info ON person.ppid=info.ppid
WHERE info.mode=2
GROUP BY age_group,male,mode;

SELECT age_group,male,mode,
CASE 
WHEN age_group=1 AND male=0 THEN (info_dist_km/(SELECT avg_speed FROM temp296 WHERE a=1 AND m=0))*60
ELSE 0
END AS info_durn_min
FROM person JOIN info ON person.ppid=info.ppid
WHERE info.mode IN (7) AND info.info_dist_km>2;

我收到"42P01:错误:关系"temp296"不存在".

I got "42P01: ERROR: relation "temp296" does not exist".

我也尝试过"BEGIN; [...] COMMIT;". -"HY010:光标已打开".

I also have tried with "BEGIN; [...] COMMIT;" - "HY010:The cursor is open".

PostgreSQL 9.0.10，由Visual C ++ build 1500编译，64位 psqlODBC 09.01.0200 Windows 7 x64

PostgreSQL 9.0.10, compiled by Visual C++ build 1500, 64-bit psqlODBC 09.01.0200 Windows 7 x64

推荐答案

我认为它对您不起作用的原因是因为默认情况下ODBC在自动提交模式下起作用.如果您按顺序执行语句，则第一条语句

I think that the reason why it did not work for you because by default ODBC works in autocommit mode. If you executed your statements serially, the very first statement

CREATE TEMP TABLE temp296 ON COMMIT DROP ... ;

完成后必须自动提交，因此删除了临时表.

must have autocommitted after finishing, and thus dropped your temp table.

不幸的是，ODBC不直接支持使用BEGIN TRANSACTION; ... COMMIT;之类的语句来处理事务.

Unfortunately, ODBC does not support directly using statements like BEGIN TRANSACTION; ... COMMIT; to handle transactions.

相反，您可以使用 SQLSetConnectAttr 函数来禁用自动提交这个:

Instead, you can disable auto-commit using SQLSetConnectAttr function like this:

SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);

但是，执行此操作之后，您必须记住使用 SQLEndTran 像这样:

But, after you do that, you must remember to commit any change by using SQLEndTran like this:

SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);

虽然使用WITH方法作为一种变通办法，但值得注意的是，适当地使用事务比在自动提交模式下运行要快.

While WITH approach has worked for you as a workaround, it is worth noting that using transactions appropriately is faster than running in auto-commit mode.

例如，如果您需要在表中插入许多行(成千上万)，则使用事务处理的速度可能比自动提交快数百倍.

For example, if you need to insert many rows into the table (thousands or millions), using transactions can be hundreds and thousand times faster than autocommit.

这篇关于PostgreSQL，ODBC和临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！