Re: [Postgres-xc-bugs] column w/ UNIQUE constraint was not chosen as the distribution key

[Michael P. <mic...@gm...>]

Even if you do not provide precise information, you need to know that this
is not a bug.
When using CREATE TABLE AS and select data from a table, you do not copy
the constraints of the table being scanned but only the data,
so it makes absolutely no sense to enforce the hash key on the column uni1
for the table uni_col2.
Also, in the case of the 2nd table created, you do not specify any
distribution information with CREATE TABLE AS, so the distribution chosen
is hash with the first column whose type can be use to calculate the hash
keys, so in this case it is no_uni1, which is the first column of table
uni_col2.

However, you can specify a distribute type for the newly-created table as
specified by the docs if you really want to enforce the hash to uni1 for
the 2nd table:
http://postgres-xc.sourceforge.net/docs/1_0/sql-createtableas.html

On Tue, Nov 13, 2012 at 4:45 PM, Hitoshi HEMMI
<hem...@la...>wrote:

> Hi, list
>
>
> ============================================================================
> POSTGRES-XC BUG REPORT TEMPLATE
>
> ============================================================================
>
> Your name : Hitoshi Hemmi
> Your email address : hem...@la...
>
>
> System Configuration:
> ---------------------
> Architecture : x86_64
>
> Operating Systems : CentOS release 6.2 x86_64
>
> Postgres-XC version : Postgres-XC 1.0.1dev
>
> Description of problems:
> ==============================================
>
> 1) Create table "uni_col" with UNIQUE constraint on column unil
>
> mydb=# CREATE TABLE uni_col ( no_uni1 integer, no_uni2 integer, uni1
> integer unique);
> NOTICE: CREATE TABLE / UNIQUE will create implicit index
> "uni_col_uni1_key" for table "uni_col"
> CREATE TABLE
>
> mydb=# \d uni_col
> Table "public.uni_col"
> Column | Type | Modifiers
> ---------+---------+-----------
> no_uni1 | integer |
> no_uni2 | integer |
> uni1 | integer |
> Indexes:
> "uni_col_uni1_key" UNIQUE CONSTRAINT, btree (uni1)
>
>
> 2) Add some records to uni_col
>
> mydb=# INSERT INTO uni_col VALUES(1,1,1);
> INSERT 0 1
> mydb=# INSERT INTO uni_col VALUES(2,2,2);
> INSERT 0 1
> mydb=# INSERT INTO uni_col VALUES(3,3,3);
> INSERT 0 1
>
> mydb=# SELECT relid,relname FROM pg_stat_user_tables WHERE relname =
> 'uni_col';
> relid | relname
> --------+---------
> 114775 | uni_col
> (1 row)
>
>
> 3) See how uni_col is distributed and by what key
>
> mydb=# SELECT * FROM pgxc_class WHERE pcrelid = 114775;
> pcrelid | pclocatortype | pcattnum | pchashalgorithm | pchashbuckets |
> nodeoid
> s
>
> ---------+---------------+----------+-----------------+---------------+---------
> ----
> 114775 | H | 3 | 1 | 4096 | 16385 16
> 386
> (1 row)
>
>
> 4) Create another table uni_col2
>
> mydb=# CREATE TABLE uni_col2 AS SELECT * FROM uni_col;
> INSERT 0 3
>
>
> 5) See how uni_col2 is distributed and by what key;
> Expect uni1 is the key, but it isn't.
>
> mydb=# SELECT relid,relname FROM pg_stat_user_tables WHERE relname =
> 'uni_col2';
>
> relid | relname
> --------+----------
> 114780 | uni_col2
> (1 row)
>
> mydb=# SELECT * FROM pgxc_class WHERE pcrelid = 114780;
> pcrelid | pclocatortype | pcattnum | pchashalgorithm | pchashbuckets |
> nodeoid
> s
>
> ---------+---------------+----------+-----------------+---------------+---------
> ----
> 114780 | H | 1 | 1 | 4096 | 16385 16
> 386
> (1 row)
>
> mydb=# \d uni_col2
> Table "public.uni_col2"
> Column | Type | Modifiers
> ---------+---------+-----------
> no_uni1 | integer |
> no_uni2 | integer |
> uni1 | integer |
>
> --
> Hitoshi HEMMI
> NTT Open Source Software Center
> hem...@la...
> (Please note that my address has changed.)
> Tel:(03)5860-5115
> Fax:(03)5463-5490
>
>
>
> ------------------------------------------------------------------------------
> Monitor your physical, virtual and cloud infrastructure from a single
> web console. Get in-depth insight into apps, servers, databases, vmware,
> SAP, cloud infrastructure, etc. Download 30-day Free Trial.
> Pricing starts from $795 for 25 servers or applications!
> http://p.sf.net/sfu/zoho_dev2dev_nov
> _______________________________________________
> Postgres-xc-bugs mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>



-- 
Michael Paquier
http://michael.otacoo.com