#478 LIKE INCLUDING ALL Does not include DISTRIBUTE BY

V1.1 maintenance
open
nobody
5
2015-02-12
2014-02-14
No

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.

Related

Bugs: #478

Discussion

  • cbx

    cbx - 2014-02-19

    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

      Ashutosh Bapat - 2014-02-19

      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.

      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

       

      Related

      Bugs: #478

      • 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

          Ashutosh Bapat - 2014-02-20

          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.

          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

           

          Related

          Bugs: #478

  • David E. Wheeler

    Added issue #479.

     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks