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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
