Re: [Postgres-xc-general] pgbench suggestion

[Koichi S. <koi...@gm...>]

No, So far, views are only propagated to coordinators.   pgxc_*
catalogs will be found only at coordinators.   Depending upon objects,
CREATE/ALTER/DROP are propagated to datanodes as well.

View is a kind of exception.

Regards;
----------
Koichi Suzuki


2012/11/6 Tatsuo Ishii <is...@po...>:
> Thanks. Your answer cleared my question.
>
> BTW, the view created on the coordinator node should be copied to data
> nodes?  I couldn't find the view on the data nodes. If the answer is
> no, DDL executed on coordinator node is not copied to data node in
> Postgres-XC? CREATE TABLE seems to be copied to data node. Maybe
> CREATE VIEW is treated specially?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
>> Yes, the view works at coordinator locally.
>>
>> CREATE VIEW is propagated to all the coordinators so you should run it
>> only once at one of them.
>>
>> The reason I suggested EXECUTE DIRECT is user table looked involved in
>> the query.
>>
>> Regards;
>> ----------
>> Koichi Suzuki
>>
>>
>> 2012/11/6 Tatsuo Ishii <is...@po...>:
>>> Thanks. Your view works great! BTW, Should the view be executed on
>>> coordinator node? If so, I'm a little bit confused. Because you said:
>>>
>>>> Because pgxc_class and pg_class are both local to each node, oid is
>>>> also local.   To query correctly, they should be issued using EXECUTE
>>>> DIRECT statement.   Each EXECUTE DIRECT will return the result local
>>>> to each node.
>>>> 2012/11/6 Tatsuo Ishii <is...@po...>:
>>>>> I sent a query to coordinator node and got following result. Doesn't
>>>>> this means table "pgbench_accounts" distributed?
>>>>>
>>>>> test=# select * from pgxc_class as x join pg_class as c on x.pcrelid = c.oid where c.relname = 'pgbench_accounts';
>>>
>>>> pgxc_class has distribution definition of each table.    I'm using the
>>>> following views as a handy tool:
>>>>
>>>> ----
>>>> [koichi@linker:scripts]$ cat distr_view.sql
>>>> DROP VIEW IF EXISTS xc_table_distribution;
>>>>
>>>>
>>>> CREATE VIEW xc_table_distribution AS
>>>>
>>>> SELECT pg_class.relname relation,
>>>>        CASE
>>>>          WHEN pclocatortype = 'H' THEN 'Hash'
>>>>          WHEN pclocatortype = 'M' THEN 'Modulo'
>>>>          WHEN pclocatortype = 'N' THEN 'Round Robin'
>>>>          WHEN pclocatortype = 'R' THEN 'Replicate'
>>>>          ELSE 'Unknown'
>>>>        END AS distribution,
>>>>        pg_attribute.attname attname
>>>>     FROM pg_class, pgxc_class, pg_attribute
>>>>     WHERE pg_class.oid = pgxc_class.pcrelid
>>>>           and pg_class.oid = pg_attribute.attrelid
>>>>           and pgxc_class.pcattnum = pg_attribute.attnum
>>>>  UNION
>>>>
>>>> SELECT pg_class.relname relation,
>>>>        CASE
>>>>          WHEN pclocatortype = 'H' THEN 'Hash'
>>>>          WHEN pclocatortype = 'M' THEN 'Modulo'
>>>>          WHEN pclocatortype = 'N' THEN 'Round Robin'
>>>>          WHEN pclocatortype = 'R' THEN 'Replicate'
>>>>          ELSE 'Unknown'
>>>>        END AS distribution,
>>>>        '- none -' attname
>>>>    FROM pg_class, pgxc_class, pg_attribute
>>>>    WHERE pg_class.oid = pgxc_class.pcrelid
>>>>          and pg_class.oid = pg_attribute.attrelid
>>>>          and pgxc_class.pcattnum = 0
>>>>     ;
>>>>
>>>> COMMENT ON VIEW xc_table_distribution IS
>>>> 'View to show distribution/replication attribute of the given table.';
>>>>
>>>> [koichi@linker:scripts]$
>>>> ------
>>>>
>>>> Info for pgbench tables are as follows:
>>>> -----------
>>>> koichi=# select * from xc_table_distribution
>>>> koichi-# ;
>>>>      relation     | distribution | attname
>>>> ------------------+--------------+----------
>>>>  t                | Hash         | a
>>>>  pgbench_branches | Hash         | bid
>>>>  y                | Modulo       | a
>>>>  pgbench_tellers  | Hash         | bid
>>>>  pgbench_accounts | Hash         | bid
>>>>  x                | Round Robin  | - none -
>>>>  pgbench_history  | Hash         | bid
>>>>  s                | Replicate    | - none -
>>>> (8 rows)
>>>>
>>>> koichi=#
>>>> ------
>>>>
>>>> Hope it helps.
>>>>
>>>> Regards;
>>>> ----------
>>>> Koichi Suzuki
>>>>
>>>>
>>>> 2012/11/6 Tatsuo Ishii <is...@po...>:
>>>>>> One suggestion.   Because set bid value is relatively small, it may be
>>>>>> better to distribute tables using MODULO, not HASH.
>>>>>
>>>>> What is the distribution method when pgbench -k is used?
>>>>> --
>>>>> Tatsuo Ishii
>>>>> SRA OSS, Inc. Japan
>>>>> English: http://www.sraoss.co.jp/index_en.php
>>>>> Japanese: http://www.sraoss.co.jp