Re: Transactions and temp tables - Mailing list pgsql-hackers

Hi Heikki,

> Emmanuel Cecchet wrote:
>> Here is the latest patch and the regression tests for the temp tables 
>> and 2PC issue.
>
> This fails:
>
> postgres=# begin;
> BEGIN
> postgres=# CREATE TEMPORARY TABLE temp1 (id int4);
> CREATE TABLE
> postgres=# PREPARE TRANSACTION 'foo';
> PREPARE TRANSACTION
> postgres=# CREATE TEMPORARY TABLE temp2 (id int4);
> ERROR:  cannot insert into frozen hashtable "accessed temp tables"
I will address that.
> I don't understand the bookkeeping of accessed and prepared temp tables
> in general. What's it for?
Right now (in 8.3) the bookkeeping prevents a transaction that has used 
a temp table to prepare commit. As you mentioned earlier 
(http://archives.postgresql.org/pgsql-hackers/2008-02/msg01277.php) we 
should be able to allow CREATE+DROP in the same transaction.
> The comments on preparedTempRel says that it keeps track of "accessed
> temporary relations that have been prepared commit but not committed
> yet". That's never going to work as a backend-private hash table,
> because there's no way to remove entries from it when the prepared
> transaction is committed or rolled back from another backend.
It does not really matter since we only allow empty temp tables at 
prepared time. And the transaction can only be prepared locally. If the 
transaction is committed or rolled back from another backend, the only 
thing that can happen is that tables that were created in the 
transaction will remain in the list. They will be ignored at the next 
prepare since the relation will not exist anymore. Once again, the 
tables remaining in the list after prepare are empty.
> What's the purpose of checking that a table is empty on prepare? I think
> I'd feel more comfortable with the approach of only accepting PREPARE
> TRANSACTIOn if the accessed temp tables have been created and destroyed
> in the same transaction, to avoid possibly surprising behavior when a
> temp table is kept locked by a prepared transaction and you try to drop
> it later in the sesssion, but the patch allows more than that. I guess
> accessing an existing ON COMMIT DELETE ROWS temp table would also be OK,
Yes, I was trying to allow also ON COMMIT DROP and ON COMMIT DELETE ROW. 
An empty temp table at PREPARE time would be similar to an ON COMMIT 
DELETE ROW table.
>  but checking that there's no visible rows in the table doesn't achieve
> that.
If the relation exist but contains no row, is it possible that the table 
is not empty? What would I need to do to ensure that the table is empty?
> I don't think you can just ignore "prepared temp relations" in
> findDependentObjects to avoid the lockup at backend exit. It's also used
> for DROP CASCADE, for example.
Do you mean that it will break the DROP CASCADE behavior in general, or 
that would break the behavior for master/child temp tables? By the way, 
does Postgres support child temp tables?

Thanks for the feedback. I will address the problem of the frozen hash 
list but let me know what you think of the other potential issues.

Emmanuel

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