Re: [Fwd: Re: Transactions and temp tables]

Hi Heikki,

The point of using temp tables was performance. Using regular tables in
our case would hurt performance too much. Well if we cannot get a
temporary fix in 8.4, we will maintain a separate patch to get that
functionality just for temp tables that are created and dropped in the
same transaction. Hopefully we will be able to come up with a working
solution in 8.5.

Thanks for your help,
Emmanuel

> Emmanuel Cecchet wrote:
>
>> I just saw that this new patch was not considered because the previous
>> version ended being rejected.
>> Note that this version of the patch aims at supporting ONLY temp tables
>> that are created AND dropped in the same transaction. We need to be able
>> to use temp tables in transactions that are doing 2PC, but the temp
>> table lifespan does not need to cross transaction boundaries.
>>
>> Please let me know if this patch could be integrated in 8.4.
>>
>
> IMHO, this is just getting too kludgey. We came up with pretty good
> ideas on how to handle temp tables properly, by treating the same as
> non-temp tables. That should eliminate all the problems the latest patch
> did, and also the issues with sequences, and allow all access to temp
> tables, not just a limited subset. I don't think it's worthwhile to
> apply the kludge as a stopgap measure, let's do it properly in 8.5.
>
> As a workaround, you can use a regular table instead of a temporary one.
> If you create and drop the regular table in the same transaction (that's
> the same limitation that latest patch has), you won't end up with a
> bogus table in your database if the connection is dropped unexpectedly.
> If your application uses multiple connections simultaenously, you'll
> need a little bit of code in the application so that you don't try to
> create a table with the same name in all backends. You could also create
> a different schema for each connection, and do "set
> search_path='semitempschemaX, public'", so that you can use the same
> table name and still have separate tables for each connections.
>
> (sorry for the late reply)
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>

--
Emmanuel Cecchet
Aster Data Systems
Web: http://www.asterdata.com