public inbox for [email protected]help / color / mirror / Atom feed
[pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL 14+ messages / 2 participants [nested] [flat]
* [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2016-10-20 06:47 Surinder Kumar <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Surinder Kumar @ 2016-10-20 06:47 UTC (permalink / raw) To: pgadmin-hackers Hi, The options like "sort" and "nulls" must be conditional. i.e. include only when access method type is other than "gist" or "gin". Please find attached patch and review. Thanks, Surinder Kumar -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [application/octet-stream] RM1840.patch (1.8K, 3-RM1840.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/9.1_plus/create.sql index 5c2dc05..33af197 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/9.1_plus/create.sql @@ -3,8 +3,8 @@ CREATE {% if data.indisunique %}UNIQUE {% endif %}INDEX {% if data.isconcurrent {% if mode == 'create' %} ({% for c in data.columns %}{% if loop.index != 1 %}, {% endif %}{{conn|qtIdent(c.colname)}}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.op_class %} - {{c.op_class}}{% endif %}{% if c.sort_order is defined %}{% if c.sort_order %} DESC{% else %} ASC{% endif %}{% endif %}{% if c.nulls is defined %} NULLS {% if c.nulls %} -FIRST{% else %}LAST{% endif %}{% endif %}{% endfor %}) + {{c.op_class}}{% endif %}{% if data.amname is defined and data.amname not in ['gist', 'gin'] %}{% if c.sort_order is defined %}{% if c.sort_order %} DESC{% else %} ASC{% endif %}{% endif %}{% if c.nulls is defined %} NULLS {% if c.nulls %} +FIRST{% else %}LAST{% endif %}{% endif %}{% endif %}{% endfor %}) {% else %} {## We will get indented data from postgres for column ##} ({% for c in data.columns %}{% if loop.index != 1 %}, {% endif %}{{c.colname}}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.op_class %} @@ -16,4 +16,4 @@ FIRST{% else %}LAST{% endif %}{% endif %}{% endfor %}) {% endif %}{% if data.spcname %} TABLESPACE {{conn|qtIdent(data.spcname)}}{% endif %}{% if data.indconstraint %} WHERE {{data.indconstraint}} -{% endif %}; \ No newline at end of file +{% endif %}; ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2016-10-21 11:08 Dave Page <[email protected]> parent: Surinder Kumar <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Dave Page @ 2016-10-21 11:08 UTC (permalink / raw) To: Surinder Kumar <[email protected]>; +Cc: pgadmin-hackers Thanks, applied. On Thu, Oct 20, 2016 at 7:47 AM, Surinder Kumar <[email protected]> wrote: > Hi, > > The options like "sort" and "nulls" must be conditional. i.e. include only > when access method type is other than "gist" or "gin". > > Please find attached patch and review. > > Thanks, > Surinder Kumar > > > > -- > Sent via pgadmin-hackers mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgadmin-hackers > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2016-10-21 15:16 Surinder Kumar <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Surinder Kumar @ 2016-10-21 15:16 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers Hi This fix is for exclusion constraint. The options like "order" and "nulls" must be conditional. i.e. include only when access method type is other than "gist". Please find attached patch and review. On Fri, Oct 21, 2016 at 4:38 PM, Dave Page <[email protected]> wrote: > Thanks, applied. > > On Thu, Oct 20, 2016 at 7:47 AM, Surinder Kumar > <[email protected]> wrote: > > Hi, > > > > The options like "sort" and "nulls" must be conditional. i.e. include > only > > when access method type is other than "gist" or "gin". > > > > Please find attached patch and review. > > > > Thanks, > > Surinder Kumar > > > > > > > > -- > > Sent via pgadmin-hackers mailing list ([email protected]) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgadmin-hackers > > > > > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [application/octet-stream] RM1840_same_fix_for_exclusion_constraint.patch (3.8K, 3-RM1840_same_fix_for_exclusion_constraint.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql index db29048..a8097de 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql @@ -1,7 +1,7 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} EXCLUDE {% if data.amname and data.amname != '' %}USING {{data.amname}}{% endif %} ( {% for col in data.columns %}{% if loop.index != 1 %}, - {% endif %}{{ conn|qtIdent(col.column)}} {% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} + {% endif %}{{ conn|qtIdent(col.column)}} {% if data.amname is defined and data.amname != 'gist' %}{% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %}{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %} USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %} @@ -14,4 +14,4 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} IS {{ data.comment|qtLiteral }}; -{% endif %} \ No newline at end of file +{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql index db29048..a8097de 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql @@ -1,7 +1,7 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} EXCLUDE {% if data.amname and data.amname != '' %}USING {{data.amname}}{% endif %} ( {% for col in data.columns %}{% if loop.index != 1 %}, - {% endif %}{{ conn|qtIdent(col.column)}} {% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} + {% endif %}{{ conn|qtIdent(col.column)}} {% if data.amname is defined and data.amname != 'gist' %}{% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %}{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %} USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %} @@ -14,4 +14,4 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} IS {{ data.comment|qtLiteral }}; -{% endif %} \ No newline at end of file +{% endif %} ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2016-10-21 15:22 Dave Page <[email protected]> parent: Surinder Kumar <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Dave Page @ 2016-10-21 15:22 UTC (permalink / raw) To: Surinder Kumar <[email protected]>; +Cc: pgadmin-hackers Hi On Fri, Oct 21, 2016 at 4:16 PM, Surinder Kumar <[email protected]> wrote: > Hi > > This fix is for exclusion constraint. > The options like "order" and "nulls" must be conditional. i.e. include only > when access method type is other than "gist". When creating an index, the asc/desc options are disabled if gist/gin used. I think they also should be here. Also, what about gin indexes in this case? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2016-10-21 15:42 Surinder Kumar <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Surinder Kumar @ 2016-10-21 15:42 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers On Fri, Oct 21, 2016 at 8:52 PM, Dave Page <[email protected]> wrote: > Hi > > On Fri, Oct 21, 2016 at 4:16 PM, Surinder Kumar > <[email protected]> wrote: > > Hi > > > > This fix is for exclusion constraint. > > The options like "order" and "nulls" must be conditional. i.e. include > only > > when access method type is other than "gist". > > When creating an index, the asc/desc options are disabled if gist/gin > used. I think they also should be here. > > Also, what about gin indexes in this case? > As per documentation <https://www.postgresql.org/docs/9.2/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE;, *The access method must support amgettuple (see Chapter 52 <https://www.postgresql.org/docs/9.2/static/indexam.html;); at present this means GIN cannot be used* > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2016-10-21 15:46 Dave Page <[email protected]> parent: Surinder Kumar <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Dave Page @ 2016-10-21 15:46 UTC (permalink / raw) To: Surinder Kumar <[email protected]>; +Cc: pgadmin-hackers On Fri, Oct 21, 2016 at 4:42 PM, Surinder Kumar <[email protected]> wrote: > On Fri, Oct 21, 2016 at 8:52 PM, Dave Page <[email protected]> wrote: >> >> Hi >> >> On Fri, Oct 21, 2016 at 4:16 PM, Surinder Kumar >> <[email protected]> wrote: >> > Hi >> > >> > This fix is for exclusion constraint. >> > The options like "order" and "nulls" must be conditional. i.e. include >> > only >> > when access method type is other than "gist". >> >> When creating an index, the asc/desc options are disabled if gist/gin >> used. I think they also should be here. >> >> Also, what about gin indexes in this case? > > As per documentation, > The access method must support amgettuple (see Chapter 52); at present this > means GIN cannot be used OK, but this patch (unlike the last one) only seems to check for gist. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2016-11-24 12:13 Surinder Kumar <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Surinder Kumar @ 2016-11-24 12:13 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers Hi Please find updated patch with change. On Fri, Oct 21, 2016 at 9:16 PM, Dave Page <[email protected]> wrote: > On Fri, Oct 21, 2016 at 4:42 PM, Surinder Kumar > <[email protected]> wrote: > > On Fri, Oct 21, 2016 at 8:52 PM, Dave Page <[email protected]> wrote: > >> > >> Hi > >> > >> On Fri, Oct 21, 2016 at 4:16 PM, Surinder Kumar > >> <[email protected]> wrote: > >> > Hi > >> > > >> > This fix is for exclusion constraint. > >> > The options like "order" and "nulls" must be conditional. i.e. include > >> > only > >> > when access method type is other than "gist". > >> > >> When creating an index, the asc/desc options are disabled if gist/gin > >> used. I think they also should be here. > >> > >> Also, what about gin indexes in this case? > > > > As per documentation, > > The access method must support amgettuple (see Chapter 52); at present > this > > means GIN cannot be used > > OK, but this patch (unlike the last one) only seems to check for gist. > I have modified the code so It will check for 'gist' and 'spgist' > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [application/octet-stream] RM1840_exclusion_constraint_v2.patch (3.8K, 3-RM1840_exclusion_constraint_v2.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql index db29048..93f3648 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql @@ -1,7 +1,7 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} EXCLUDE {% if data.amname and data.amname != '' %}USING {{data.amname}}{% endif %} ( {% for col in data.columns %}{% if loop.index != 1 %}, - {% endif %}{{ conn|qtIdent(col.column)}} {% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} + {% endif %}{{ conn|qtIdent(col.column)}} {% if data.amname is defined and data.amname not in ['gist', 'spgist'] %}{% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %}{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %} USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %} @@ -14,4 +14,4 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} IS {{ data.comment|qtLiteral }}; -{% endif %} \ No newline at end of file +{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql index db29048..93f3648 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql @@ -1,7 +1,7 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} EXCLUDE {% if data.amname and data.amname != '' %}USING {{data.amname}}{% endif %} ( {% for col in data.columns %}{% if loop.index != 1 %}, - {% endif %}{{ conn|qtIdent(col.column)}} {% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} + {% endif %}{{ conn|qtIdent(col.column)}} {% if data.amname is defined and data.amname not in ['gist', 'spgist'] %}{% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %}{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %} USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %} @@ -14,4 +14,4 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }} IS {{ data.comment|qtLiteral }}; -{% endif %} \ No newline at end of file +{% endif %} ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2016-11-25 11:46 Dave Page <[email protected]> parent: Surinder Kumar <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Dave Page @ 2016-11-25 11:46 UTC (permalink / raw) To: Surinder Kumar <[email protected]>; +Cc: pgadmin-hackers On Thu, Nov 24, 2016 at 12:13 PM, Surinder Kumar <[email protected]> wrote: > Hi > > Please find updated patch with change. > > On Fri, Oct 21, 2016 at 9:16 PM, Dave Page <[email protected]> wrote: >> >> On Fri, Oct 21, 2016 at 4:42 PM, Surinder Kumar >> <[email protected]> wrote: >> > On Fri, Oct 21, 2016 at 8:52 PM, Dave Page <[email protected]> wrote: >> >> >> >> Hi >> >> >> >> On Fri, Oct 21, 2016 at 4:16 PM, Surinder Kumar >> >> <[email protected]> wrote: >> >> > Hi >> >> > >> >> > This fix is for exclusion constraint. >> >> > The options like "order" and "nulls" must be conditional. i.e. >> >> > include >> >> > only >> >> > when access method type is other than "gist". >> >> >> >> When creating an index, the asc/desc options are disabled if gist/gin >> >> used. I think they also should be here. >> >> >> >> Also, what about gin indexes in this case? >> > >> > As per documentation, >> > The access method must support amgettuple (see Chapter 52); at present >> > this >> > means GIN cannot be used >> >> OK, but this patch (unlike the last one) only seems to check for gist. > > I have modified the code so It will check for 'gist' and 'spgist' Hi, This still doesn't seem right to me. For example, if I choose an access method that doesn't have a default operator class for the selected data type, Postgres asks me to explicitly choose one, which I now can't because the combo box is disabled. Conversely, whilst the opclass should probably not be disabled, the ASC/DESC and NULLs FIRST/LAST options probably should be disabled (right now, they're just ignored). Thoughts? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2017-01-13 06:50 Surinder Kumar <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Surinder Kumar @ 2017-01-13 06:50 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers Hi Please find updated patch following changes: 1) Keep field 'opclass' combo box enabled. 2) Keep ASC/DESC and NULLs FIRST/LAST options disable for access methods other than 'btree'. 3) Add validation for name field. On Fri, Nov 25, 2016 at 5:16 PM, Dave Page <[email protected]> wrote: > On Thu, Nov 24, 2016 at 12:13 PM, Surinder Kumar > <[email protected]> wrote: > > Hi > > > > Please find updated patch with change. > > > > On Fri, Oct 21, 2016 at 9:16 PM, Dave Page <[email protected]> wrote: > >> > >> On Fri, Oct 21, 2016 at 4:42 PM, Surinder Kumar > >> <[email protected]> wrote: > >> > On Fri, Oct 21, 2016 at 8:52 PM, Dave Page <[email protected]> wrote: > >> >> > >> >> Hi > >> >> > >> >> On Fri, Oct 21, 2016 at 4:16 PM, Surinder Kumar > >> >> <[email protected]> wrote: > >> >> > Hi > >> >> > > >> >> > This fix is for exclusion constraint. > >> >> > The options like "order" and "nulls" must be conditional. i.e. > >> >> > include > >> >> > only > >> >> > when access method type is other than "gist". > >> >> > >> >> When creating an index, the asc/desc options are disabled if gist/gin > >> >> used. I think they also should be here. > >> >> > >> >> Also, what about gin indexes in this case? > >> > > >> > As per documentation, > >> > The access method must support amgettuple (see Chapter 52); at present > >> > this > >> > means GIN cannot be used > >> > >> OK, but this patch (unlike the last one) only seems to check for gist. > > > > I have modified the code so It will check for 'gist' and 'spgist' > > Hi, > > This still doesn't seem right to me. For example, if I choose an > access method that doesn't have a default operator class for the > selected data type, Postgres asks me to explicitly choose one, which I > now can't because the combo box is disabled. Conversely, whilst the > opclass should probably not be disabled, the ASC/DESC and NULLs > FIRST/LAST options probably should be disabled (right now, they're > just ignored). > > Thoughts? > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [application/octet-stream] RM1840_V1.patch (9.4K, 3-RM1840_V1.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py index d099502..3319b7f 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py @@ -816,7 +816,7 @@ class ExclusionConstraintView(PGChildNodeView): sql = render_template("/".join([self.template_path, 'create.sql']), data=data, conn=self.conn) - return sql, data['name'] if 'name' in data else old_data['name'] + return sql, data['name'] @check_precondition def sql(self, gid, sid, did, scid, tid, exid=None): diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js index 9250d9f..42b4336 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js @@ -23,26 +23,9 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { },{ id: 'oper_class', label:'{{ _('Operator class') }}', type:'text', node: 'table', url: 'get_oper_class', first_empty: true, - editable: function(m) { - if (m instanceof Backbone.Collection) { - return true; - } - if ((_.has(m.collection, 'handler') && - !_.isUndefined(m.collection.handler) && - !_.isUndefined(m.collection.handler.get('oid')))) { - return false; - } - - if (m.collection) { - var indexType = m.collection.handler.get('amname') - return (indexType == 'btree' || _.isUndefined(indexType) || - _.isNull(indexType) || indexType == ''); - } else { - return true; - } - }, + editable: true, select2: { - allowClear: true, width: 'style', + allowClear: true, width: 'style', tags: true, placeholder: '{{ _("Select the operator class") }}' }, cell: Backgrid.Extension.Select2Cell.extend({ initialize: function () { @@ -105,7 +88,10 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { onText: 'ASC', offText: 'DESC', },editable: function(m) { - if (m instanceof Backbone.Collection) { + if (_.contains(['gist', 'spgist', 'hash'], m.top.get('amname'))) { + return false; + } + else if (m instanceof Backbone.Collection) { return true; } if ((_.has(m.collection, 'handler') && @@ -121,7 +107,10 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { onText: 'FIRST', offText: 'LAST', },editable: function(m) { - if (m instanceof Backbone.Collection) { + if (_.contains(['gist', 'spgist', 'hash'], m.top.get('amname'))) { + return false; + } + else if (m instanceof Backbone.Collection) { return true; } if ((_.has(m.collection, 'handler') && @@ -894,8 +883,15 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { }], validate: function() { this.errorModel.clear(); - var columns = this.get('columns'); - if ((_.isUndefined(columns) || _.isNull(columns) || columns.length < 1)) { + var columns = this.get('columns'), + name = this.get('name'); + + if ((_.isUndefined(name) || _.isNull(name) || name.length < 1)) { + var msg = '{{ _('Please specify name for exclusion constraint.') }}'; + this.errorModel.set('name', msg); + return msg; + } + else if ((_.isUndefined(columns) || _.isNull(columns) || columns.length < 1)) { var msg = '{{ _('Please specify columns for exclusion constraint.') }}'; this.errorModel.set('columns', msg); return msg; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql index db29048..80c427a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql @@ -1,7 +1,7 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} EXCLUDE {% if data.amname and data.amname != '' %}USING {{data.amname}}{% endif %} ( {% for col in data.columns %}{% if loop.index != 1 %}, - {% endif %}{{ conn|qtIdent(col.column)}} {% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} + {% endif %}{{ conn|qtIdent(col.column)}}{% if col.oper_class and col.oper_class != '' %} {{col.oper_class}}{% endif%}{% if col.order is defined %}{% if col.order %} ASC{% else %} DESC{% endif %} NULLS{% endif %} {% if col.nulls_order is defined %}{% if col.nulls_order %}FIRST {% else %}LAST {% endif %}{% endif %}WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %} USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql index db29048..80c427a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql @@ -1,7 +1,7 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} EXCLUDE {% if data.amname and data.amname != '' %}USING {{data.amname}}{% endif %} ( {% for col in data.columns %}{% if loop.index != 1 %}, - {% endif %}{{ conn|qtIdent(col.column)}} {% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} + {% endif %}{{ conn|qtIdent(col.column)}}{% if col.oper_class and col.oper_class != '' %} {{col.oper_class}}{% endif%}{% if col.order is defined %}{% if col.order %} ASC{% else %} DESC{% endif %} NULLS{% endif %} {% if col.nulls_order is defined %}{% if col.nulls_order %}FIRST {% else %}LAST {% endif %}{% endif %}WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %} USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.6_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.6_plus/create.sql index db29048..80c427a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.6_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.6_plus/create.sql @@ -1,7 +1,7 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} EXCLUDE {% if data.amname and data.amname != '' %}USING {{data.amname}}{% endif %} ( {% for col in data.columns %}{% if loop.index != 1 %}, - {% endif %}{{ conn|qtIdent(col.column)}} {% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} + {% endif %}{{ conn|qtIdent(col.column)}}{% if col.oper_class and col.oper_class != '' %} {{col.oper_class}}{% endif%}{% if col.order is defined %}{% if col.order %} ASC{% else %} DESC{% endif %} NULLS{% endif %} {% if col.nulls_order is defined %}{% if col.nulls_order %}FIRST {% else %}LAST {% endif %}{% endif %}WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %} USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %} ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2017-01-17 09:38 Dave Page <[email protected]> parent: Surinder Kumar <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Dave Page @ 2017-01-17 09:38 UTC (permalink / raw) To: Surinder Kumar <[email protected]>; +Cc: pgadmin-hackers Hi On Fri, Jan 13, 2017 at 6:50 AM, Surinder Kumar <[email protected]> wrote: > Hi > > Please find updated patch following changes: > 1) Keep field 'opclass' combo box enabled. That doesn't seem to be working. > 2) Keep ASC/DESC and NULLs FIRST/LAST options disable for access methods > other than 'btree'. > 3) Add validation for name field. Those bits do though :-) Please fix 1). Thanks! > On Fri, Nov 25, 2016 at 5:16 PM, Dave Page <[email protected]> wrote: >> >> On Thu, Nov 24, 2016 at 12:13 PM, Surinder Kumar >> <[email protected]> wrote: >> > Hi >> > >> > Please find updated patch with change. >> > >> > On Fri, Oct 21, 2016 at 9:16 PM, Dave Page <[email protected]> wrote: >> >> >> >> On Fri, Oct 21, 2016 at 4:42 PM, Surinder Kumar >> >> <[email protected]> wrote: >> >> > On Fri, Oct 21, 2016 at 8:52 PM, Dave Page <[email protected]> wrote: >> >> >> >> >> >> Hi >> >> >> >> >> >> On Fri, Oct 21, 2016 at 4:16 PM, Surinder Kumar >> >> >> <[email protected]> wrote: >> >> >> > Hi >> >> >> > >> >> >> > This fix is for exclusion constraint. >> >> >> > The options like "order" and "nulls" must be conditional. i.e. >> >> >> > include >> >> >> > only >> >> >> > when access method type is other than "gist". >> >> >> >> >> >> When creating an index, the asc/desc options are disabled if >> >> >> gist/gin >> >> >> used. I think they also should be here. >> >> >> >> >> >> Also, what about gin indexes in this case? >> >> > >> >> > As per documentation, >> >> > The access method must support amgettuple (see Chapter 52); at >> >> > present >> >> > this >> >> > means GIN cannot be used >> >> >> >> OK, but this patch (unlike the last one) only seems to check for gist. >> > >> > I have modified the code so It will check for 'gist' and 'spgist' >> >> Hi, >> >> This still doesn't seem right to me. For example, if I choose an >> access method that doesn't have a default operator class for the >> selected data type, Postgres asks me to explicitly choose one, which I >> now can't because the combo box is disabled. Conversely, whilst the >> opclass should probably not be disabled, the ASC/DESC and NULLs >> FIRST/LAST options probably should be disabled (right now, they're >> just ignored). >> >> Thoughts? >> >> -- >> 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 -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2017-01-17 09:52 Surinder Kumar <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Surinder Kumar @ 2017-01-17 09:52 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers Hi On Tue, Jan 17, 2017 at 3:08 PM, Dave Page <[email protected]> wrote: > Hi > > On Fri, Jan 13, 2017 at 6:50 AM, Surinder Kumar > <[email protected]> wrote: > > Hi > > > > Please find updated patch following changes: > > 1) Keep field 'opclass' combo box enabled. > > That doesn't seem to be working. Is the field 'opclass' is not enabled for you?. Can you please tell in which scenario it doesn't gets enabled? so that I can fix it. > > > 2) Keep ASC/DESC and NULLs FIRST/LAST options disable for access methods > > other than 'btree'. > > 3) Add validation for name field. > > Those bits do though :-) > > Please fix 1). > > Thanks! > > > On Fri, Nov 25, 2016 at 5:16 PM, Dave Page <[email protected]> wrote: > >> > >> On Thu, Nov 24, 2016 at 12:13 PM, Surinder Kumar > >> <[email protected]> wrote: > >> > Hi > >> > > >> > Please find updated patch with change. > >> > > >> > On Fri, Oct 21, 2016 at 9:16 PM, Dave Page <[email protected]> wrote: > >> >> > >> >> On Fri, Oct 21, 2016 at 4:42 PM, Surinder Kumar > >> >> <[email protected]> wrote: > >> >> > On Fri, Oct 21, 2016 at 8:52 PM, Dave Page <[email protected]> > wrote: > >> >> >> > >> >> >> Hi > >> >> >> > >> >> >> On Fri, Oct 21, 2016 at 4:16 PM, Surinder Kumar > >> >> >> <[email protected]> wrote: > >> >> >> > Hi > >> >> >> > > >> >> >> > This fix is for exclusion constraint. > >> >> >> > The options like "order" and "nulls" must be conditional. i.e. > >> >> >> > include > >> >> >> > only > >> >> >> > when access method type is other than "gist". > >> >> >> > >> >> >> When creating an index, the asc/desc options are disabled if > >> >> >> gist/gin > >> >> >> used. I think they also should be here. > >> >> >> > >> >> >> Also, what about gin indexes in this case? > >> >> > > >> >> > As per documentation, > >> >> > The access method must support amgettuple (see Chapter 52); at > >> >> > present > >> >> > this > >> >> > means GIN cannot be used > >> >> > >> >> OK, but this patch (unlike the last one) only seems to check for > gist. > >> > > >> > I have modified the code so It will check for 'gist' and 'spgist' > >> > >> Hi, > >> > >> This still doesn't seem right to me. For example, if I choose an > >> access method that doesn't have a default operator class for the > >> selected data type, Postgres asks me to explicitly choose one, which I > >> now can't because the combo box is disabled. Conversely, whilst the > >> opclass should probably not be disabled, the ASC/DESC and NULLs > >> FIRST/LAST options probably should be disabled (right now, they're > >> just ignored). > >> > >> Thoughts? > >> > >> -- > >> 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 > ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2017-01-17 10:13 Dave Page <[email protected]> parent: Surinder Kumar <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Dave Page @ 2017-01-17 10:13 UTC (permalink / raw) To: Surinder Kumar <[email protected]>; +Cc: pgadmin-hackers Hi On Tue, Jan 17, 2017 at 9:52 AM, Surinder Kumar <[email protected]> wrote: > Hi > On Tue, Jan 17, 2017 at 3:08 PM, Dave Page <[email protected]> wrote: >> >> Hi >> >> On Fri, Jan 13, 2017 at 6:50 AM, Surinder Kumar >> <[email protected]> wrote: >> > Hi >> > >> > Please find updated patch following changes: >> > 1) Keep field 'opclass' combo box enabled. >> >> That doesn't seem to be working. > > Is the field 'opclass' is not enabled for you?. No. > Can you please tell in which scenario it doesn't gets enabled? so that I can > fix it. PostgreSQL 9.4, table: CREATE TABLE public.foo ( id text COLLATE pg_catalog."default" NOT NULL, data1 text COLLATE pg_catalog."default", CONSTRAINT foo_pkey1 PRIMARY KEY (id), CONSTRAINT gerp1 UNIQUE (id, data1) ) 1) Right-click, Create index 2) Name: xxxx 3) Access method: gist 4) Add column. Select id (note the opclass field is not enabled) 5) Add column. Select data1 (note the opclass field is not enabled). 6) Click save, and behold the following error in all it's glory :-) ERROR: data type text has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2017-01-20 07:29 Surinder Kumar <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Surinder Kumar @ 2017-01-20 07:29 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers Hi Dave, The last patch was for exclusion constraint only. The same issue issue is reported RM2061 <https://redmine.postgresql.org/issues/2061; for index. So, this patch takes care of both issues. Added a flag '*is_sort_nulls_applicable*' which is set to true for access method 'btree'. If its is true, the values for options 'ASC/DESC' and 'NULLS' are included in 'create' sql for exclusion and index otherwise not. Please find updated patch and review. On Tue, Jan 17, 2017 at 3:43 PM, Dave Page <[email protected]> wrote: > Hi > > On Tue, Jan 17, 2017 at 9:52 AM, Surinder Kumar > <[email protected]> wrote: > > Hi > > On Tue, Jan 17, 2017 at 3:08 PM, Dave Page <[email protected]> wrote: > >> > >> Hi > >> > >> On Fri, Jan 13, 2017 at 6:50 AM, Surinder Kumar > >> <[email protected]> wrote: > >> > Hi > >> > > >> > Please find updated patch following changes: > >> > 1) Keep field 'opclass' combo box enabled. > >> > >> That doesn't seem to be working. > It is now fixed in the updated patch. > > > > Is the field 'opclass' is not enabled for you?. > > No. > > > Can you please tell in which scenario it doesn't gets enabled? so that I > can > > fix it. > > PostgreSQL 9.4, table: > > CREATE TABLE public.foo > ( > id text COLLATE pg_catalog."default" NOT NULL, > data1 text COLLATE pg_catalog."default", > CONSTRAINT foo_pkey1 PRIMARY KEY (id), > CONSTRAINT gerp1 UNIQUE (id, data1) > ) > > 1) Right-click, Create index > > 2) Name: xxxx > > 3) Access method: gist > > 4) Add column. Select id (note the opclass field is not enabled) > > 5) Add column. Select data1 (note the opclass field is not enabled). > > 6) Click save, and behold the following error in all it's glory :-) > > ERROR: data type text has no default operator class for access method > "gist" > HINT: You must specify an operator class for the index or define a > default operator class for the data type. Thanks for steps. > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [application/octet-stream] RM1840_V2.patch (16.0K, 3-RM1840_V2.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py index 6b7f52b..fe7d934 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/__init__.py @@ -816,7 +816,7 @@ class ExclusionConstraintView(PGChildNodeView): sql = render_template("/".join([self.template_path, 'create.sql']), data=data, conn=self.conn) - return sql, data['name'] if 'name' in data else old_data['name'] + return sql, data['name'] @check_precondition def sql(self, gid, sid, did, scid, tid, exid=None): diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js index 9250d9f..0660589 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/exclusion_constraint/templates/exclusion_constraint/js/exclusion_constraint.js @@ -7,10 +7,11 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { defaults: { column: undefined, oper_class: undefined, - order: undefined, - nulls_order: undefined, + order: false, + nulls_order: false, operator:undefined, - col_type:undefined + col_type:undefined, + is_sort_nulls_applicable: true }, toJSON: function () { var d = pgBrowser.Node.Model.prototype.toJSON.apply(this, arguments); @@ -23,26 +24,9 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { },{ id: 'oper_class', label:'{{ _('Operator class') }}', type:'text', node: 'table', url: 'get_oper_class', first_empty: true, - editable: function(m) { - if (m instanceof Backbone.Collection) { - return true; - } - if ((_.has(m.collection, 'handler') && - !_.isUndefined(m.collection.handler) && - !_.isUndefined(m.collection.handler.get('oid')))) { - return false; - } - - if (m.collection) { - var indexType = m.collection.handler.get('amname') - return (indexType == 'btree' || _.isUndefined(indexType) || - _.isNull(indexType) || indexType == ''); - } else { - return true; - } - }, + editable: true, select2: { - allowClear: true, width: 'style', + allowClear: true, width: 'style', tags: true, placeholder: '{{ _("Select the operator class") }}' }, cell: Backgrid.Extension.Select2Cell.extend({ initialize: function () { @@ -55,6 +39,12 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { if (url && (indextype == 'btree' || _.isUndefined(indextype) || _.isNull(indextype) || indextype == '')) { + // Set sort_order and nulls to true if access method is btree + setTimeout(function() { + m.set('order', true); + m.set('nulls_order', true); + }, 10); + var node = this.column.get('schema_node'), eventHandler = m.top || m, node_info = this.column.get('node_info'), @@ -108,6 +98,14 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { if (m instanceof Backbone.Collection) { return true; } + else { + if (m.top.get('amname') === 'btree') { + m.set('is_sort_nulls_applicable', true); + return true; + } + m.set('is_sort_nulls_applicable', false); + return false; + } if ((_.has(m.collection, 'handler') && !_.isUndefined(m.collection.handler) && !_.isUndefined(m.collection.handler.get('oid')))) { @@ -124,6 +122,15 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { if (m instanceof Backbone.Collection) { return true; } + else { + if (m.top.get('amname') === 'btree') { + m.set('is_sort_nulls_applicable', true); + return true; + } + m.set('is_sort_nulls_applicable', false); + return false; + } + if ((_.has(m.collection, 'handler') && !_.isUndefined(m.collection.handler) && !_.isUndefined(m.collection.handler.get('oid')))) { @@ -894,8 +901,15 @@ function($, _, S, pgAdmin, pgBrowser, Alertify) { }], validate: function() { this.errorModel.clear(); - var columns = this.get('columns'); - if ((_.isUndefined(columns) || _.isNull(columns) || columns.length < 1)) { + var columns = this.get('columns'), + name = this.get('name'); + + if ((_.isUndefined(name) || _.isNull(name) || name.length < 1)) { + var msg = '{{ _('Please specify name for exclusion constraint.') }}'; + this.errorModel.set('name', msg); + return msg; + } + else if ((_.isUndefined(columns) || _.isNull(columns) || columns.length < 1)) { var msg = '{{ _('Please specify columns for exclusion constraint.') }}'; this.errorModel.set('columns', msg); return msg; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js index 6531ba5..6f46265 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/indexes/templates/index/js/index.js @@ -51,7 +51,8 @@ function($, _, S, pgAdmin, pgBrowser, Backform, alertify) { collspcname: undefined, op_class: undefined, sort_order: false, - nulls: false + nulls: false, + is_sort_nulls_applicable: true }, schema: [ { @@ -77,7 +78,7 @@ function($, _, S, pgAdmin, pgBrowser, Backform, alertify) { control: 'node-ajax-options', url: 'get_collations', node: 'index' },{ id: 'op_class', label:'{{ _('Operator class') }}', - cell: NodeAjaxOptionsDepsCell, + cell: NodeAjaxOptionsDepsCell, tags: true, type: 'text', disabled: 'checkAccessMethod', editable: function(m) { // Header cell then skip @@ -108,13 +109,19 @@ function($, _, S, pgAdmin, pgBrowser, Backform, alertify) { },{ id: 'sort_order', label:'{{ _('Sort order') }}', cell: Backgrid.Extension.TableChildSwitchCell, type: 'switch', - disabled: 'checkAccessMethod', editable: function(m) { - // Header cell then skip - if (m instanceof Backbone.Collection) { - return false; + // Header cell then skip + if (m instanceof Backbone.Collection) { + return false; + } + else { + if (m.top.get('amname') === 'btree') { + m.set('is_sort_nulls_applicable', true); + return true; } - return !(m.checkAccessMethod.apply(this, arguments)); + m.set('is_sort_nulls_applicable', false); + return false; + } }, deps: ['amname'], options: { @@ -125,13 +132,18 @@ function($, _, S, pgAdmin, pgBrowser, Backform, alertify) { },{ id: 'nulls', label:'{{ _('NULLs') }}', cell: Backgrid.Extension.TableChildSwitchCell, type: 'switch', - disabled: 'checkAccessMethod', editable: function(m) { - // Header cell then skip - if (m instanceof Backbone.Collection) { + // Header cell then skip + if (m instanceof Backbone.Collection) { + return true; + } else { + if (m.top.get('amname') === 'btree') { + m.set('is_sort_nulls_applicable', true); return true; - } - return !(m.checkAccessMethod.apply(this, arguments)); + } + m.set('is_sort_nulls_applicable', false); + return false; + } }, deps: ['amname', 'sort_order'], options: { @@ -184,9 +196,11 @@ function($, _, S, pgAdmin, pgBrowser, Backform, alertify) { if(m.get('sort_order') == true && m.previous('sort_order') == false) { setTimeout(function() { m.set('nulls', true) }, 10); } - return false; } - return true; + else { + m.set('is_sort_nulls_applicable', false); + } + return false; }, }); diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql index db29048..6d0bd1b 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.1_plus/create.sql @@ -1,7 +1,7 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} EXCLUDE {% if data.amname and data.amname != '' %}USING {{data.amname}}{% endif %} ( {% for col in data.columns %}{% if loop.index != 1 %}, - {% endif %}{{ conn|qtIdent(col.column)}} {% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} + {% endif %}{{ conn|qtIdent(col.column)}}{% if col.oper_class and col.oper_class != '' %} {{col.oper_class}}{% endif%}{% if col.order is defined and col.is_sort_nulls_applicable %}{% if col.order %} ASC{% else %} DESC{% endif %} NULLS{% endif %} {% if col.nulls_order is defined and col.is_sort_nulls_applicable %}{% if col.nulls_order %}FIRST {% else %}LAST {% endif %}{% endif %}WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %} USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql index db29048..6d0bd1b 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.2_plus/create.sql @@ -1,7 +1,7 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} EXCLUDE {% if data.amname and data.amname != '' %}USING {{data.amname}}{% endif %} ( {% for col in data.columns %}{% if loop.index != 1 %}, - {% endif %}{{ conn|qtIdent(col.column)}} {% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} + {% endif %}{{ conn|qtIdent(col.column)}}{% if col.oper_class and col.oper_class != '' %} {{col.oper_class}}{% endif%}{% if col.order is defined and col.is_sort_nulls_applicable %}{% if col.order %} ASC{% else %} DESC{% endif %} NULLS{% endif %} {% if col.nulls_order is defined and col.is_sort_nulls_applicable %}{% if col.nulls_order %}FIRST {% else %}LAST {% endif %}{% endif %}WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %} USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.6_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.6_plus/create.sql index db29048..6d0bd1b 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.6_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/exclusion_constraint/sql/9.6_plus/create.sql @@ -1,7 +1,7 @@ ALTER TABLE {{ conn|qtIdent(data.schema, data.table) }} ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} EXCLUDE {% if data.amname and data.amname != '' %}USING {{data.amname}}{% endif %} ( {% for col in data.columns %}{% if loop.index != 1 %}, - {% endif %}{{ conn|qtIdent(col.column)}} {% if col.oper_class and col.oper_class != '' %}{{col.oper_class}} {% endif%}{% if col.order %}ASC{% else %}DESC{% endif %} NULLS {% if col.nulls_order %}FIRST{% else %}LAST{% endif %} WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} + {% endif %}{{ conn|qtIdent(col.column)}}{% if col.oper_class and col.oper_class != '' %} {{col.oper_class}}{% endif%}{% if col.order is defined and col.is_sort_nulls_applicable %}{% if col.order %} ASC{% else %} DESC{% endif %} NULLS{% endif %} {% if col.nulls_order is defined and col.is_sort_nulls_applicable %}{% if col.nulls_order %}FIRST {% else %}LAST {% endif %}{% endif %}WITH {{col.operator}}{% endfor %}){% if data.fillfactor %} WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %} USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/9.1_plus/create.sql index 33af197..b7bfa52 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index/sql/9.1_plus/create.sql @@ -3,7 +3,7 @@ CREATE {% if data.indisunique %}UNIQUE {% endif %}INDEX {% if data.isconcurrent {% if mode == 'create' %} ({% for c in data.columns %}{% if loop.index != 1 %}, {% endif %}{{conn|qtIdent(c.colname)}}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.op_class %} - {{c.op_class}}{% endif %}{% if data.amname is defined and data.amname not in ['gist', 'gin'] %}{% if c.sort_order is defined %}{% if c.sort_order %} DESC{% else %} ASC{% endif %}{% endif %}{% if c.nulls is defined %} NULLS {% if c.nulls %} + {{c.op_class}}{% endif %}{% if data.amname is defined %}{% if c.sort_order is defined and c.is_sort_nulls_applicable %}{% if c.sort_order %} DESC{% else %} ASC{% endif %}{% endif %}{% if c.nulls is defined and c.is_sort_nulls_applicable %} NULLS {% if c.nulls %} FIRST{% else %}LAST{% endif %}{% endif %}{% endif %}{% endfor %}) {% else %} {## We will get indented data from postgres for column ##} ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL @ 2017-01-25 13:21 Dave Page <[email protected]> parent: Surinder Kumar <[email protected]> 0 siblings, 0 replies; 14+ messages in thread From: Dave Page @ 2017-01-25 13:21 UTC (permalink / raw) To: Surinder Kumar <[email protected]>; +Cc: pgadmin-hackers Thanks, patch applied. On Fri, Jan 20, 2017 at 7:29 AM, Surinder Kumar <[email protected]> wrote: > Hi Dave, > > The last patch was for exclusion constraint only. The same issue issue is > reported RM2061 for index. > So, this patch takes care of both issues. > > Added a flag 'is_sort_nulls_applicable' which is set to true for access > method 'btree'. > If its is true, the values for options 'ASC/DESC' and 'NULLS' are included > in 'create' sql for exclusion and index otherwise not. > > Please find updated patch and review. > > On Tue, Jan 17, 2017 at 3:43 PM, Dave Page <[email protected]> wrote: >> >> Hi >> >> On Tue, Jan 17, 2017 at 9:52 AM, Surinder Kumar >> <[email protected]> wrote: >> > Hi >> > On Tue, Jan 17, 2017 at 3:08 PM, Dave Page <[email protected]> wrote: >> >> >> >> Hi >> >> >> >> On Fri, Jan 13, 2017 at 6:50 AM, Surinder Kumar >> >> <[email protected]> wrote: >> >> > Hi >> >> > >> >> > Please find updated patch following changes: >> >> > 1) Keep field 'opclass' combo box enabled. >> >> >> >> That doesn't seem to be working. > > It is now fixed in the updated patch. >> >> > >> > Is the field 'opclass' is not enabled for you?. >> >> No. >> >> > Can you please tell in which scenario it doesn't gets enabled? so that I >> > can >> > fix it. >> >> PostgreSQL 9.4, table: >> >> CREATE TABLE public.foo >> ( >> id text COLLATE pg_catalog."default" NOT NULL, >> data1 text COLLATE pg_catalog."default", >> CONSTRAINT foo_pkey1 PRIMARY KEY (id), >> CONSTRAINT gerp1 UNIQUE (id, data1) >> ) >> >> 1) Right-click, Create index >> >> 2) Name: xxxx >> >> 3) Access method: gist >> >> 4) Add column. Select id (note the opclass field is not enabled) >> >> 5) Add column. Select data1 (note the opclass field is not enabled). >> >> 6) Click save, and behold the following error in all it's glory :-) >> >> ERROR: data type text has no default operator class for access method >> "gist" >> HINT: You must specify an operator class for the index or define a >> default operator class for the data type. > > Thanks for steps. >> >> >> -- >> 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 -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers ^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2017-01-25 13:21 UTC | newest] Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2016-10-20 06:47 [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL Surinder Kumar <[email protected]> 2016-10-21 11:08 ` Dave Page <[email protected]> 2016-10-21 15:16 ` Surinder Kumar <[email protected]> 2016-10-21 15:22 ` Dave Page <[email protected]> 2016-10-21 15:42 ` Surinder Kumar <[email protected]> 2016-10-21 15:46 ` Dave Page <[email protected]> 2016-11-24 12:13 ` Surinder Kumar <[email protected]> 2016-11-25 11:46 ` Dave Page <[email protected]> 2017-01-13 06:50 ` Surinder Kumar <[email protected]> 2017-01-17 09:38 ` Dave Page <[email protected]> 2017-01-17 09:52 ` Surinder Kumar <[email protected]> 2017-01-17 10:13 ` Dave Page <[email protected]> 2017-01-20 07:29 ` Surinder Kumar <[email protected]> 2017-01-25 13:21 ` Dave Page <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox