Subject: [pgAdmin4][Patch]: RM1840 - cannot create gist index due to enforced ASC, DESC options in generated SQL

From: Surinder Kumar <[email-masked]>
Date: Thu, 20 Oct 2016 12:17:52 +0530

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-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

---

From: Dave Page <[email-masked]>
Date: Fri, 21 Oct 2016 12:08:50 +0100

Thanks, applied.

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

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

---

From: Surinder Kumar <[email-masked]>
Date: Fri, 21 Oct 2016 20:46:13 +0530

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".

---

From: Dave Page <[email-masked]>
Date: Fri, 21 Oct 2016 16:22:53 +0100

Hi

> 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?

---

From: Surinder Kumar <[email-masked]>
Date: Fri, 21 Oct 2016 21:12:14 +0530

> 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*

---

From: Dave Page <[email-masked]>
Date: Fri, 21 Oct 2016 16:46:31 +0100

On Fri, Oct 21, 2016 at 4:42 PM, Surinder Kumar
<[email-masked]> wrote:
> 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.

---

From: Surinder Kumar <[email-masked]>
Date: Thu, 24 Nov 2016 17:43:35 +0530

Hi

Please find updated patch with change.

> 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'

---

From: Dave Page <[email-masked]>
Date: Fri, 25 Nov 2016 11:46:38 +0000

On Thu, Nov 24, 2016 at 12:13 PM, Surinder Kumar
<[email-masked]> wrote:
>> >> > The options like "order" and "nulls" must be conditional. i.e.
>> >> > include
>> >> > only
> 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?

---

From: Surinder Kumar <[email-masked]>
Date: Fri, 13 Jan 2017 12:20:46 +0530

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.

---

From: Dave Page <[email-masked]>
Date: Tue, 17 Jan 2017 09:38:47 +0000

Hi

> 1) Keep field 'opclass' combo box enabled.

That doesn't seem to be working.

> 3) Add validation for name field.

Those bits do though :-)

Please fix 1).

Thanks!

>> >> > The access method must support amgettuple (see Chapter 52); at
>> >> > present
>> >> > this
>> >> > means GIN cannot be used
>> >>

---

From: Surinder Kumar <[email-masked]>
Date: Tue, 17 Jan 2017 15:22:01 +0530

Hi

> 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.​

---

From: Dave Page <[email-masked]>
Date: Tue, 17 Jan 2017 10:13:13 +0000

Hi

On Tue, Jan 17, 2017 at 9:52 AM, Surinder Kumar
<[email-masked]> wrote:
> 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.

---

From: Surinder Kumar <[email-masked]>
Date: Fri, 20 Jan 2017 12:59:52 +0530

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.

> >> That doesn't seem to be working.
​It is now fixed in the updated patch.​

> can
> default operator class for the data type.​

​Thanks for steps.​

---

From: Dave Page <[email-masked]>
Date: Wed, 25 Jan 2017 13:21:49 +0000

Thanks, patch applied.

On Fri, Jan 20, 2017 at 7:29 AM, Surinder Kumar
<[email-masked]> wrote:
> reported RM2061 for index.
> Added a flag 'is_sort_nulls_applicable' which is set to true for access
> method 'btree'.
> It is now fixed in the updated patch.
>>
>> >
> Thanks for steps.