Skip site navigation (1) Skip section navigation (2)

Re: Transactions and temp tables

From: Emmanuel Cecchet <manu(at)frogthinker(dot)org>
To: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Transactions and temp tables
Date: 2008-11-07 23:19:48
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers

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.
I have also added a compilation of the tests I have even if some are not 
really relevant anymore without the support for empty temp tables but we 
will probably reuse them later.

Thanks in advance for the feedback,

Emmanuel Cecchet wrote:
> 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 
>> 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);
>> begin;
>> 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 that the 
> 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';"
>> hlinnaka(at)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
email: manu(at)frogthinker(dot)org
Skype: emmanuel_cecchet

Attachment: patch-2pc-temp-table-8.4v3.txt
Description: text/plain (7.4 KB)
Attachment: patch-2pc-regression-test-suite-8.4v3.txt
Description: text/plain (31.0 KB)

In response to


pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2008-11-07 23:21:48
Subject: Re: Updated backslash consistency patch
Previous:From: Tom LaneDate: 2008-11-07 23:18:45
Subject: Re: [RRR] Tests citext casts

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group