Re: Creating temp tables inside read only transactions

From: Darren Duncan
Subject: Re: Creating temp tables inside read only transactions
Date: ,
Msg-id: 4E167FF2.2060907@darrenduncan.net
(view: Whole thread, Raw)
In response to: Re: Creating temp tables inside read only transactions  (Guillaume Lelarge)
Responses: Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis)
List: pgsql-general

Tree view

Creating temp tables inside read only transactions  (mike beeper, )
 Re: Creating temp tables inside read only transactions  (Guillaume Lelarge, )
  Re: Creating temp tables inside read only transactions  (Darren Duncan, )
   Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
    Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
     Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
     Re: [HACKERS] Creating temp tables inside read only transactions  (Gavin Flower, )
     Re: [HACKERS] Creating temp tables inside read only transactions  (Robert Haas, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Tom Lane, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
        Re: [HACKERS] Creating temp tables inside read only transactions  (Craig Ringer, )
        Re: [HACKERS] Creating temp tables inside read only transactions  (Jeff Davis, )
         Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Craig Ringer, )
      Re: [HACKERS] Creating temp tables inside read only transactions  (Michael Nolan, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (John R Pierce, )
       Re: [HACKERS] Creating temp tables inside read only transactions  (Christopher Browne, )
        Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
         Re: [HACKERS] Creating temp tables inside read only transactions  ("David Johnston", )
         Re: [HACKERS] Creating temp tables inside read only transactions  (Florian Pflug, )
          Re: [HACKERS] Creating temp tables inside read only transactions  (Christopher Browne, )
           Re: [HACKERS] Creating temp tables inside read only transactions  ("David Johnston", )
            Re: [HACKERS] Creating temp tables inside read only transactions  (Darren Duncan, )
            Re: [HACKERS] Creating temp tables inside read only transactions  (Chris Travers, )
             Re: [HACKERS] Creating temp tables inside read only transactions  (Alban Hertroys, )
            Re: [HACKERS] Creating temp tables inside read only transactions  (Florian Pflug, )
     Re: [HACKERS] Creating temp tables inside read only transactions  (Florian Pflug, )
 Re: [HACKERS] Creating temp tables inside read only transactions  (mike beeper, )

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


pgsql-general by date:

From: Vincent de Phily
Date:
Subject: Re: [PERFORM] DELETE taking too much memory
From: David Hartveld
Date:
Subject: Re: Streaming replication on 9.1-beta2 after pg_restore is very slow