How to use PostgreSQL temporary tables with Ecto?

As you know, temporary tables are deleted at the end of session or transaction. Since a connection (maintained by Postgrex) is a session and connections are reused, you would be best served by ensuring you wrap the temporary table lifecycle in a transaction so the table is deleted at the end of the transaction.

Hard to say the best strategy for you use case on the available data. However using permanent tables transiently means you need to manage unique naming and cleanup in the case of transaction rollback or server crashes.

As you know, temporary tables are deleted at the end of session or transaction. Since a connection (maintained by Postgrex) is a session and connections are reused, you would be best served by ensuring you wrap the temporary table lifecycle in a transaction so the table is deleted at the end of the transaction.

Thanks – I did know the first part, but I hadn’t seen any explicit mention of connections being (equivalent to) sessions. That’s one thing I was worried about.

One potential workaround, to avoid using a transaction, seems to be to use a dynamic repo:

• Replicas and dynamic repositories — Ecto v3.5.6

The example has a :pool_size setting of 1, which I’m guessing indicates a ‘pool’ with a single connection.

But a transaction should be fine too for my case.

Hard to say the best strategy for you use case on the available data. However using permanent tables transiently means you need to manage unique naming and cleanup in the case of transaction rollback or server crashes.

Of course – but that’s probably easily handled for my use case, i.e. a daily background job.

One thing to remember is that every statement is always wrapped in a transaction. A single SQL statement is wrapped in an implicit transaction. And of course developers and users can declare explicit transactions.

I don’t think transactions are anything to be avoided or worked around since they underpin the ACID properties of a database.

Thanks – those are all good points to make explicit for anyone else that finds this post.

I don’t think transactions are anything to be avoided or worked around since they underpin the ACID properties of a database.

I’ve found it generally hard to reason about ‘transaction semantics’ in systems that weren’t designed with it in mind. After the fact, it’s not usually clear what semantics are ‘correct’ and considerations ‘devolve’ to cost-benefit analysis of the relevant tradeoffs (including development/admin time). Or at least that’s been true of most of my experience!

Interestingly, Microsoft SQL Server seems to explicitly cleanup ‘session data’, e.g. temporary tables, when a database connection in a pool is re-used:

• SQL Server Temp Tables and Connection Pooling - Stack Overflow

How have you used PostgreSQL temporary tables with Ecto? Strictly in transactions? Or (also) with DROP ON COMMIT?

That’s an interesting read, thanks for the link (about SQL Server). As I read it, I think the the SQLServer connection pool is calling a stored procedure to clear temp tables at either connection checkin or checkout (I didn’t see clearly which).

In Elixir, you can implement very similar (same?) semantics with

Repo.checkout fn ->
  # Do stuff
  Repo.query!(“DISCARD TEMP”)
end

Interestingly, this post from @josevalim notes that using Repo.transaction/1 instead of Repo.checkout/1 will likely have better performance.

I don’t have a common use case for temp tables but I found this post on stackoverflow that gives good guidance for using CREATE TEMPORARY TABLE some_table ON COMMIT DROP AS SELECT ..... which also appears to suggest a transaction is required with this form to get the expected results.

I just wonder - what do you need temporary tables for?

Thanks! I hadn’t seen (or remembered) Repo.checkout/1. That looks like what I’d want if I couldn’t use a transaction.

It turned out that I didn’t need them; or at least not yet!

There are some alternatives, like PostgreSQL materialized views, but I was testing temporary tables and those alternatives because of ‘bad’ performance for a big recurring query.

I was curious about how others were using them with Ecto and PostgreSQL because I hadn’t found any detailed examples, e.g. of ‘checking-out’ a connection, or otherwise isolating a temporary table from other sessions/connections in the same pool.

If that was in recurring query then temporary table should not help much, and if that helped anyway (because was probably used more than once in query) then you can achieve exactly the same with CTE.

I don’t think CTEs are generally equivalent to temporary tables, even just performance-wise. One difference being that you can’t index the CTE data. Another being that a CTE can only be used with one query (tho I’d expect PostgreSQL would likely re-use its ‘query plan’ if the same CTE was used for multiple queries).

I really like PostgreSQL’s materialized views as an alternative to temporary tables too. Like tables, they can be indexed. And there’s nice concise syntax for ‘refreshing’ one of those views. The only downside is that it persists, tho that seems like a relatively small cost, especially if it’s going to be used regularly anyways.