From: PARIS Nicolas <niparisco@gmail.com>
To: Thom Brown <thom@linux.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: PGSQL 9.3 - Materialized View - multithreading
Date: Fri, 04 Apr 2014 21:49:12 +0200
Message-ID: <533F0CB8.40001@gmail.com> (raw)
In-Reply-To: <CAA-aLv7S5XtJX-k4jYP1iMQVh3d0+qS7T4EtwZJ8D3zU3pSpiA@mail.gmail.com>
References: <CA+ssMOS8kjy+YKMV+hR5VTuLSa-Fd-ADHVgsNBaEaXS44Azwiw@mail.gmail.com>
<CAA-aLv7S5XtJX-k4jYP1iMQVh3d0+qS7T4EtwZJ8D3zU3pSpiA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: niparisco@gmail.com, thom@linux.com
Subject: Re: PGSQL 9.3 - Materialized View - multithreading
In-Reply-To: <533F0CB8.40001@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox