Обсуждение: [pgAdmin4][RM3462] PG11: Support INCLUDE indexes

On Wed, Jul 18, 2018 at 1:59 PM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Dave,

On Wed, Jul 18, 2018 at 6:07 PM, Dave Page <dpage@pgadmin.org> wrote:

Hi

On Wed, Jul 18, 2018 at 12:12 PM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Hackers,

Attached is the patch to support INCLUDE clause for indexes which is added from Postgres 11. As per Postgres 11 docs, INCLUDE clause is supported for Unique Key, Primary Key, Exclusion constraint and Normal Indexes.

The patch also includes python test cases for Exclusion constraint and small fix in SQL Editor module where the test case ExtractSQLFromNetworkParametersTest was failing for me due on Python 2.7.

​Kindly review.​

I spotted a couple of issues whilst testing this:

- There are no doc updates and updated screenshots.

​Will add and send the patch.​

 

Thanks.

 

- There's some indenting issue in the CREATE TABLE SQL (see how the INCLUDE line is doubly-indented), e.g.

-- Table: public.t1

-- DROP TABLE public.t1;

CREATE TABLE public.t1

(

    id bigint NOT NULL DEFAULT nextval('t1_id_seq'::regclass),

    c1 text COLLATE pg_catalog."default",

    c2 text COLLATE pg_catalog."default",

    c3 date,

    CONSTRAINT t1_pkey PRIMARY KEY (id),

    CONSTRAINT t1_id_c1_c2_key UNIQUE (id, c1)

        INCLUDE(c2)

​This is intentional because it is part of the constraint and not the part of table. Try adding fill factor and check the SQL. It will also be indented in the same way.​

Ah, ok - cool.

 

 

)

WITH (

    OIDS = FALSE

)

TABLESPACE pg_default;

ALTER TABLE public.t1

    OWNER to postgres;

-- Index: i1

-- DROP INDEX public.i1;

CREATE INDEX i1

    ON public.t1 USING btree

    (id, c1 COLLATE pg_catalog."default")

    INCLUDE(c2, c3)

    TABLESPACE pg_default; 

Thanks!

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--

Thanks and Regards,

Aditya Toshniwal

Software Engineer | EnterpriseDB Software Solutions | Pune

"Don't Complain about Heat, Plant a tree"

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi Hackers,

Attached is the updated patch and includes doc, screenshot updates.

Kindly review.

On Wed, Jul 18, 2018 at 6:41 PM, Dave Page <dpage@pgadmin.org> wrote:

On Wed, Jul 18, 2018 at 1:59 PM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Dave,

On Wed, Jul 18, 2018 at 6:07 PM, Dave Page <dpage@pgadmin.org> wrote:

Hi

On Wed, Jul 18, 2018 at 12:12 PM, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Hackers,

Attached is the patch to support INCLUDE clause for indexes which is added from Postgres 11. As per Postgres 11 docs, INCLUDE clause is supported for Unique Key, Primary Key, Exclusion constraint and Normal Indexes.

The patch also includes python test cases for Exclusion constraint and small fix in SQL Editor module where the test case ExtractSQLFromNetworkParametersTest was failing for me due on Python 2.7.

​Kindly review.​

I spotted a couple of issues whilst testing this:

- There are no doc updates and updated screenshots.

​Will add and send the patch.​

 

Thanks.

 

- There's some indenting issue in the CREATE TABLE SQL (see how the INCLUDE line is doubly-indented), e.g.

-- Table: public.t1

-- DROP TABLE public.t1;

CREATE TABLE public.t1

(

    id bigint NOT NULL DEFAULT nextval('t1_id_seq'::regclass),

    c1 text COLLATE pg_catalog."default",

    c2 text COLLATE pg_catalog."default",

    c3 date,

    CONSTRAINT t1_pkey PRIMARY KEY (id),

    CONSTRAINT t1_id_c1_c2_key UNIQUE (id, c1)

        INCLUDE(c2)

​This is intentional because it is part of the constraint and not the part of table. Try adding fill factor and check the SQL. It will also be indented in the same way.​

Ah, ok - cool.

 

 

)

WITH (

    OIDS = FALSE

)

TABLESPACE pg_default;

ALTER TABLE public.t1

    OWNER to postgres;

-- Index: i1

-- DROP INDEX public.i1;

CREATE INDEX i1

    ON public.t1 USING btree

    (id, c1 COLLATE pg_catalog."default")

    INCLUDE(c2, c3)

    TABLESPACE pg_default; 

Thanks!

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--

Thanks and Regards,

Aditya Toshniwal

Software Engineer | EnterpriseDB Software Solutions | Pune

"Don't Complain about Heat, Plant a tree"

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--

Thanks and Regards,

Aditya Toshniwal

Software Engineer | EnterpriseDB Software Solutions | Pune

"Don't Complain about Heat, Plant a tree"