Re: Transactions and temp tables

Emmanuel Cecchet <manu(at)frogthinker(dot)org> writes:
> Ok, so actually I don't see any different behavior between a temp table
> or a regular table. The locking happens the same way and as long as the
> commit prepared happens (potentially in another session), the lock is
> released at commit time which seems to make sense.

Right, the problem is that you can't shut down the original backend
because it'll try to drop the temp table at exit, and then block on
the lock that the prepared xact is holding. From a database management
standpoint that is unacceptable --- it means for instance that you can't
shut down the database cleanly while such a prepared transaction is
pending. The difference from a regular table is that no such automatic
action is taken at backend exit for regular tables.

The whole business of having restrictions on temp table access is
annoying; I wish we could get rid of them not add complexity to
enforcing them. The local-buffer-management end of the issue seems
readily solvable: we need only decree that PREPARE has to flush out any
dirty local buffers (and maybe discard local buffers altogether, not
sure). But I've not been able to see a decent solution to the lock
behavior.

regards, tom lane