From: Emmanuel Cecchet <manu@frogthinker.org>
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Transactions and temp tables
Date: Fri, 21 Nov 2008 10:46:53 -0500
Message-ID: <4926D7ED.9060103@frogthinker.org> (raw)
In-Reply-To: <492543D5.9050904@enterprisedb.com>
References: <48EA7B12.6050906@frogthinker.org>
<48EB0551.50205@enterprisedb.com>
<48EB95B7.10604@frogthinker.org>
<48EC7EB5.5020001@enterprisedb.com>
<48ECAA8F.3070306@frogthinker.org>
<28137.1223472191@sss.pgh.pa.us>
<48ECBBC2.6060003@frogthinker.org>
<29132.1223475486@sss.pgh.pa.us>
<48EFA13C.2060407@frogthinker.org>
<490F804A.60108@enterprisedb.com>
<490FB4B3.908@frogthinker.org>
<49106B10.2090407@enterprisedb.com>
<4910DFC0.3080301@frogthinker.org>
<4914CD14.2060608@frogthinker.org>
<4921C42A.3030600@enterprisedb.com>
<492221A3.1060608@frogthinker.org>
<4922A798.7050709@enterprisedb.com>
<49232993.1050801@enterprisedb.com>
<49243376.2000404@frogthinker.org>
<492543D5.9050904@enterprisedb.com>
Heikki Linnakangas wrote:
> Emmanuel Cecchet wrote:
>> I still quite did not get what the big deal was if an ON COMMIT
>> DELETE ROWS temp table was created inside a transaction.
>
> In case the transaction that created a temp table rolls back, the
> table needs to be removed. Removing a temporary table belonging to
> another backend is problematic; the local buffers in the original
> backend need to be dropped, as well as the entry in the on commit
> actions list.
>
>> Why the new checks you are doing in lock.c would not work with
>> dropped temp tables? Could it be possible to drop the lock as soon as
>> the temp table is dropped inside a transaction?
>
> If you release the lock early on a table that you drop, another
> transactions would be free to access the table, even though it's about
> to be dropped.
>
>> I will try to find more time to review the patch tonight.
>
> Thanks!
>
> Thinking about this whole thing yet more, I wonder if we could have a
> more holistic approach and make temporary tables work just like
> regular ones. The problems we've identified this far are:
>
> 1. If the prepared transaction keeps the temp table locked, the
> backend can't exit, because the shutdown hook tries to drop all temp
> tables.
>
> 2. When a prepared transaction that has deleted a temporary table
> commits (or one that created one aborts), we need to drop all the
> local buffers from the original backend's private buffer cache.
>
> 3. When a prepared transaction that has deleted a temporary table
> commits (or one that created one aborts), we need to remove the
> on-commit entry from the original backend's private list.
>
> Is there more? I think we can solve all the above problems:
>
> 1. Modify RemoveTempRelations so that it doesn't block if it can't
> immediately acquire lock on the to-be-removed object. That way the
> original backend can exit even if a prepared transaction is holding a
> lock on a temporary object.
>
> To avoid conflict with a new backend that's assigned the same
> backendid, divorce the temporary namespace naming from backendid so
> that the temporary namespace name stays reserved for the prepared
> transaction.
Is that going to cause any problem with DROP CASCADE operations or
trying to later drop a child table if the parent table is locked? I did
hit that issue when I tried to modify RemoveTempRelations but I was
probably not very smart at it.
> 2. Flush and drop all local buffers on temporary tables that have been
> created or dropped in the transaction at PREPARE TRANSACTION already.
Would there be any issue if someone was trying to use a READ_UNCOMMITTED
isolation level to access the temp table data?
> 3. Add on-commit field to pg_class, and only keep a list of temporary
> tables that have been accessed in the current transaction in
> backend-private memory.
Yes, this seems doable. We will certainly have to keep a list per
transaction id in case multiple prepared but uncommitted transactions
have accessed different temp table on that backend.
Have you already started to code some of this?
I am looking at adding new tests to check all cases including all types
of temp tables (normal/on delete/on drop, created inside or outside a
transaction, prepare/postmaster stop/commit/rollback,
inherit/index/vaccuum). That should get all the use cases covered.
Let me know what you think.
Thanks,
Emmanuel
--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu@frogthinker.org
Skype: emmanuel_cecchet
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: manu@frogthinker.org, heikki.linnakangas@enterprisedb.com, tgl@sss.pgh.pa.us
Subject: Re: Transactions and temp tables
In-Reply-To: <4926D7ED.9060103@frogthinker.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox