#478 LIKE INCLUDING ALL Does not include DISTRIBUTE BY

[David E. Wheeler]

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.

[cbx]

I think LIKE clause is a part of a columns definition.
As DISTRIBUTE BY clause is outside of the definition, not copying the distribution rule is natural shelving conveniency in my opinion.

[David E. Wheeler]

Hrm. Perhaps. It would be nice, however, to have a way to some way to tell it to use the same DISTRIBUTE BY algorithm. I'm getting around it for now by specifying the DISTRIBUTE BY clause explicitly:

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

Sure would be nice not to have to do that, though.

[Ashutosh Bapat]

If grammar permits, we might do DISTRIBUTE LIKE. Can you please add a
feature request, or best provide a patch?

On Wed, Feb 19, 2014 at 12:09 PM, David E. Wheeler wheeler@users.sf.netwrote:

Hrm. Perhaps. It would be nice, however, to have a way to some way to tell
it to use the same DISTRIBUTE BY algorithm. I'm getting around it for now
by specifying the DISTRIBUTE BY clause explicitly:

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

Sure would be nice not to have to do that, though.

• [bugs:#478] http://sourceforge.net/p/postgres-xc/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:18 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:

BEGINSETCREATE TABLECREATE 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: childHas OIDs: noDistribute 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: parentHas OIDs: noDistribute 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

[David E. Wheeler]

Hrm. So something like

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

? That looks pretty useful.

[Ashutosh Bapat]

Thanks for the feature request.

On Wed, Feb 19, 2014 at 10:45 PM, David E. Wheeler wheeler@users.sf.netwrote:

Hrm. So something like

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

? That looks pretty useful.

• [bugs:#478] http://sourceforge.net/p/postgres-xc/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:

BEGINSETCREATE TABLECREATE 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: childHas OIDs: noDistribute 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: parentHas OIDs: noDistribute 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

[David E. Wheeler]

Added issue #479.