Re: Creating temp tables inside read only transactions

From: Guillaume Lelarge
Subject: Re: Creating temp tables inside read only transactions
Date: ,
Msg-id: 1310070248.2046.6.camel@laptop
(view: Whole thread, Raw)
In response to: Creating temp tables inside read only transactions  (mike beeper)
Responses: Re: Creating temp tables inside read only transactions  (Darren Duncan)
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, )

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



pgsql-general by date:

From: "Dr. Tingrong Lu"
Date:
Subject: Re: Add Foreign Keys To Table
From: Joe Lester
Date:
Subject: Re: failed archive command