Re: Transactions and temp tables

Emmanuel Cecchet wrote:
>> Emmanuel Cecchet wrote:
>>> As I have not found yet an elegant solution to deal with the DROP
>>> CASCADE issue, here is a simpler patch that handles temp tables that
>>> are dropped at commit time. This is a good first step and we will try
>>> to elaborate further to support ON COMMIT DELETE ROWS.
>>
>> The problem with stopping the postmaster seems to still be there..
>>
>> All the problems are centered around locking. We need to address that
>> and decide what is sane locking behavior wrt. temp tables and 2PC.
>>
>> First, there's the case where a temp table is created and dropped in
>> the same transaction. It seems perfectly sane to me to simply drop all
>> locks on the dropped table at PREPARE TRANSACTION. Does anyone see a
>> problem with that? If not, we might as well do that for all tables,
>> not just temporary ones. It seems just as safe for non-temporary tables.
> This seems good to me. Any access to the table after PREPARE TRANSACTION
> but before COMMIT on that backend would return a relation not found
> which is what we expect. For a regular table, I don't know if that
> makes a difference if the prepared transaction rollbacks?

I don't think there's any difference with temp tables and regular ones
from locking point of view.

>> Secondly, there's the case of accessing a ON COMMIT DELETE ROWS table.
>> There too, could we simply drop the locks at PREPARE TRANSACTION? I
>> can't immediately see anything wrong with that.
> As there is no data anyway, I don't think the locks are going to change
> anything. But in the most recent stripped-down version of the patch, on
> delete rows is no more supported, I only allow on commit drop. I did not
> find the flag to see if a temp table was created with the on delete rows
> option.

Hmm. I think we can use the on_commits list in tablecmds.c for that.

> Do you want me to look at the locking code or will you have time to do
> it? Hints will be welcome if you want me to do it.

I can give it a shot for change. Attached is a patch that allows the ON
COMMIT DELETE ROWS case. The beef of the patch is:

- An entry is made into the on_commits list in tablecmds.c for all temp
tables, even if there's no ON COMMIT action
- There's a new function, check_prepare_safe_temp_table(Oid relid) in
tablecmds.c, that uses the on_commits list to determine if the access to
the given relation is "PREPARE-safe". That is, it's not a temp table, or
it's an access to an ON COMMIT DELETE ROWS temp table and the temp table
wasn't created or dropped in the same transaction.
- MyXactMadeTempRelUpdate variable is gone. The check is driven from the
lock manager again, like it was in 8.1, by calling the new
check_prepare_sage_temp_table function for all relation locks in
AtPrepare_Locks().
- changed the on_commits linked list in tablecmds.c into a hash table
for performance

Somehow this feels pretty baroque, though. Perhaps a better approach
would be to add a new AtPrepare_OnCommitActions function to tablecmds.c,
that gets called before AtPrepare_Locks. It would scan through the
on_commits list, and release all locks for the "PREPARE-safe" temp
tables, and throw the error if necessary. I'll try that next.

BTW, there's a very relevant thread here:

http://archives.postgresql.org/pgsql-hackers/2008-03/msg00063.php

if you haven't read it already.

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