Postgresql – Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction)

ctelockingpostgresqltemporary-tables

I am getting 

"ERROR: out of shared memory"

presumably because my plpgsql function drops/creates a lot of temporary tables.

Searching for a solution to the issue, I found this answer that says:

So I guess one answer is that you will need enough locks to cope with
all these tables being added/dropped throughout the transaction.
Alternatively, you could try to reuse the temp tables between queries,
simply truncate them to remove all the temp data

In order to avoid having too many looks, I am thinking about rewriting as many temporary table code using Common Table Expressions (CTEs).

My questions are:

Do the table expressions in CTEs use locks that count towards max_locks_per_transaction?

Will replacing temp tables with CTEs solve the max_locks_per_transaction problem?

Related:

https://stackoverflow.com/questions/16490664/

https://stackoverflow.com/questions/3132533

Best Answer

A CTE is no table and won't need an entry in the lock table.

It is probably a good idea not to create and drop temporary tables all the time. Apart from the problem you are experiencing, it can lead to a bloated pg_attribute catalog table (all those added and deleted columns).

Mind that a CTE is only for the duration of a single table.

Related Question