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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Sorry for my late responce. (Are you still there? ;-)
We still have problem.
Document you pointed says:
"If DISTRIBUTE BY is not specified, columns with UNIQUE constraint will 
be chosen as the distribution key."
What does it mean?
We tried two possible scenarios, neither succeeded.

1) Specify unique constraint when CREATE TABLE -> syntax error
test_db1=# CREATE TABLE uni_col2(no_uni1 integer, no_uni2 integer, uni1 
integer unique) AS SELECT * FROM uni_col;
ERROR: syntax error at or near "AS"
LINE 1: ...i1 integer, no_uni2 integer, uni1 integer unique) AS SELECT ...

2) unique does not inherit by CREATE TABLE AS
test_db1=# CREATE TABLE uni_col2(no_uni1, no_uni2, uni1) AS SELECT * 
FROM uni_col;
INSERT 0 3
test_db1=# \d uni_col2
Table "public.uni_col2"
Column | Type | Modifiers
---------+---------+-----------
no_uni1 | integer |
no_uni2 | integer |
uni1 | integer |

test_db1=# SELECT relid,relname FROM pg_stat_user_tables WHERE relname = 
'uni_col2';
relid | relname
-------+----------
32848 | uni_col2
(1 row)

test_db1=# SELECT * FROM pgxc_class WHERE pcrelid = 32848;
pcrelid | pclocatortype | pcattnum | pchashalgorithm | pchashbuckets | 
nodeoids
---------+---------------+----------+-----------------+---------------+------------- 

32848 | H | 1 | 1 | 4096 | 16385 16386
(1 row)

By the way, we use v1.0.1. (Sorry)

-hemmi

Michael Paquier さんは書きました:
> 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... <mailto:hem...@la...>> wrote:
>
>     Hi, list
>
>     ============================================================================
>     POSTGRES-XC BUG REPORT TEMPLATE
>     ============================================================================
>
>     Your name : Hitoshi Hemmi
>     Your email address : hem...@la...
>     <mailto: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... <mailto: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...
>     <mailto:Pos...@li...>
>     https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>
>
>
>
> -- 
> Michael Paquier
> http://michael.otacoo.com

-- 
Hitoshi HEMMI
NTT Open Source Software Center
hem...@la...
(Please note that my address has changed.)
Tel:(03)5860-5115
Fax:(03)5463-5490