Re: Transactions and temp tables

Hi,

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

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
>>> 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 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';"
>> PREPARE TRANSACTION
>> 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
--
Web: http://www.frogthinker.org
email: manu(at)frogthinker(dot)org
Skype: emmanuel_cecchet