Обсуждение: 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,
Вложения
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,
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
Вложения
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