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