Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51428=pgsql-performance-ar@postgresql.org>)
 id 1WWA7O-0005Rc-Pv
 for pgsql-performance@arkaria.postgresql.org; Fri, 04 Apr 2014 19:49:39 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51428=pgsql-performance-ar@postgresql.org>)
 id 1WWA7O-0003sJ-0M
 for pgsql-performance@arkaria.postgresql.org; Fri, 04 Apr 2014 19:49:38 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <niparisco@gmail.com>) id 1WWA7M-0003sC-TR
 for pgsql-performance@postgresql.org; Fri, 04 Apr 2014 19:49:36 +0000
Received: from mail-wi0-x22e.google.com ([2a00:1450:400c:c05::22e])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <niparisco@gmail.com>) id 1WWA7J-0004TJ-G1
 for pgsql-performance@postgresql.org; Fri, 04 Apr 2014 19:49:36 +0000
Received: by mail-wi0-f174.google.com with SMTP id d1so1942429wiv.13
 for <pgsql-performance@postgresql.org>; Fri, 04 Apr 2014 12:49:32 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:cc:subject
 :references:in-reply-to:content-type:content-transfer-encoding;
 bh=PCk4OX0Qk0280KIng9qTn1nlw12MJv/bsDTx9VkmL9A=;
 b=v3Il3WdzIUKkSxDypV5RRbQsI7CMo03rSgZ3v3sK3QAm6XkFbImxR7kJxwl7qpzR8x
 /PSLmMJ6UEvBpqiqVWdos1KcpSBhg+Vcvw0NZFv/tOGbYIzhVRola04Ec6twxJPfCEZ+
 K5Vafdy4kxSA4ZLkvLczCPJ55BAgt1hQnr+WPgbjvWF4ViZxq2tvXAu0JbZGYvpPDbl7
 4eIB1eqR7s1QHR3LYSAFMjGyMKTeEFQJkGnCA/1WyVEDfPSnc6YF3Vh0lXCEDJbZy6U1
 dQRgPudNM0nV7qWS0G+HaTmCPkt/Wp0E+PHcbCNSGa1ZPgbfOIH7vK4x058atFRKeADS
 8X/w==
X-Received: by 10.194.186.140 with SMTP id fk12mr21808780wjc.47.1396640972563;
 Fri, 04 Apr 2014 12:49:32 -0700 (PDT)
Received: from [192.168.1.30] (96.84.15.109.rev.sfr.net. [109.15.84.96])
 by mx.google.com with ESMTPSA id gi8sm222548wib.8.2014.04.04.12.49.30
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Fri, 04 Apr 2014 12:49:31 -0700 (PDT)
Message-ID: <533F0CB8.40001@gmail.com>
Date: Fri, 04 Apr 2014 21:49:12 +0200
From: PARIS Nicolas <niparisco@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.2.0
MIME-Version: 1.0
To: Thom Brown <thom@linux.com>
CC: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: PGSQL 9.3 - Materialized View - multithreading
References: 
 <CA+ssMOS8kjy+YKMV+hR5VTuLSa-Fd-ADHVgsNBaEaXS44Azwiw@mail.gmail.com>
 <CAA-aLv7S5XtJX-k4jYP1iMQVh3d0+qS7T4EtwZJ8D3zU3pSpiA@mail.gmail.com>
In-Reply-To: 
 <CAA-aLv7S5XtJX-k4jYP1iMQVh3d0+qS7T4EtwZJ8D3zU3pSpiA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Thanks,

"The only thing that immediately comes to mind would be running a
 rather hacky DO function in 4 separate sessions:"
You mean 8 sessions I guess.

8 separate sessions ?
Have you any idea how to manage sessions ? Is it possible to create
separate session internaly ?
Do I have to make 8 external connection to database, to get 8 process.
It would be great if I could manage session internaly, in a pl/sql by
example.


Le 04/04/2014 18:54, Thom Brown a écrit :
> On 4 April 2014 17:29, Nicolas Paris <niparisco@gmail.com> wrote:
>> Hello,
>>
>> My question is about multiprocess and materialized View.
>> http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html
>> I (will) have something like 3600 materialised views, and I would like to
>> know the way to refresh them in a multithread way
>> (anderstand 8 cpu cores -> 8 refresh process  in the same time)
> 
> The only thing that immediately comes to mind would be running a
> rather hacky DO function in 4 separate sessions:
> 
> DO $$
> DECLARE
>   session CONSTANT BIGINT := 0;
>   rec RECORD;
> BEGIN
>   FOR rec IN SELECT quote_ident(nspname) || '.' ||
> quote_ident(relname) AS mv FROM pg_class c INNER JOIN pg_namespace n
> ON c.relnamespace = n.oid WHERE relkind = 'm' AND c.oid::bigint % 8 =
> session LOOP
>     RAISE NOTICE 'Refreshing materialized view: %', rec.mv;
>     EXECUTE 'REFRESH MATERIALIZED VIEW ' || rec.mv || ';';
>   END LOOP;
> END$$ language plpgsql;
> 
> Where you would set session to 0 for the first session, 1 for the
> next, 2 for the next and 3 for the next, and so on until you reach 7
> for the last.  These would each be run in a separate parallel session,
> although someone may come up with a better solution.
> 



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance