Thanks for the feature request.


On Wed, Feb 19, 2014 at 10:45 PM, David E. Wheeler <wheeler@users.sf.net> wrote:

Hrm. So something like

CREATE TABLE child ( LIKE parent INCLUDING ALL ) INHERITS (parent)
DISTRIBUTE LIKE parent;

? That looks pretty useful.


[bugs:#478] LIKE INCLUDING ALL Does not include DISTRIBUTE BY

Status: open
Labels: inheritance like create table distribute by
Created: Fri Feb 14, 2014 07:28 PM UTC by David E. Wheeler
Last Updated: Wed Feb 19, 2014 06:39 AM UTC
Owner: nobody

Given this SQL:

BEGIN;

SET client_min_messages TO warning;

CREATE TABLE parent (
    foo_id     INTEGER     NOT NULL,
    name       TEXT        NOT NULL,
    rank       INTEGER     NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (foo_id, name)
) DISTRIBUTE BY HASH(name);

CREATE TABLE child (
    LIKE parent INCLUDING ALL
) INHERITS (parent);

\d+ parent
\d+ child

ROLLBACK;

The output is:

BEGIN
SET
CREATE TABLE
CREATE TABLE
                                   Table "public.parent"
   Column   |           Type           | Modifiers | Storage  | Stats target | Description

------------+--------------------------+-----------+----------+--------------+------------
-
 foo_id     | integer                  | not null  | plain    |              | 
 name       | text                     | not null  | extended |              | 
 rank       | integer                  | not null  | plain    |              | 
 created_at | timestamp with time zone | not null  | plain    |              | 
Indexes:
    "parent_pkey" PRIMARY KEY, btree (foo_id, name)
Child tables: child
Has OIDs: no
Distribute By: HASH(name)
Location Nodes: ALL DATANODES

                                   Table "public.child"
   Column   |           Type           | Modifiers | Storage  | Stats target | Description

------------+--------------------------+-----------+----------+--------------+------------
-
 foo_id     | integer                  | not null  | plain    |              | 
 name       | text                     | not null  | extended |              | 
 rank       | integer                  | not null  | plain    |              | 
 created_at | timestamp with time zone | not null  | plain    |              | 
Indexes:
    "child_pkey" PRIMARY KEY, btree (foo_id, name)
Inherits: parent
Has OIDs: no
Distribute By: HASH(foo_id)
Location Nodes: ALL DATANODES

ROLLBACK

Note that the DISTRIBUTE BY algorithm was not copied, even though I declared LIKE parent INCLUDING ALL. It would be nice if LIKE also copidd the DISTRIBUTE BY algorithm.

Workaround is to explicitly include the DISTRIBUTE BY clause when creating the child table.


Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/postgres-xc/bugs/478/

To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/




--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company