PostgreSQL, ODBC and temp table

Question

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;

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

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

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

Answer 1

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.

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

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

SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);

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

SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);

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.

Answer 2

It is not uncommon for temporary tables to not be available via SQLPrepare/SQLExecute in ODBC i.e., on prepared statements e.g., MS SQL Server is like this. The solution is usually to use SQLExecDirect.