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

Heikki Linnakangas wrote:
>> 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.
> I think you'll want to check explicitly that the table is defined with 
> ON COMMIT DELETE ROWS, instead of checking that it's empty.
Where can I find the field containing the CREATE options for the temp table?
> Yeah, thanks to MVCC, it's possible that the table looks empty to the 
> transaction being prepared, using SnapshotNow, but there's some tuples 
> that are still visible to other transactions. For example:
>
> CREATE TEMPORARY TABLE foo (id int4);
> INSERT INTO foo VALUES (1);
> begin;
> DELETE FROM foo;
> PREPARE TRANSACTION 'foo'; -- doesn't error, because the table is 
> empty, according to SnapshotNow
> SELECT * FROM foo; -- Still shows the one row, because the deleting 
> transaction hasn't committed yet.
Is that a problem? If your transaction isolation level is not 
serializable the SELECT will not block and return the current snapshot. From the transaction standpoint, it is fine
thatthe transaction can 
 
prepare or am I missing something?
Actually, I did a test and if the temp table is created with 'on commit 
delete rows' option, the select blocks until the transaction is 
committed. This seems a normal behavior to me.
>>> 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?
>
> For temp tables, I suppose.
I confirm that doing a drop cascade on a master temp table after a 
prepared transaction committed from another backend will not drop the 
children for now.
>
> The hack in findDependentObjects still isn't enough, anyway. If you 
> have a prepared transaction that created a temp table, the database 
> doesn't shut down:
>
> $ bin/pg_ctl -D data start
> server starting
> $ LOG:  database system was shut down at 2008-11-04 10:27:27 EST
> LOG:  autovacuum launcher started
> LOG:  database system is ready to accept connections
>
> $ bin/psql postgres -c "begin; CREATE TEMPORARY TABLE temp (id 
> integer); PREPARE TRANSACTION 'foo';"
> PREPARE TRANSACTION
> hlinnaka@heikkilaptop:~/pgsql.fsmfork$ bin/pg_ctl -D data stop
> LOG:  received smart shutdown request
> LOG:  autovacuum launcher shutting down
> waiting for server to shut 
> down............................................................... 
> failed
> pg_ctl: server does not shut down
Interesting case, if the table is created but not accessed it is not 
enlisted and then the shutdown does not catch this dependency. The table 
should be enlisted at CREATE time as well.

The bookkeeping of prepared commit tables is just for the shutdown case 
right now. If you think it is a bad idea altogether to have session temp 
tables (even with delete rows on commit) that can cross commit 
boundaries, then we can remove that second bookkeeping and only allow 
temp tables that have been created withing the scope of the transaction.

I fixed the hash_freeze problem but this drop cascade on temp table 
seems to be an issue (if anyone uses that feature).

Emmanuel

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