Re: Transactions and temp tables

Heikki Linnakangas wrote:
> 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.
Is that going to cause any problem with DROP CASCADE operations or
trying to later drop a child table if the parent table is locked? I did
hit that issue when I tried to modify RemoveTempRelations but I was
probably not very smart at it.
> 2. Flush and drop all local buffers on temporary tables that have been
> created or dropped in the transaction at PREPARE TRANSACTION already.
Would there be any issue if someone was trying to use a READ_UNCOMMITTED
isolation level to access the temp table data?
> 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.
Yes, this seems doable. We will certainly have to keep a list per
transaction id in case multiple prepared but uncommitted transactions
have accessed different temp table on that backend.

Have you already started to code some of this?
I am looking at adding new tests to check all cases including all types
of temp tables (normal/on delete/on drop, created inside or outside a
transaction, prepare/postmaster stop/commit/rollback,
inherit/index/vaccuum). That should get all the use cases covered.

Let me know what you think.
Thanks,
Emmanuel

--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu(at)frogthinker(dot)org
Skype: emmanuel_cecchet