/
Avoiding Temp Tables with Native SQL Execution

Avoiding Temp Tables with Native SQL Execution

Owned by Former user (Deleted)
Last updated: Nov 16, 2017

It's not possible to use temporary tables in queries used with the sql:query(q) or sql:execute(q) built-in functions. This includes using these BIFs to call on SQL functions that use temp tables. Doing so will result in an error such as:

cannot PREPARE a transaction that has operated on temporary tables

From https://www.postgresql.org/docs/9.3/static/sql-prepare-transaction.html :

It is not currently allowed to PREPARE a transaction that has executed any operations involving temporary tables, created any cursors WITH HOLD, or executed LISTEN, UNLISTEN, or NOTIFY. Those features are too tightly tied to the current session to be useful in a transaction to be prepared.

Furthermore, using temp tables in any scenario is discouraged because of the potential amount of memory consumed.

 

Related content