Re: Transactions and temp tables

Emmanuel Cecchet wrote:
> I still quite did not get what the big deal was if an ON COMMIT DELETE 
> ROWS temp table was created inside a transaction.

In case the transaction that created a temp table rolls back, the table 
needs to be removed. Removing a temporary table belonging to another 
backend is problematic; the local buffers in the original backend need 
to be dropped, as well as the entry in the on commit actions list.

> Why the new checks you are doing in lock.c would not work with dropped 
> temp tables? Could it be possible to drop the lock as soon as the temp 
> table is dropped inside a transaction?

If you release the lock early on a table that you drop, another 
transactions would be free to access the table, even though it's about 
to be dropped.

> I will try to find more time to review the patch tonight.

Thanks!

Thinking about this whole thing yet more, I wonder if we could have a 
more holistic approach and make temporary tables work just like regular 
ones. The problems we've identified this far are:

1. If the prepared transaction keeps the temp table locked, the backend 
can't exit, because the shutdown hook tries to drop all temp tables.

2. When a prepared transaction that has deleted a temporary table 
commits (or one that created one aborts), we need to drop all the local 
buffers from the original backend's private buffer cache.

3. When a prepared transaction that has deleted a temporary table 
commits (or one that created one aborts), we need to remove the 
on-commit entry from the original backend's private list.

Is there more? I think we can solve all the above problems:

1. Modify RemoveTempRelations so that it doesn't block if it can't 
immediately acquire lock on the to-be-removed object. That way the 
original backend can exit even if a prepared transaction is holding a 
lock on a temporary object.

To avoid conflict with a new backend that's assigned the same backendid, 
divorce the temporary namespace naming from backendid so that the 
temporary namespace name stays reserved for the prepared transaction.

2. Flush and drop all local buffers on temporary tables that have been 
created or dropped in the transaction at PREPARE TRANSACTION already.

3. Add on-commit field to pg_class, and only keep a list of temporary 
tables that have been accessed in the current transaction in 
backend-private memory.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com