One of the problems regarding the use of temporary tables in prepared transactions
is that such transaction will hold locks for a temporary table after being prepared.
That locks will prevent the backend from exiting since it will fail to acquire lock
needed to delete temp table during exit. Also, re-acquiring such lock after server
restart seems like an ill-defined operation.
...
Any thoughts?
It occurs to me that there is no problem to solve here.
When we PREPARE, it is because we expect to COMMIT or ABORT soon afterwards.
If we are using an external transaction manager, the session is idle while we wait for the manager to decide whether to commit or abort. Or the session is disconnected or server is crashes. Either way, nothing happens between PREPARE and resolution. So there is no need at all for locking of temporary tables after the prepare.
The ONLY case where this matters is if someone does a PREPARE and then starts doing other work on the session. Which makes no sense in the normal workflow of a session. I'm sure there are tests that do that, but those tests are unrepresentative of sensible usage.
If you were using session temporary tables while using a transaction mode pool then you're already going to have problems, so that aspect is a non-issue.
So I think we should ban this by definition. Say that we expect that you won't do any work on the session until COMMIT/ABORT. That means we can then drop locks on sesion temporary tables and drop on-commit temp tables when we hit the prepare, not try and hold them for later.
A patch is needed to implement the above, but I think we can forget the current patch as not needed.