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@frogthinker.org
Skype: emmanuel_cecchet