Re: Creating temp tables inside read only transactions

Guillaume Lelarge wrote [on pgsql-general]:
> On Thu, 2011-07-07 at 16:01 +0000, mike beeper wrote [on pgsql-general]:
>> I have a function that creates a temp table, populate it with results
>> during intermediate processing, and reads from it at the end.  When
>> the transaction is marked as read only, it does not allow creation of
>> temp table, even though there are no permanent writes to the db.  Are
>> there any workarounds? The following block errors out.
>>
>> SET TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
>> create temp table test(test int);
>
> When you create a temporary table, PostgreSQL needs to add rows in
> pg_class, pg_attribute, and probably other system catalogs. So there are
> writes, which aren't possible in a read-only transaction. Hence the
> error. And no, there is no workaround.

That sounds like a deficiency to overcome.

It should be possible for those system catalogs to be virtual, defined like
union views over similar immutable tables for the read-only database plus
mutable in-memory ones for the temporary tables.

Are there any plans in the works to do this?

On the other hand, if one can have lexical-scope tables (table-typed routine
variables), and I know Pg 8.4+ has named subqueries which handle a lot of cases
where temp tables would otherwise be used, I would certainly expect those to
work when you're dealing with a readonly database.

-- Darren Duncan