Re: Creating temp tables inside read only transactions

On Thu, 2011-07-07 at 16:01 +0000, mike beeper wrote:
> 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.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com