Re: Transactions and temp tables

Emmanuel Cecchet wrote:
> There is a problem with temp tables with on delete rows that are created
> inside a transaction.
> Take the 2pc_on_delete_rows_transaction.sql test case and change the
> creation statement, instead of
> create temp table foo(x int) on commit delete rows;
> try
> create temp table foo(x serial primary key) on commit delete rows;
>
> The test will fail. It looks like the onCommit field is not properly
> updated when serial or primary key is used in that context. I did not
> figure out why.

A serial column uses a sequence behind the scenes.

Hmm. Seems like we would need to treat sequences and indexes the same as
tables with ON COMMIT DELETE ROWS, i.e release the locks early and don't
error out.

All in all, this is getting pretty messy. My patch felt a bit hackish to
begin with, and having to add special cases for sequences and indexes
would make it even more so. And what about temporary views? I'm starting
to feel that instead of special-casing temp relations, we need to move
into the opposite direction and make temp relations more like regular
relations. Unfortunately, that's not going to happen in the 8.4
timeframe :-(. Let's try the other approach in 8.5.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com