Обсуждение: PATCH: To fix the issue of column order in Primary Key/Index constraints (pgAdmin4)

Поиск
Список
Период
Сортировка

PATCH: To fix the issue of column order in Primary Key/Index constraints (pgAdmin4)

От
Murtuza Zabuawala
Дата:
Hi,

PFA patch to fix the issue where column order in Primary Key/Index constraints was maintained while creating sql from template.
RM#1842


--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Вложения

Re: PATCH: To fix the issue of column order in Primary Key/Index constraints (pgAdmin4)

От
Dave Page
Дата:
On Thu, Nov 24, 2016 at 9:57 AM, Murtuza Zabuawala
<murtuza.zabuawala@enterprisedb.com> wrote:
> Hi,
>
> PFA patch to fix the issue where column order in Primary Key/Index
> constraints was maintained while creating sql from template.
> RM#1842

As far as I can see, this doesn't work. Given the following indexes
from a PEM database:

CREATE UNIQUE INDEX pem_chart_config_uid_did_idx ON pem.chart_config
USING btree (cid, uid, did)
        WHERE objid IS NULL;

CREATE INDEX pem_chart_config_objid_idx ON pem.chart_config USING
btree (cid, uid, did, objid)
        WHERE objid IS NOT NULL AND database IS NULL;

CREATE UNIQUE INDEX pem_chart_config_objid_db_idx ON pem.chart_config
USING btree (cid, uid, did, objid, database)
        WHERE objid IS NOT NULL AND database IS NOT NULL AND schema IS NULL;

CREATE UNIQUE INDEX pem_chart_config_objid_db_schema_idx ON
pem.chart_config USING btree (cid, uid, did, objid, database, schema)
        WHERE objid IS NOT NULL AND database IS NOT NULL AND schema IS
NOT NULL AND tbl IS NULL;

CREATE UNIQUE INDEX pem_chart_config_objid_db_schema_tbl_idx ON
pem.chart_config USING btree (cid, uid, did, objid, database, schema,
tbl)
        WHERE objid IS NOT NULL AND database IS NOT NULL AND schema IS
NOT NULL AND tbl IS NOT NULL;


I'm seeing the following RE-SQL:

-- Index: pem_chart_config_objid_db_idx

-- DROP INDEX pem.pem_chart_config_objid_db_idx;

CREATE UNIQUE INDEX pem_chart_config_objid_db_idx
    ON pem.chart_config USING btree
    (database COLLATE pg_catalog."default", objid, did, uid, cid)
    TABLESPACE pg_default    WHERE objid IS NOT NULL AND database IS
NOT NULL AND schema IS NULL
;

-- Index: pem_chart_config_objid_db_schema_idx

-- DROP INDEX pem.pem_chart_config_objid_db_schema_idx;

CREATE UNIQUE INDEX pem_chart_config_objid_db_schema_idx
    ON pem.chart_config USING btree
    (schema COLLATE pg_catalog."default", database COLLATE
pg_catalog."default", objid, did, uid, cid)
    TABLESPACE pg_default    WHERE objid IS NOT NULL AND database IS
NOT NULL AND schema IS NOT NULL AND tbl IS NULL
;

-- Index: pem_chart_config_objid_db_schema_tbl_idx

-- DROP INDEX pem.pem_chart_config_objid_db_schema_tbl_idx;

CREATE UNIQUE INDEX pem_chart_config_objid_db_schema_tbl_idx
    ON pem.chart_config USING btree
    (tbl COLLATE pg_catalog."default", schema COLLATE
pg_catalog."default", database COLLATE pg_catalog."default", objid,
did, uid, cid)
    TABLESPACE pg_default    WHERE objid IS NOT NULL AND database IS
NOT NULL AND schema IS NOT NULL AND tbl IS NOT NULL
;

-- Index: pem_chart_config_objid_idx

-- DROP INDEX pem.pem_chart_config_objid_idx;

CREATE INDEX pem_chart_config_objid_idx
    ON pem.chart_config USING btree
    (objid, did, uid, cid)
    TABLESPACE pg_default    WHERE objid IS NOT NULL AND database IS NULL
;

-- Index: pem_chart_config_uid_did_idx

-- DROP INDEX pem.pem_chart_config_uid_did_idx;

CREATE UNIQUE INDEX pem_chart_config_uid_did_idx
    ON pem.chart_config USING btree
    (did, uid, cid)
    TABLESPACE pg_default    WHERE objid IS NULL
;

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

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

Re: [pgadmin-hackers] PATCH: To fix the issue of column order inPrimary Key/Index constraints (pgAdmin4)

От
Murtuza Zabuawala
Дата:
Hi Dave,

Please find updates patch for the same.
RM#1842


--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Nov 25, 2016 at 3:37 PM, Dave Page <dpage@pgadmin.org> wrote:
On Thu, Nov 24, 2016 at 9:57 AM, Murtuza Zabuawala
<murtuza.zabuawala@enterprisedb.com> wrote:
> Hi,
>
> PFA patch to fix the issue where column order in Primary Key/Index
> constraints was maintained while creating sql from template.
> RM#1842

As far as I can see, this doesn't work. Given the following indexes
from a PEM database:

CREATE UNIQUE INDEX pem_chart_config_uid_did_idx ON pem.chart_config
USING btree (cid, uid, did)
        WHERE objid IS NULL;

CREATE INDEX pem_chart_config_objid_idx ON pem.chart_config USING
btree (cid, uid, did, objid)
        WHERE objid IS NOT NULL AND database IS NULL;

CREATE UNIQUE INDEX pem_chart_config_objid_db_idx ON pem.chart_config
USING btree (cid, uid, did, objid, database)
        WHERE objid IS NOT NULL AND database IS NOT NULL AND schema IS NULL;

CREATE UNIQUE INDEX pem_chart_config_objid_db_schema_idx ON
pem.chart_config USING btree (cid, uid, did, objid, database, schema)
        WHERE objid IS NOT NULL AND database IS NOT NULL AND schema IS
NOT NULL AND tbl IS NULL;

CREATE UNIQUE INDEX pem_chart_config_objid_db_schema_tbl_idx ON
pem.chart_config USING btree (cid, uid, did, objid, database, schema,
tbl)
        WHERE objid IS NOT NULL AND database IS NOT NULL AND schema IS
NOT NULL AND tbl IS NOT NULL;


I'm seeing the following RE-SQL:

-- Index: pem_chart_config_objid_db_idx

-- DROP INDEX pem.pem_chart_config_objid_db_idx;

CREATE UNIQUE INDEX pem_chart_config_objid_db_idx
    ON pem.chart_config USING btree
    (database COLLATE pg_catalog."default", objid, did, uid, cid)
    TABLESPACE pg_default    WHERE objid IS NOT NULL AND database IS
NOT NULL AND schema IS NULL
;

-- Index: pem_chart_config_objid_db_schema_idx

-- DROP INDEX pem.pem_chart_config_objid_db_schema_idx;

CREATE UNIQUE INDEX pem_chart_config_objid_db_schema_idx
    ON pem.chart_config USING btree
    (schema COLLATE pg_catalog."default", database COLLATE
pg_catalog."default", objid, did, uid, cid)
    TABLESPACE pg_default    WHERE objid IS NOT NULL AND database IS
NOT NULL AND schema IS NOT NULL AND tbl IS NULL
;

-- Index: pem_chart_config_objid_db_schema_tbl_idx

-- DROP INDEX pem.pem_chart_config_objid_db_schema_tbl_idx;

CREATE UNIQUE INDEX pem_chart_config_objid_db_schema_tbl_idx
    ON pem.chart_config USING btree
    (tbl COLLATE pg_catalog."default", schema COLLATE
pg_catalog."default", database COLLATE pg_catalog."default", objid,
did, uid, cid)
    TABLESPACE pg_default    WHERE objid IS NOT NULL AND database IS
NOT NULL AND schema IS NOT NULL AND tbl IS NOT NULL
;

-- Index: pem_chart_config_objid_idx

-- DROP INDEX pem.pem_chart_config_objid_idx;

CREATE INDEX pem_chart_config_objid_idx
    ON pem.chart_config USING btree
    (objid, did, uid, cid)
    TABLESPACE pg_default    WHERE objid IS NOT NULL AND database IS NULL
;

-- Index: pem_chart_config_uid_did_idx

-- DROP INDEX pem.pem_chart_config_uid_did_idx;

CREATE UNIQUE INDEX pem_chart_config_uid_did_idx
    ON pem.chart_config USING btree
    (did, uid, cid)
    TABLESPACE pg_default    WHERE objid IS NULL
;

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

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

Вложения

Re: [pgadmin-hackers] PATCH: To fix the issue of column order inPrimary Key/Index constraints (pgAdmin4)

От
Dave Page
Дата:
Thanks - patch applied!

On Fri, Dec 16, 2016 at 9:03 AM, Murtuza Zabuawala
<murtuza.zabuawala@enterprisedb.com> wrote:
> Hi Dave,
>
> Please find updates patch for the same.
> RM#1842
>
>
> --
> Regards,
> Murtuza Zabuawala
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> On Fri, Nov 25, 2016 at 3:37 PM, Dave Page <dpage@pgadmin.org> wrote:
>>
>> On Thu, Nov 24, 2016 at 9:57 AM, Murtuza Zabuawala
>> <murtuza.zabuawala@enterprisedb.com> wrote:
>> > Hi,
>> >
>> > PFA patch to fix the issue where column order in Primary Key/Index
>> > constraints was maintained while creating sql from template.
>> > RM#1842
>>
>> As far as I can see, this doesn't work. Given the following indexes
>> from a PEM database:
>>
>> CREATE UNIQUE INDEX pem_chart_config_uid_did_idx ON pem.chart_config
>> USING btree (cid, uid, did)
>>         WHERE objid IS NULL;
>>
>> CREATE INDEX pem_chart_config_objid_idx ON pem.chart_config USING
>> btree (cid, uid, did, objid)
>>         WHERE objid IS NOT NULL AND database IS NULL;
>>
>> CREATE UNIQUE INDEX pem_chart_config_objid_db_idx ON pem.chart_config
>> USING btree (cid, uid, did, objid, database)
>>         WHERE objid IS NOT NULL AND database IS NOT NULL AND schema IS
>> NULL;
>>
>> CREATE UNIQUE INDEX pem_chart_config_objid_db_schema_idx ON
>> pem.chart_config USING btree (cid, uid, did, objid, database, schema)
>>         WHERE objid IS NOT NULL AND database IS NOT NULL AND schema IS
>> NOT NULL AND tbl IS NULL;
>>
>> CREATE UNIQUE INDEX pem_chart_config_objid_db_schema_tbl_idx ON
>> pem.chart_config USING btree (cid, uid, did, objid, database, schema,
>> tbl)
>>         WHERE objid IS NOT NULL AND database IS NOT NULL AND schema IS
>> NOT NULL AND tbl IS NOT NULL;
>>
>>
>> I'm seeing the following RE-SQL:
>>
>> -- Index: pem_chart_config_objid_db_idx
>>
>> -- DROP INDEX pem.pem_chart_config_objid_db_idx;
>>
>> CREATE UNIQUE INDEX pem_chart_config_objid_db_idx
>>     ON pem.chart_config USING btree
>>     (database COLLATE pg_catalog."default", objid, did, uid, cid)
>>     TABLESPACE pg_default    WHERE objid IS NOT NULL AND database IS
>> NOT NULL AND schema IS NULL
>> ;
>>
>> -- Index: pem_chart_config_objid_db_schema_idx
>>
>> -- DROP INDEX pem.pem_chart_config_objid_db_schema_idx;
>>
>> CREATE UNIQUE INDEX pem_chart_config_objid_db_schema_idx
>>     ON pem.chart_config USING btree
>>     (schema COLLATE pg_catalog."default", database COLLATE
>> pg_catalog."default", objid, did, uid, cid)
>>     TABLESPACE pg_default    WHERE objid IS NOT NULL AND database IS
>> NOT NULL AND schema IS NOT NULL AND tbl IS NULL
>> ;
>>
>> -- Index: pem_chart_config_objid_db_schema_tbl_idx
>>
>> -- DROP INDEX pem.pem_chart_config_objid_db_schema_tbl_idx;
>>
>> CREATE UNIQUE INDEX pem_chart_config_objid_db_schema_tbl_idx
>>     ON pem.chart_config USING btree
>>     (tbl COLLATE pg_catalog."default", schema COLLATE
>> pg_catalog."default", database COLLATE pg_catalog."default", objid,
>> did, uid, cid)
>>     TABLESPACE pg_default    WHERE objid IS NOT NULL AND database IS
>> NOT NULL AND schema IS NOT NULL AND tbl IS NOT NULL
>> ;
>>
>> -- Index: pem_chart_config_objid_idx
>>
>> -- DROP INDEX pem.pem_chart_config_objid_idx;
>>
>> CREATE INDEX pem_chart_config_objid_idx
>>     ON pem.chart_config USING btree
>>     (objid, did, uid, cid)
>>     TABLESPACE pg_default    WHERE objid IS NOT NULL AND database IS NULL
>> ;
>>
>> -- Index: pem_chart_config_uid_did_idx
>>
>> -- DROP INDEX pem.pem_chart_config_uid_did_idx;
>>
>> CREATE UNIQUE INDEX pem_chart_config_uid_did_idx
>>     ON pem.chart_config USING btree
>>     (did, uid, cid)
>>     TABLESPACE pg_default    WHERE objid IS NULL
>> ;
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>
>



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

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