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

On Thu, Nov 24, 2016 at 9:57 AM, Murtuza Zabuawala
<murtuza(dot)zabuawala(at)enterprisedb(dot)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