Re: [Postgres-xc-general] pgbench suggestion

[Tatsuo I. <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