public inbox for [email protected]
help / color / mirror / Atom feedFrom: Surinder Kumar <[email protected]>
To: Dave Page <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL
Date: Fri, 20 Jan 2017 12:59:52 +0530
Message-ID: <CAM5-9D91tXWjTt0AEzOyUbukmSMw9QA9+xoav7caddY_ig0C7w@mail.gmail.com> (raw)
In-Reply-To: <CA+OCxowyX+RkvqiEQZ0BNn=iTUnyDTF3XXixEGnhsU8SwaTVCA@mail.gmail.com>
References: <CAM5-9D-ti4O0bE3W_=puMMWPjO4qR525gYfKuGSYAiGow-AZdQ@mail.gmail.com>
<CA+OCxowGDMCf_YJauW=u24X_7X4XMnT5tkca4qMRZCpH8z2mDA@mail.gmail.com>
<CAM5-9D8XX_BVPi2XQODAK5DPRrhi9sTMHQDh_KTu0p9FjkU=dQ@mail.gmail.com>
<CA+OCxozBD1OBWQTG5SvdnVFipuScgK1nNaXUxazDSXqif_gtzQ@mail.gmail.com>
<CAM5-9D8osVRFd4s9kps-Bnuqw53WPFT4TN8ayuOO0Hr5nzuwxA@mail.gmail.com>
<CA+OCxowo2158=USKZ1nFvpogA4BO2R3UcHHC6xWzSsiGOdj6eg@mail.gmail.com>
<CAM5-9D97YhGDXQZfOg+FBf0nDedQvydWH+mAmr2iRi0rOL9PSA@mail.gmail.com>
<CA+OCxoyCJuY-Edh7K8uX8v9f+uYAS0YpQMmnzqPn5AZyY9WgKw@mail.gmail.com>
<CAM5-9D-+uETwn4_LqHOGAwk46Wg6U-udSV_WrAy=FHnKwffhFA@mail.gmail.com>
<CA+OCxowjCY1bvuBM3nZ8QpCkG2GxawCThLTjf9mg_VRHGCQaew@mail.gmail.com>
<CAM5-9D_EPCFv4kYs_qaXpBNyDRQhANDCz+kgYikyf7-Xathg4w@mail.gmail.com>
<CA+OCxowyX+RkvqiEQZ0BNn=iTUnyDTF3XXixEGnhsU8SwaTVCA@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-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 ##}
view thread (14+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL
In-Reply-To: <CAM5-9D91tXWjTt0AEzOyUbukmSMw9QA9+xoav7caddY_ig0C7w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox